When using Power BI for your analyses, you’re bound to create a few dashboards that you really love and use often. You can streamline your process and standardize your reports by creating templates that give you the ability to upload different data sources and automatically rebuild your charts and formats in a new, dynamic dashboard.
In addition to this guide, watch the video tutorial to create a Power BI template:
How to Create a Power BI Template for Different Data Sources
For this example, we’re using a CTR curve that we’ve created. This dashboard is useful for every client and team member, so creating a template will give us the ability to use each client’s specific data and build out a custom CTR curve in seconds, without having to take the time to rebuild and reformat over and over again.
Think About How You Transform Your Data
When building a Power BI dashboard with the end goal of creating a template, you need to be mindful of the way that you transform your data. Creating a template will essentially mean that when you upload a data source, that source will be transformed based on the steps you apply on your original dashboard.
Why does this matter?
When you transform data for your dashboard, you need to do so in a way that can be reapplied to other data sources. The names of your columns must match. 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 template.
You can envision 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 a column, has an extra column, or has a different column than your original data source, the steps will not work when applied to the new, different data source.
Build Your Power BI Dashboard
Once you’ve built out your dashboard and are satisfied with your report, you’re ready to create your Power BI template.
In this example, we’ll create a template using a single data source (an export from Google Search Console), but you can expand this process to include as many sources as you’d like, as long as you create a separate parameter for each file.
Create a New Query Parameter
Open the query editor, select your file, and click on the gear to the right of the Source under Applied Steps.
You will see the file path to the data source in the popup that opens. Click on the icon to the left of the file path and select New Parameter.
You can also create a new parameter by selecting Manage Parameters > New Parameter under the Home tab in the query editor, but you’ll need to update the source for your file (by clicking the gear icon to the right of Source under Applied Steps) once you’ve created your new parameter as an additional step.
Create your parameter by adding a Parameter Name, filling out the description to describe any requirements for that file, changing the Type to “Text,” and adding the file path to the current data source.
If you select “Required,” that means the template will not open unless a value is entered into the prompt. Unmarking “Required” could be used for optional files.
In the left sidebar of your query editor, you will see the new parameter.
Save Your Dashboard as a Power BI Template File
Now that you’ve created your parameter and updated your data source to that parameter, you can close out of the query editor and apply.
To save your Power BI template, select File > Save As .pbit file (Power BI Template file).
Open Your Power BI Template With a New Data Source
To test your new template, open the template file and you will be prompted to enter the file path for your data source.
Enter your data source’s file path, including the filename and file type. After clicking OK, your dashboard should load by applying your steps to your new data source.
Your Turn: Give This a Try
Now that you know the steps, use this tutorial to create your own Power BI template. We’d love to hear what you think of this tutorial and how you use this template with your own data.
For more data strategy tips and tricks, check out our comprehensive guide to Power BI.
Sign up for our newsletter for more posts like this!
