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

    Comments 1 Comment
    1. AliGW's Avatar
      AliGW -
      Very useful, Ken - thanks!