I’ve been using Power Query (former Data Explorer) to query and combine different SQL Server sources over the last few weeks. One thing I recognized when adding filters was the fixed value for every condition. No expressions or Excel formulas can be used directly to filter queries. In this post I will show you how to use dynamic parameter values giving users the ability to adjust their query results without deep scripting language experience. In the example we’ll use Excel 2013 and the AdventureWorks2012.
First we will connect to the AdventureWorks DB and query one table:
How to connect to a SQL Server Database with the Power Query, managing connection and credentials can be found here: Microsoft
Having established the connection I will use the Employee table in the HumanResources schema. This table gives the ability to use filter on different columns and column types. We will use three different columns to filter:
- “JobTitle” which is of type text.
- “Gender” which has only one char
- “SickLeaveHours” as number and
- “HireDate” as Date.
From the query result we hide all unused columns.
To hide the unused columns, mark the columns to use holding down the “STRG” key while marking the columns. Here “BusinessEntityID” to link to the person details and the columns we want to filter on:
The result looks like this:
Now we add static filters to the columns we want to set up dynamic filters later on. Click on the little triangle of each column and use “<Datatype> Filters”. The example shows the filter for “SickLeaveHours” with the fixed value of “40”
Adding also filters to the other columns the result looks like this:
I renamed each filter to identify it later. Now let’s take a look at the script code for these step by clicking on the script sign 
in the function line. If you cannot see this sign, enable the option “Enable Advanced Query Editing” in the Settings of Power Query. Click here so settings Dialog
The script code for our example should look like this:
As you can see all four filters have the fixed values that were defined by the filter Dialog. Now let’s setup our parameters to make the filter more dynamic. Therefor I added a tablecalled parameter to the excel workbook including names and values for my parameter.
We can make Power Query access this table and the values for each parameter with the following steps.
In Power Query use “From Table”, select the “Parameter” table and right click one of the values and click on “Drill Down”. In my example it is the value for “SickLeaveHours”. Opening the script code to Access the value of the cell in our parameter table it looks like:
I rename the two lines to identify it later and copy the lines. Accessing the script from the AdventureWorks query, I will paste these two lines. I add a comment to show you the new lines. (Don’t miss to add a comma “,” at the second pasted line.)
Now we have the lines to access the cell in our parameter table. We just have to replace the fixed value with the link to our cell.
After doing the same with the other parameters my script code is now:
Now we can change the values in Parameter Excel Table and click on “Refresh” in Power Query to have dynamic filters. The values can even have excel formulas included to produce values. You can download the example Workbook here
.
Much fun with dynamic filters in Power Query (former Data Explorer).
Kommentare (0)