Using Power BI Templates

One of the big issues when distributing Power BI files locally is that the file paths get hard coded, and there is no way to make the path dynamic. This is very frustrating as any user who has a different data path to the source file from the author must edit the query and update the file path. One strategy to avoid this issue is to using Power BI Templates to prompt the user for the source data path when the template is launched. As a by-product, this also clears all data from the file, forcing a user to have the ability to connect to the data source in order to see anything.

The general method for setting up a Power BI template is as follows:

Use "Parameters" to hold key values

Exporting to (not Saving as) a *.pbit file

And the reason we use Parameters to hold the key values?

Parameters are reset upon template export

Queries are not reset upon template export

Laying the Groundwork for Using Power BI Templates

Let’s create a template. Use whatever file you like, but for this article we will assume that we have a Power BI project that pulls data from an Excel file called "DataFile.xlsx" which lives at "C:\Data Folder".

To lay the groundwork for a template, begin by editing the query and:

Step 1: Create a “FolderPath” parameter

To create the new FolderPath Paramter, go to Home --> Manage Parameters --> New Parameter

Item

Value

Parameter Name:

FolderPath (no spaces)

Description:

A description of what the parameter is for

Type:

Text

Current Value:

The current folder path without the trailing backslash
("C:\Data Folder")

Figure 1- Creating the FolderPath Parameter

Step 2: Create a FileName query

Notice that this time we need a query, not a parameter. This will ensure that the template will not clear this value, allowing us to keep the file name preserved in the solution.

To create the query, right click in the Queries pane (on the left side of the window) --> New Query --> Blank Query

Item

Value

Query Name:

FileName (no spaces)

Formula:

The file name preceded by a backslash ("\DataFile.xlsx")

Figure 2- Creating the FileName Query

Step 3: Modify the original query that calls the data source

The original query that calls the Excel file in this example is (as shown in Figure 2, above) MyExcelData, so this is the query we need to modify. To do so:

Select the original query and select the Source step

The formula bar should now contain the original call to the data source:

Figure 3- The original Source step

Update the formula to make use of the new Parameter and Query:

Figure 4- The revised Source step

Upon committing this change, the table should still return the results you had before. If you get an error then check the values of the FolderPath parameter and FileName queries to ensure they are correct.

Note: The backslash to separate the FolderPath from the FileName should be part of the FileName query. This makes it easier for the end user as they don’t have to remember to type the backslash when they are inputting the file path. And if they do – returning "C:\Data Folder\\DataFile.xlsx" – the solution will still work. (Power BI implicitly replaces the \\ with a single \ character.)

Creating a Template

With the groundwork in place, the next step is to export the file to a template.

Step 1: Create a Backup file

The first thing you should do before exporting your file to a template is to save it as a regular *.pbix file first. This is because all data will be cleared from the file upon export. If anything goes wrong, it’s very helpful to have a fallback point.
Save your file via either:

File --> Save

File --> Save As

Step 2: Export to a Template

Now we are ready to create our template file. To do this:

Go to File --> Export --> Power BI Template

Enter a template description (this is the prompt shown to a user when they open the file)

Figure 5- Providing a Template prompt

Click OK

Choose a location in which to store the *.pbit file

Using Power BI Templates

Next, you should test your template, to ensure the user experience is what you need it to be. To do this, we will replicate the user experience:

Close Power BI Desktop

Locate the *.pbit file and open it

Upon opening, the user will be prompted with the following:

Figure 6- Template prompting

There are five things you may wish to know about this dialog:

The dialog’s title comes directly from the name of the template file ("MyReport.pbit").

The FolderPath parameter is listed with a little "i" beside it. Mousing over the i will display the text from the parameters “Description” box.

The user will be required to type or paste in the folder path manually. Unfortunately, there is no way to add a browse dialog to this interface.

The Load button is a split-button that also has the ability to select Edit, which will take the user directly into the Power Query editing interface. (Since this is a Power BI Desktop file and the user has the ability to do this from the Home tab, there is no way to disable this behavior.)

If the user hits cancel for any reason or cannot access the data source, a blank Power BI file will be opened instead, so they user won’t be able to see any of your data or reports.

Deploying a Template

Deploying the template file is easy: simply email the *.pbit file to someone (but you should always test it first, of course)!

I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!

If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!