Power Query – Controlling M Query Functions with User Driven Parameters

Have you ever had a user run a query against one of your largest tables only for them to immediately filter the results in Excel to show the last years worth of data? All of that data brought across your network and then immediately filtered out. Or maybe Excel just can handle the amount of unfiltered data they’re trying to return.

In this post I’d like to show you a way to solve this problem with Power Query in a solution that can dynamically filter your data returned based on user driven parameters.

In the scenario that I will demonstrate, rather then returning an entire result of all the company’s employees, I just want to return a list that show employees with a hire date within a certain range of dates that I or a user will provide. Using Power Query’s ability to make queries into Functions I’ll give my user the ability to provide the range of their choice

Connect to the Data

  • In my example I’m going use a SQL Server table as my data source but it could be any type of table. To connect to a SQL Server table from the Power Query tab in Excel select From Database > From SQL Server Database. For my example I’ll be using the AdventureWorksDW sample database.

image

  • Next you will be prompted to provide your Server and Database names where the table is located. Type these in then click OK.
  • Once you provide the Server and Database name you will also be prompted for the credentials you will use to access the data then click Connect.
  • The Navigator pane will appear showing all the available tables.  If you’re following my example with the AdventureWorksDW sample database then choose DimEmployee and then click Edit. This returns back the table to the Power Query Editor. **Note** This could be done on any table from any database.

Making the Query a Function

  • What I’d like to have my users do is return back this list of employees but only when the HireDate column falls within a range of values that they provide. To do this we’ll start by applying a hardcoded value to filter to the HireDate column. Find the HireDate column and apply a filter by clicking the down arrow next to the column and then Date Filters > Between

image

  • The range of values you filter on will depend on your table but for the DimEmployee table in AdventureWorksDW I used the following filter then clicked OK.

image

  • This simple places a filter on the query. If we want to make the filter dynamic we need to modify the M Query that’s behind the user interface. Go to the View tab on the Query Editor ribbon and select Advanced Editor. This will open the query window where you can modify

image

  • Next modify this query to add in a start date and end date parameter with the code in red below.

