Deep Dive into Query Parameters and Power BI Templates
Two of the new features in the Power BI Desktop April Update are Query Parameters and Power BI Template files. In this blog post, we will take a deeper look at the new capabilities and scenarios that these two features enable in Power BI.
At a very high level, Query Parameters allow users to easily make parts of their reports and data models (such as a query filter, a data source reference, a measure definition, etc.) depend on one or more parameter values. On top of this capability, Templates allow users to export the definition of a report (report + data model + queries definition + parameters, if any) without including the actual data. Users can easily instantiate a Template using Power BI Desktop, which will ask them for parameter values (if needed) and create a new Power BI Desktop Report (PBIX file) based upon the contents in the Template file (PBIT file).
Let’s take a closer look at how this can be done…
Query Parameters
With the new Query Parameters feature, users can now easily define one or multiple parameters to be used in their queries, Data Model and report layers in Power BI Desktop. Users can define new parameters by using the “Manage Parameters” dialog in the Query Editor window.
From this dialog, users can create new parameters and specify metadata and settings for each parameter:
- Parameter Name.
- Parameter Description: This will be displayed next to the parameter name in downstream experiences, and helps the user who is specifying the parameter value to better understand the purpose and semantics of this parameter.
- Optional vs. Required: Users can specify whether a certain parameter is optional or a value for that parameter must be specified (required).
- Parameter Type: This field applies a Data Type restriction on the input value for the parameter. For instance, users can define a parameter of type Text, or Date/Time. Users can also specify Any type for more flexibility.
- Parameter Accepted Values: In addition to Data Type restrictions, users can apply further restrictions to the allowed values for a given parameter. For instance, users could specify that the Data Type for a parameter is Text and restrict the acceptable values to a static list of Text values. Users will then be able to pick one of these values when specifying the parameter value to use. Currently, only a static list of Accepted Values (or any value of the expected type) are accepted. In future updates, we will enable users to bind the list of Accepted Values for a parameter to the output of another query. This would enable dynamic sets of options to be displayed to the user, maybe even based on their selection for a previous parameter. A typical example for this would be making a “City” parameter change the list of values to select from, based on another parameter that allows users to specify a “State”. Also in a future update, we will enable users to select more than one value from the list of Accepted Values, rather than just one as it is the case in the current product version.
- Default Value: This setting allows the Parameter creator to specify what the default value or selection should be for the user specifying the parameter value.
- Current Value: This setting allows users to specify the value for this parameter in the current report.
After defining one or more parameters and clicking OK in the “Manage Parameters” dialog, users will get back to the Query Editor dialog and will see a new query defined for each parameter. Note that Parameters are just like any other query in Power BI Desktop; they can be referenced from other queries, loaded to the Data Model and reference in DAX expressions, etc. By default though, Parameters are not loaded to the Data Model, so users have to right-click on a parameter and select “Enable Load” if they want to have them loaded to the Data Model.
Now that users have defined one or more parameters, they need to specify how they are being referenced or used by other queries. We’re adding support via the most common UX dialogs to accomplish this. Currently, most of the Data Source dialogs support referencing parameters on each input field. Once users have parameters in a Power BI Desktop report, and they access a data source dialog, they will see a new widget that allows them to switch the input mode for a given input box. The two supported modes are “Enter a value” (i.e. a static value) or “Parameter” (to reference an existing parameter from the PBIX file).
In addition to Data Source dialogs, parameters can also be referenced from the “Filter Rows” dialog and the “Replace Values” dialog. In future updates, we will add support for referencing parameters from other dialogs, such as the “Insert Conditional Column” dialog.
After Query Parameters have been referenced from other queries as needed, users can hit “Close & Apply” in the Query Editor ribbon to get their data and parameters loaded into the data model. At that point, they can start creating the report and even reference these parameters from DAX expressions, such as the one in the following screenshot.
Within the Report view, users can edit parameter values by using the “Edit Parameters” button in the Home ribbon tab (under “Edit Queries”).
This will bring up a dialog to allow users to reconfigure parameter values and will refresh the data and report visuals based on the new parameter values.
Note that currently, parameter values can only be modified within Power BI Desktop. We’re actively working on ways to allow users to change parameter values within PowerBI.com for published reports consumed via the Web experience.
To summarize, Query Parameters allow users to:
- Define one or more parameters and associated metadata (name, description, etc.)
- Apply restrictions to the values that a parameter can have, including a Data Type as well as the ability to provide a finite list of values accepted for that parameter. In the future, we will also provide the ability for that “list of values” to be dynamically calculated. We will also allow users to select more than one value from that list, as opposed to today’s single value selection.
- Query Parameters can be referenced via the UX dialogs for most common operations in Power BI Desktop’s “Get Data” & “Query Editor” experiences: Data Source connection dialogs, Filter Rows and Replace Values. In the future, we will add support for referencing parameters from more dialogs. As a temporary workaround, you can reference parameters via the M code for any step in your queries.
Templates
Another new feature in the Power BI Desktop April Update that we will cover in this blog post is Templates. This feature allows users to export a Power BI Desktop report as a template (PBIT file), which can be instantiated as a new Power BI Desktop report (PBIX file).
A Power BI Report Template contains the definition of the Report (pages, visuals, etc.), Data Model definition (schema, relationships, measures, etc.) and Queries definition (collection of queries, including Query Parameters, etc.). In other words, a Power BI Report template includes pretty much everything that a Report file includes, with the exception of the data itself. When a user tries to instantiate a template, either via double-click on the PBIT file, or by using the “File –> Import –> Power BI Template” path, a new Power BI Desktop file will be created, containing the actual data based on current user’s credentials for data sources, etc.
In order to Export a Power BI Template, users can click “File –> Export –> Power BI Template” from the main Power BI Desktop window.
This will bring up a new dialog where users can provide a description for the template, followed by a Save File dialog where they can pick the name and path to save the template file to.
Notice how the file size of a Power BI Template (PBIT file) is much smaller than the size of a Power BI Report (PBIX file). Well, in this case, not that much since my dataset size was small anyways… : )
Now that users have created a Power BI Report Template, they can use it to instantiate new Power BI Reports on their machine, or share the template file with other users to let them create new Power BI Reports based on this template. There are two ways in which users can consume a Power BI Report template:
- By double-clicking on the PBIT file in a machine with Power BI Desktop installed. This will launch Power BI Desktop and get them into the “Import Template” flow.
- Within Power BI Desktop, clicking on “File –> Import –> Power BI Template”. This will create a new Power BI Desktop file based upon the contents defined in the template.
As part of importing a template, users will be asked to provide values for parameters defined in the template. For instance, importing the “Customers by Country” template that we created in the previous steps will prompt users to select a Country value from the list of accepted values that we specified while defining the parameter.
Once Parameter values have been specified, a new PBIX file will be created, containing all Report pages, visuals, data model artifacts and queries as the original PBIX file, but containing the data based on the current user’s credentials and parameter selection.
Do you find Query Parameters and Power BI Report Templates valuable? If so, we encourage you to try this feature using the latest Power BI Desktop version and share feedback with us via our usual channels (Smile/Frown, Community Forums, UserVoice Feature Suggestions, etc.). You can also download the Power BI Report Template that I created in this blog post to explore the contents in more detail.