BLOG

Using dynamic parameter values in Power Query Queries

06.05.2013 Stefan Grigat

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:

Power Query
Menu entry to chose source „from Database“

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:

Power Query
Resultset to hide unused Columns

 

The result looks like this:

Power Query
Result of the hiding operation

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”

Power Query
How to apply Row Filter

Adding also filters to the other columns the result looks like this:

Power Query
Resultset with all Filters applied

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:

Power Query

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.

Power Query

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:

Power Query

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.)

Power Query

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.

Power Query

After doing the same with the other parameters my script code is now:

Power Query

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).

 

Your email address will not be published. Required fields are marked *

Join #teamoraylispeople

Gestalte mit uns
die Welt der Daten