Power Query parameters

Parameters are variables or sets of values ​​that are intended to influence the appearance of a query. They are mostly used for filtering, but if you know the “M” language you can also use them for other purposes. Since I will not (for now) write about how “M” statements are created, I will use a simple example where a single parameter will be created and we will use it to filter a query.

To begin with, we will use a table that records turnover by city. It consists of two columns: CITY and SALES. We will load this table into the Power Query Editor and save the query.

Next, you need to create a parameter that will filter this table. To do this, open the Manage Parameters menu on the Home ribbon and select the New Parameter option.

A window will open in which we define the properties of the new parameter. First we will give it the name “F”. In the Description field we can add a short description, and the Required field should be checked if it is mandatory. In the Type field we can select the data type for the given parameter and in the Suggested Value field we select a value, list or query. We will skip these two settings, only enter the value “Belgrade” in the Current Value field. We did this because our goal is to filter the query to show only the amount of sales in Belgrade.

Let’s go back to Turnover. In the View ribbon, the Formula Bar option must be selected in order to see the formula on which the query originated. Then we should filter GRAD column. We will apply a text filter by selecting “F”, after which we get an empty query table.

By modifying the query in the formula bar, we will remove quotes so that Power Query knows that we are not referring to the value of “F” but a parameter with that name. After confirming the selection a query table will be filtered and show only sales in Belgrade. By changing the parameter, we could see sales in another city without affecting the query itself.