When you are using Power BI for your report analysis, you also need to create a few dashboards that can be really helpful and used often. You can restructure your process and update your reports by creating Power BI templates that give you the ability to upload a number of data sources and automatically rebuild your charts and formats in a new, active dashboard.
Templates help the users to export the definition of a report without considering the actual data. Users can easily create a Template using Power BI Desktop, which asks them for parameter values and create a new Power BI Desktop Report depending upon the elements in the Template file (PBIT file).
HOW TO TRANSFORM YOUR DATA?
While making a Power BI report with the aim of creating a Power BI template, you need to be alert of the way that you transform your data. Because creating a Power BI template will basically mean that when you upload a data source, it will be transformed or modified based on the steps you apply on your original report.
How are they connected?
When you transform data from your report, you need to perform it in a way that can be reapplied to other data sources as well. But the condition is that the names of the columns of original data source and new data source must be the same. You cannot apply transformations that may work in one file but not another, like replacing a specific value, unless that value will be found in any file that you may open with the Power BI template.
You can predict Power BI’s applied steps as a macro for your data; the same steps will be performed in the exact same order, based on transformations you make to each column. If your new data is missing any column, has an additional column, or has a different column than your primary data source, the steps will not work when applied to the new, different data source.
BUILD YOUR POWER BI TEMPLATE
After you have built your Power BI report, it’s time to create your Power BI template. Follow the below procedure to create a Power BI template.
- CREATE A NEW REPORT IN POWER BI
In order to create a Power BI template, you should first have a Power BI report. Image below shows the financial report created on Power BI desktop.
- EDIT QUERIES IN POWER BI
Now you have to go to the Power Query Editor to create a new parameter. To open Power Query Editor go to Home tab > Edit Queries tab > Select Edit Queries option as shown in below image.
- CREATE A NEW QUERY PARAMETER
Now it’s the time to create a new parameter in your query editor. As shown in below image, you can only see the source file i.e. Financials in the Queries pane. When you will add a parameter, it will be displayed below the source file in the Queries pane.
After that you have to create a new parameter by selecting Manage Parameters > New Parameter under the Home tab in the query editor ribbon.
As soon as you click on new parameter option, the Parameters window will get open. Now you need to create your parameter by giving a Parameter Name in the Name Text box. Then you have to change the Type to “Text,”and add the file path to the current data source.Also make sure that the Required box is checked. If you select “Required,” that means that your Power BI template will not open unless a value is entered into the prompt. If the “Required” box is unchecked it can be used for optional files.
After clicking on OK button, you will see the new parameter in the left sidebar of your query editor as shown in below image.
- CHANGE SOURCE SETTINGS IN POWER BI
After creating the new parameter, you need to update the source for your file by clicking on the gear icon to the right of Source under Applied Steps as shown in image below.
When you click on the gear icon a window will get open as shown below which will allow you to change the source path.
To change the file path, click on the Advanced option first and then click on the dropdown of the file path and choose parameter as shown in image below.
When you click on OK you will get to see that your path is been replaced by your parameter as shown below.
- CLOSE AND APPLY QUERY EDITOR
As you’ve created your parameter and updated your data source to that parameter, you can close out of the query editor and apply as shown in image below.
- SAVE REPORT AS A POWER BI TEMPLATE FILE
To save your Power BI template, select File > Save As and save it as a .pbit file i.e. Power BI Template file.
- OPEN POWER BI TEMPLATE FILE WITH A NEW DATA SOURCE
To test your new template, open the Power BI template file and click OK to export the template as shown in image below.
After that you will be prompted to enter the file path for your data source. Enter your data source’s file path as shown below, including the filename and file type.
After clicking on Load button, your report should load by applying your steps to your new data source.
When data gets loaded completely you will observe that your Power BI template file includes all the data that you had applied to your report. Below shown is the image of Power BI template file.