In this article we will show you how to use parameters within Power Query in PowerBI. Query parameters are a powerful way to dynamically interact with your data in Power BI. With the query parameters, users can define one or multiple parameters to be used in their queries. Stay tuned while we work through this exercise to demonstrate how query parameters work.
Consider a query with geographical information in it like Country, State, and City:
As you can see the query contains information on state and city names from a few countries across the globe. We would like to just focus on Europe and hence we would create a parameter to define a list of all the countries in Europe. We have already created a list query called Europe which we will use when we create the parameter.
Under the Home menu click on Manage Parameters to bring up the Parameters window:
Enter the data in the relevant sections like below and hit OK.
A new parameter EuropeanCountries is now created which we can now use to filter the query. Click on the Filter option in the Countries column and go to Text Filters-> equals… to bring up the Filter Rows window. Select Parameter instead of Text to choose the EuropeanCoutries parameter and hit OK.
Since we set the current value of the parameter to be Germany the query now lists only data from Germany
You can now dynamically update your charts by changing the current value. In the data model go to Home-> Edit Queries-> Edit Parameters to bring up the Enter Parameters window. From the drop down list choose the country you want and the query and any chart/table which references the Country field will be updated
This is just one example of how you can use parameters. If you would like to learn more about Power Query and Power BI then check out our Power BI Intermediate course.
Stay tuned for more Power BI articles from guest blogger Abhijith in the coming weeks!