}

Power BI: Creating and Sharing Power BI Templates

7/16/2019

[:en]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.[:]

Written by Dan Buskirk

The pleasures of the table belong to all ages.” Actually, Brillat-Savaron was talking about the dinner table, but the quote applies equally well to Dan’s other big interest, tables of data. Dan has worked with Microsoft Excel since the Dark Ages and has utilized SQL Server since Windows NT first became available to developers as a beta (it was 32 bits! wow!). Since then, Dan has helped corporations and government agencies gather, store, and analyze data and has also taught and mentored their teams using the Microsoft Business Intelligence Stack to impose order on chaos. Dan has taught Learning Tree in Learning Tree’s SQL Server & Microsoft Office curriculums for over 14 years. In addition to his professional data and analysis work, Dan is a proponent of functional programming techniques in general, especially Microsoft’s new .NET functional language F#. Dan enjoys speaking at .NET and F# user’s groups on these topics.

Chat With Us