How to use Parameters in Power Query in Power BI

Colin Maitland, 09 September 2019

In my previous article I demonstrated how to use Connection Parameters in Power BI for the configuration of the Source step in Power Queries used to load data from Dynamics 365 (Online). In this article I will demonstrate how to use Parameters in Power Query.

The following image shows the first three Applied Steps of a Power Query that loads Pledges from Dynamics 365 (Online) into a Power BI data model. The third step filters the rows so that only active Pledges are loaded.

The Filtered Rows step was configured as shown below. This configuration uses the numeric value 0. This is the generic Dynamics 365 Status, StateCode, value for “Active”.

The following image shows the related M code in the corresponding Power Query step. Here, you can see that the meaning and purpose of the value 0 in this Power Query step is not clear. The only indication is that the manually configured name of the Power Query step is “Filtered Rows (StateCode=Active)”.

From the Power BI Desktop, you can use Manage Parameters to configure Parameters for use by Power Query.

The following image shows the configuration of a Parameter named “StateCode (Active)”. The value of the StateCode for “Active” is generic across most entities in Dynamics 365 (Online) and so the name of this Parameter does not need to be “Account StateCode (Active)”. I have configured the Suggested Values for this Parameter, of which there is only one, as a list. This reduces the risk of the value being changed to something else.

After adding the “StateCode (Active)” Parameter, the “Filter Rows (StateCode=Active)” step in the Power Query can be reconfigured by changing the option prior to the Filter Rows value to Parameter and then selecting the “StateCode (Active)” Parameter from the drop-down list of available Parameters, as shown in the following images:

The related M code in the corresponding Power Query step now shows the Parameter name instead of the Parameter value. The name of the Parameter conveys its meaning and purpose.

Parameters may be used to provide clarity, standardise the use of values that are used in more than one Power Query or in more than one step of a Power Query, and allow for easy reconfiguration of Power Query steps; e.g. such as changing a Date From and Date To parameter used for filtering rows that are loaded Dynamics 365 (Online).

The following image shows an example of several Parameters displayed in the Power Query Editor navigation pane, in Power BI Desktop. I have organised these into Groups for clarity and to keep them separate from other items that are displayed in the navigation pane such as Custom Functions and Queries.

Finally, you should consider creating and using a Power BI Template for your Power BI Data Models to include standard and often used Parameters, such as the generic “StateCode (Active)” Parameter demonstrated in this article.