(startdate, enddate)=>
let
    Source = Sql.Database("localhost", "AdventureWorksDW2012"),
    dbo_DimEmployee = Source{[Schema="dbo",Item="DimEmployee"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_DimEmployee, each [HireDate] >= #date(2000, 1, 1) and [HireDate] <= #date(2002, 1, 1))
in
    #"Filtered Rows"

  • Once the parameters are created you can reference them in the query to replace the hardcoded value in the filter with a dynamic value from the parameters.  Modify the query with the code in red below then click Done.

(startdate, enddate)=>
let
    Source = Sql.Database("localhost", "AdventureWorksDW2012"),
    dbo_DimEmployee = Source{[Schema="dbo",Item="DimEmployee"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_DimEmployee, each [HireDate] >= #date(Date.Year(startdate), Date.Month(startdate), Date.Day(startdate)) and [HireDate] <= #date(Date.Year(enddate), Date.Month(enddate), Date.Day(enddate)))
in
    #"Filtered Rows"

  • This will convert the query into a function. You can test this function by clicking Invoke and then you will be prompted to provide date values to filter on.

image

image

  • If you did invoke the function make sure you remove the Invoke step before moving on. You can do this by clicking the delete icon in the Applied Steps pane.

image

  • This should return the query back to a function ready to be invoked. Now, go to the Home tab on the Query Editor ribbon and select Close & Load.
  • This will save the M Query function into the workbook but does not return any results yet.  Just how we want it! Our next step is to pass the values we want into the function.

image

Making it User Interactive

  • Go to a blank spreadsheet and create a simple Excel table that has a StartDate and Endate column with one row of values like this:

image

  • To make it so our users can type a value in this Excel Table and pass it into our function we need to take bring this small table into Power Query. Select one of the cells inside the table and on the Power Query tab select From Table. This will take the content of this table and bring it into the Power Query Editor.

image

  • To pass these two values into our function go to the Add Column tab and select Add Custom Column.
  • Write the following formula to connect the DimEmployee function created earlier to the dates we’ve now defined in the Excel table then click OK:

DimEmployee([StartDate],[EndDate])

image

  • If all your default settings are turned on inside Power Query you will probably get a privacy warning pop up.  This is because you’re working with two different data sources (1. SQL Server Table, 2. Excel Table) and there’s potential privacy concerns. In our scenario there are no legitimate privacy concerns so I’ll hit Continue. I also set the data sources to Organizational because the data sources should be contained within my company. Read more about Power Query privacy settings here.

image

  • Once the privacy settings are configured Power Query will add in a new column just called Custom (We could have renamed it previously). Click the Expand button next to the Custom column, uncheck Use original column name as prefix and then click OK. This will bring back all the rows that have hire dates between our date range.

image

  • Go ahead and remove the StartDate and EndDate columns from the query now but multi-selecting them and then right-click and select Remove Columns.
  • Next, rename the query in the Query Settings pane to Employee Data then click Close & Load on the Home tab.
  • You should now have two spreadsheets (It would obviously be a good idea to rename these) in your workbook.
    1. Sheet1 that has the Excel table with the date range values
    2. Sheet2 that has the results of the Power Query query. This data could have optionally been send to the Power Pivot Data Model
  • Now go back to Sheet1 and change the date range values in the StartDate and/or EndDate columns. After making this change the next time the Power Query query is refreshed it will pick up the results from the table to filter on saving your users from query a really large table when they only need a subset of the data.

Finishing Touches

If my users aren’t very familiar with Power Query and don’t know how to refresh their queries then we can build a quick little macro to provide a button that does it for them. Using a technique I learned from Ken Puls (blog | twitter) in a few short steps our macro will be done. In Ken’s post titled Refresh Power Query with VBA he shows how to refresh all Power Query queries in a workbook with a little VBA script.  I’m going to take the same principles he shows but just refresh the query I care about.

  • In your workbook press Alt + F11
  • Right-click on VBAProject(Book1), this may be named something different if you’ve saved with a new name, in the Project Explorer and select Insert > Module.

image

  • Use the following VBA script to refresh the workbook connection for our Employee query we created earlier (If you named your query something different you may need to adjust the section highlighted in red):

Public Sub UpdateEmployeeQuery()
‘ Macro to update my Power Query script(s)

Dim cn As WorkbookConnection

For Each cn In ThisWorkbook.Connections
If cn = "Power Query – Employee" Then cn.Refresh
Next cn
End Sub

  • Hit close on the VBA window.
  • To manually try the new code hit Alt + F8 and you will be prompted to run the script. Select the Macro we just created and click Run.

image

  • You should notice this kicks off the refresh of our Employee query.

image

image

  • Click somewhere in the worksheet that you want the button and then select the Macro we created to assign to the button. Click OK.

image

  • Click inside the button to rename it and then you’re all set!
  • Now all you have to do is change the values in the table and click the button to refresh the results of the query. This works if the results are rendered to an Excel spreadsheet or Power Pivot Data Model.

image 

As long as the data source and types of transforms support it Query Folding will still be utilized with this method. If you’re  curious about what Query folding is read more about it in this Matt Masson post.

I’ve made this example available if you would like to download it: User Driven Parameter Example.xlsx

Advertisements

13 comments

  1. I’m in the situation where I want to get data from a very large SQL Table (over 5 million rows) AND also from a large SharePoint List (over 100,00 rows and 120 columns).

    Its critical that I do some filtering of rows and columns as part of the query NOT after the data is exported.

    Question: Am I correct that in the case of a SQL DB I can just build a selection statement using standard SQL?

    Bigger Question: In the case that the source data is in a SharePoint List I have the same need. I want to build a query string to get a subset of the data. In pure Odata I know how to do this. I could also do it with a Linq query. But in Excel I have two options 1) Power Query to SharePoint of Power Query to odata (listdata.svc) to SharePoint.

    But how to I modify the connection to not get the whole LIST. One of my lists alone is generating 1.2 GB of download… but I only need about 4 MB.

    HELP!!!

  2. Chris, you probably need to create a CAML Query for your SharePoint list. I’ve done it in PowerShell, but not in VBA so can’t give you specific code and I have no idea how to do CAML in Power Query 🙂

    1. Ian,

      Thanks for the feedback. The problem is that Power Query does not support CAML queries. Its using the REST service listdata.svc. To make matters worse I have not worked out the real problem. The listdata.svc service has a bug .. it is not using indexes when the column is a date column. So if you have a list with over 5,000 rows and you want just those records modified in the last 3 days it will fail. It works for other indexed columns.

      Power Query says “not our problem” and listdata.svc says “why do you need to have an indexed filter on date type columns — idiots.

      My only hope is that the Power Query/Power BI group goes and wacks the listdata.svc programmers over the head with something heavy LOL

      Later,

      Chris M.

      1. Yeah, I kind of thought that would be a problem 🙂 Could you use a bit if VBA on workbook open (or some other workbook event) that would execute a CAML query to a file and then use that as the input to Power Query? Trying to think sideways here 🙂

  3. Great post ! Very usefull. But what about performance ? I’ve done a query filtered with parameters and the query returns 14000 rows in 1min 35 sec. The same query, but not filtered returns 28000 rows in less than a minute. Strange !!!

    So, is it possible to fine tune the query to get a better performance ?

    Thanks in advance !!

    Hugues

  4. Very helpful piece of information, thank you very much! Take me much time to google it though, should be google’s homepage. Thanks again.

  5. Great blog posting. Helped loads as just implementing some parameter functions.
    In the last section of the VBA automation i noticed the data connection in Excel 2013 for a query called “test”
    needs to be hardcoded as “Query – test”
    That is i had to drop the “Power” bit to make it work.

  6. Good timing on this post – it’s what I needed to get across an issue I was having. My dataset uses a stored procedure that takes parameters, so the final M query differs from yours, but using the parameters and the VBA automation were key to allowing the users to change the parameters without too much difficulty! 🙂

  7. For whatever reason my date column is imported into Power Query as datetime even though i converted to date in SSMS. Is it possible to modify the parameters in the advanced editor using datetime?

    Thanks,
    Alec

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s