Parameters in Power BI
Today we will follow on from our last data preparation blog post called Data Preparation Part 6 – Star Schema Power BI. We will explore the powerful functionality of parameters in Power BI. It is used in the Query Editor step of Power BI and is know as Query Parameters. Query Parameters may be added to a report or a query. Parameters may be used to automatically filter rows in a query, filter file locations, generate a measure definition and numerous other use cases.
In this post we will explore how a Query Parameter can be used for the following:
- Remove Rows
- Replace Values
- Edit Data Sources
Let’s open the query editor in order to create a parameter. Select ‘Manage Parameters’ and New Parameter from the Home tab once the query editor is opened.
We will explain the process of creating and using a Parameter by filtering rows in our data. We will use parameters to filter the below data set in order to only see data for England and exclude all other values.
We will follow a four-step approach in order to achieve this:
- Create a ‘normal’ filter to only show data from England
- Create a list of all our sales Countries
- Create the Parameter
- Edit the query editor so that the parameter automatically update the query
Firstly, filter the country column to only show data from England by selecting dropdown.
The result will filter data to only show entries from England.
Before creating our list of values for the parameter to automatically update, we must first understand where and how we will use it. Open the Advanced Editor to better understand how we will use parameters to automatically update queries.
There is a row called ‘#”Filtered Rows”‘ in the query editor that filtered the Country row to only show data from England. The entire row ‘#”Filtered Rows”‘ query reads as follow: #”Filtered Rows” = Table.SelectRows(#”Renamed Columns”, each ([Country] = “England”)). We would like to achieve two things by replacing the text in red with a parameter. Firstly, automatically update the query if we change our parameter. Secondly, to update our parameter from a list of values.
To achieve this we must create a list of countries from our sales table. Let’s see how we can achieve this. Firstly, duplicate the sales data table and rename it to Country_List. Remove the filter to see all the sales data and countries. Select the Country column and remove all duplicate so that only unique values remain.
To convert the country column to a list of values, select the list’ icon in the Transform tab. The result from these steps is a list containing all countries:
This list will now be used to create a new parameter. From the Home tab select, Parameter and then New Parameter.
We will name our query Sales_Country. The type will be text as our values are text-based. Under Selected Values we have three options:
- Any value, which allows you to enter any value
- List of values, this allows you to enter a list of values that you can choose from. You might think what is the difference between this and the list we created above. The big difference is that this list is only able to update manually, while the list created above updates automatically.
- Query, this allows you to select values from a created query, such as our Country_List.
Next, select Query and the list that we created and it is not necessary to enter any value in the Current Value field. Select OK to create the Parameter.
The Sales_Country Parameter will be displayed under the Queries navigation pane on the left-hand side of the Query Editor.
Before stepping through the process of including the parameter in the Sales_2018 query to update automatically, we will explain how to change the value of the parameter from Power BI desktop. Close the Query Editor and from Power BI desktop select Edit Queries and Edit Parameters. Thee Sales_Countries parameter is displayed in the list. Selecting the dropdown will show all countries that was created in our list. We will select Scotland to see how our values update automatically when we include our Parameter in the Advanced Query Editor of our Sales_2018 table.
Great, so let’s go back to the query editor now to set up the query of our Sales_2018 table so that it can update automatically based on the values we select from our Parameter. In the advanced editor of the Sales_2018 table, we must replace the ‘hardcoded’ value that we filtered our Country rows by and replace it with our created Parameter.
We will replace England with our Parameter, Sales_Country. When starting to type Sales_Country a list of names will appear, select Sales_Country or if it doesn’t appear we can type the entire name.
The result will look as follow:
Select done. The result is an updated table containing only values from Scotland, the value we selected earlier.
Let’s go back to Power BI desktop and test our solution a bit more. We see that the below graph contains only data from Scotland.
Let’s go to edit queries and change the country to England. A popup bar will appear at the top of the screen asking to apply changes. Select Apply Changes. The result will be as below:
Great now you have seen the power of Parameters in Power BI. This is an extremely powerful functionality to know well and to use in modelling solutions in Power BI.