7/16/2019
Templates are great for word processors and spreadsheets; they save time and enforce consistency. Templates are great for Power BI, too, but Power BI templates must solve a minor complication not faced by its sister Office applications.
The measures, columns, and visualizations that collectively constitute a Power BI report are based on a specific data model. To develop a template for a Power BI report we must define the data model, but provide parameters to let the user specify the data to be imported into the data model when he or she opens the template. These parameters might be as simple as the start date and end date of a monthly report.
Creating Parameters
We'll start with a simple Power BI sales report for the Contoso group. A line chart and a funnel chart showing sales by date and product category will suffice for our demonstration.
You can create named parameters in a Power BI report for use in slicers and DAX expressions. These are not the parameters we need for a Power BI template. The parameters we want are defined in not in Power BI but in Power Query and will be applied when a user first opens a Power BI template. We click on Edit Queries to open the Power Query Editor. There we will find a Manage Parameters button. "Manage Parameters" and "New Parameter" will both take us to the same dialog box, but "New Parameter" will open the dialog with the first new parameter already entered.
In this example we will use the simple example of Start Date and End Date parameters. Anything is fair game for parameterization, though, including database names and server names.
When we create parameters we should choose clear and unambiguous names. It is also important to pay attention to datatype, as we shall see shortly. In this example, we will select the Date datatype.
Filtering on Parameters in Power Query
Once we define our parameters we can use them to apply a filter to whatever column is appropriate, in this case the FactSales DateKey column.
If we attempt to apply a filter to the DateKey column, we find that there is no option to use our new parameters in the dialog box.
This is due to a datatype mismatch between the parameter datatypes and the column datatype. I foolishly chose "Date" for the parameter type while the column is "Date/Time". In this particular dataset we can save some space by converting the column type to "Date" to match the parameters. It doesn't matter which types you change; it only matters that they match. Once the datatypes are correctly matched, we see the option we need.
We're now essentially done.
Creating the Template
Once the necessary parameters are defined, creating the template is trivial. You simply select File | Save As... and choose template files (.pbit) from the dropdown type list in the Save As... dialog. You will be asked to provide a template description.
When a user opens a Power BI template, they will be presented with a dialog box providing the opportunity to enter values for the parameters. The description you provided when you created the template will be shown to the user, so it would be a great idea to explain exactly what values are being requested and how to enter them.
Conclusion
Power BI desktop templates are easy to create. The selection of appropriate Power Query parameters can take any Power BI report and turn it into a reusable resource.
