Ever had a user run a query on one of your largest spreadsheets, only to have Excel immediately filter the results to show data for the last few years? All this data is transmitted over your network and then immediately filtered out. Or maybe Excel just can't handle the amount of unfiltered data they're trying to return. In this post, I want to show you a way to solve this problem using Power Query in a solution that can dynamically filter your returned data based on user-controlled parameters. In the scenario I'm going to demonstrate, instead of returning a full result of all employees in the company, I just want to return a list showing employees with a hire date within a specific date range that I or a user will specify. With Power Query's ability to build queries in functions, I give my users the ability to provide the scope of their choiceConnect to the data
Make a function out of the query
- The range of values you filter by depends on your table, but for the DimEmployee table in AdventureWorksDW I used the following filter and then clickedOK.
- This simply puts a filter on the query. If we want to make the filter dynamic, we need to change the M query which is behind the UI. On the Query Editor ribbon, go to the View tab and selectAdvanced editor. This will open the query window where you can make changes
- Next, modify this query to add a start date and end date parameter with the code highlighted in red below.
( start date , end date ) =>
Leave
Quelle = Sql.Database("localhost", "AdventureWorksDW2012"),
dbo_DimEmployee = Quelle{[Schema="dbo",Item="DimEmployee"]}[Daten],
#"Filtered Rows" = Table.SelectRows(dbo_DimEmployee, jeweils [HireDate] >= #date(2000, 1, 1) und [HireDate] <= #date(2002, 1, 1))
in
#"Filtered rows"
- After the parameters are created, you can reference them in the query to replace the hard-coded value in the filter with a dynamic value from the parameters. Modify the query using the red code below, then clickCompleted.
( start date , end date ) =>
Leave
Quelle = Sql.Database("localhost", "AdventureWorksDW2012"),
dbo_DimEmployee = Quelle{[Schema="dbo",Item="DimEmployee"]}[Daten],
#"Filtered rows" = Table.SelectRows(dbo_DimEmployee, each [HireDate] >= #date(Date.Year(StartDate),Date.Month(StartDate),Date.Tag(Start date)) and [hired date] <= #date(date .year ( date),date.month (end date),Date .Day ( Date)))
in
#"Filtered rows"
- This turns the query into a function. You can test this function by clickingcalland then you will be prompted for date values to filter by.
- Once you've invoked the function, make sure you remove the Invoke step before proceeding. You can do this by clicking the delete icon in theApplied StepsFeld.
- This should return the query to a function ready to be called. Now go to the Home tab in the Query Editor ribbon and selectClose & Load.
- This saves the M-Query function in the workbook, but no results are returned yet. Just the way we want it! Our next step is to pass the desired values to the function.
Make the query user-interactive
- Go to an empty spreadsheet and create a simple Excel spreadsheet that has a StartDate and Endate column with a range of values like this:
- In order for our users to enter a value into this Excel spreadsheet and pass it to our function, we need to bring this little spreadsheet into Power Query. Select one of the cells in the table and select on the Power Query tabFrom table. This will bring the contents of that table into the Power Query Editor.
- To pass these two values to our function, go toAdd Columntap and selectAdd custom column.
- To connect the previously created DimEmployee function to the data we have now defined in the Excel spreadsheet, write the following formula, and then clickOK:
DimEmployees([Start Date],[End Date])
- If all of your default settings are enabled in Power Query, you'll likely see a privacy warning. This is because you are working with two different data sources (1st SQL Server spreadsheet, 2nd Excel spreadsheet) and there are potential privacy concerns. In our scenario, there are no legitimate privacy concerns, so I go for itContinue. I also set the data sources toOrganizationalbecause the data sources should be included in my company. Read more about Power Query's privacy settingshere.
- Once the privacy settings are configured, Power Query will add a new column called Custom (we could have renamed it earlier). press theExpandDisable the button next to the Custom columnUse the original column name as a prefixand then clickOK. This will show all rows of settings dates between our date range.
- Go ahead and now remove the StartDate and EndDate columns from the query but select them multiple times and then right click and select themremove columns.
- Next, in the Query Settings pane, rename the query to Employee Data, and then clickClose & Loadon the Home tab.
- You should now have two spreadsheets (it would be a good idea to rename them, of course) in your workbook.
- Sheet1 containing the Excel table with the date range values
- Sheet2 with the results of the Power Query query. This data could have optionally been sent to the Power Pivot data model
Final touches for Power Query
If my users are not very familiar with Power Query and don't know how to update their queries, we can create a quick little macro to provide a button that does this for them. Using a technique I learned from Ken Puls (the blog|chirp) in a few steps our macro is ready. In Ken's post entitledUpdate Power Query with VBAHe shows how to update all Power Query queries in a workbook with a small VBA script. I'll adopt the same principles he shows, but just update the query I care about.
- Press in your workbookAlt + F11
- Right click on VBAProject(Book1), this may have a different name if you saved with a new name, in the project explorer and select itInsert > Module.
- Use the following VBA script to update the workbook connection for our previously created Employee query (If you named your query differently, you may need to customize the section highlighted in red):
Public Sub UpdateEmployeeQuery()
' Macro to update my Power Query scripts
Dim cn As WorkbookConnection
For each cn in ThisWorkbook.Connections
if cn = "Power Query - Employees" Then cn.Update
next cn
End Sub
- In the VBA window, click Close.
- To manually try the new code hitAlt + F8and you will be prompted to run the script. Select the macro you just created and click on itTo run.
- You should note that this will trigger our staff query to update.
- To turn this into a button, go to the Developer tab. Instructions for making the developer tab visible.https://support.office.com/en-nz/article/Show-the-Developer-tab-e1192344-5e56-4d45-931b-e5fd9bea2d45
- ChooseInsert > Button
- Click anywhere in the worksheet where you want the button, and then select the macro we created to assign to the button. clickOK.
- Click the button to rename it and you're done!
- Now all you have to do is change the values in the table and click the button to update the query results. This works when the results are rendered in an Excel spreadsheet or a Power Pivot data model.
As long as the data source and transformation types support it, query folding will continue to be used with this method. If you're curious about what query folding is, read more about it in this Matt MassonPost.
I've provided this example if you'd like to download it:User Driven Parameter Example.xlsx
