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

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

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)!