A new feature has recently been added into Power Query – called Query Parameters. This was actually released to Power BI as part of May 2016’s update.
So what is a Query Parameter? For anyone familiar with Microsoft Reporting Services, it has pretty much the same functionality as a Report Parameter. In simple terms, it enables you to filter your data dynamically in Power Query. There has always been a Filter UI function but it could not be linked to a parameter or plugged into the M code. We can use Query Parameters as a way of reducing the data volumes brought into a PowerBook (useful for large datasets or tables) or to allow a user to switch between filters (e.g. changing database connections dynamically).
In this blog, I will concentrate on how we can reduce data volumes by implementing a Query Parameter. Look out for Part 2 in the coming weeks, where I will pick a use case for when we would want to dynamically switch between parameter values.
Reducing Data Volumes
Imagine transforming or just analysing a fact table that contains over 1 billion records. Not only will Power BI (and Power Query) probably crash or at best, perform badly, there is no need to bring all this data back into a PowerBook. A subset, or perhaps an individual month is all that is required to undertake the necessary Power Query ETL operations.
The below example demonstrates how a Query Parameter can be used to filter records in a given dataset. I have used the AdventureWorksDW2014 Database (download here) and picked the FactProductInventory table, which contains 776,286 rows. I will reduce this count by adding a new Query Parameter in Power Query – using the Datekey to find all rows in between the 31st November and 30th December 2010.
1. Assuming I have already loaded the data into the workbook, in the Query Editor, click Manage Parameters from the ribbon.
2. Create two parameters – one named DateKeyFrom and the other DateKeyTo. The former contains the value 20101131 and the latter 20101230. Keep the default settings and confirm by clicking OK. You two parameters should look like the below:
3. Click on the DateKey column drop down and choose Number Filters and Between – highlighted below.
4. Set the filter to pick up the two newly created parameters. The following screenshot illustrates the criteria required, with the key change involving changing the second drop down from ‘Decimal Number’ to ‘Parameter’.
5. Check that your dataset has been filtered and only contains data for the time period December 2010.
6. If you need to change the date criteria and let’s say, only display 5 days of data, this can be achieved easily clicking the parameters in the Queries pane. I have edited the DateKeyFrom value to 20101225. You can also update a value by clicking the Manage Properties button.
7. As soon as you click back onto the FactProductInventory table query, the data automatically begins to refresh. We are now left with only the last 5 days in December, shown below.
As Query Parameters are hot off the press, I have only just started experimenting with it. It looks like it has been added to not only reduce and filter on data, but to enable us to plug a dynamic parameters into M. Previously, I used a Function as a workaround but can now actually use them in the proper context. Click here to view a previous blog, where I specifically create and invoke a function. There are a number of other uses for Functions and Rafael Salas’ blog provides an excellent walkthrough.
My next blog will focus on how you can switch between different parameter values and dynamically display the results. If anyone has built their own Query Parameter examples and want to share them, feel free to leave me a comment below. I would be very interested to see how far people have already taken this new feature.
There are already a few great blogs and use cases out there:
1. Chris Webb’s Blog - https://blog.crossjoin.co.uk/2016/05/08/the-m-code-behind-power-bi-parameters/
2. Soheil Bakhshi Blog (1 of 2) – http://biinsight.com/power-bi-desktop-query-parameters-part-1/
3. Soheil Bakhshi Blog (2 of 2) - http://biinsight.com/power-bi-desktop-query-parameters-part2-dynamic-data-masking-and-query-parameters/