Power Query is great for filtering data before it gets loaded into Excel, and when you do that you often need to provide a friendly way for end users to choose what data gets loaded exactly. I showed a number of different techniques for doing this last week at SQLBits but here’s my favourite: using Excel slicers.
Using the Adventure Works DW database in SQL Server as an example, imagine you wanted to load only only rows for a particular date or set of dates from the FactInternetSales table. The first step to doing this is to create a query that gets all of the data from the DimDate table (the date dimension you want to use for the filtering). Here’s the code for that query – there’s nothing interesting happening here, all I’m doing is removing unnecessary columns and renaming those that are left:
[sourcecode language=”text” padlinenumbers=”true”]
let
Source = Sql.Database("localhost", "adventure works dw"),
dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(dbo_DimDate,
{"DateKey", "FullDateAlternateKey", "EnglishDayNameOfWeek",
"EnglishMonthName", "CalendarYear"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{
{"FullDateAlternateKey", "Date"}, {"EnglishDayNameOfWeek", "Day"},
{"EnglishMonthName", "Month"}, {"CalendarYear", "Year"}})
in
#"Renamed Columns"
[/sourcecode]
Here’s what the output looks like:
Call this query Date and then load it to a table on a worksheet. Once you’ve done that you can create Excel slicers on that table (slicers can be created on tables as well as PivotTables in Excel 2013 but not in Excel 2010) by clicking inside it and then clicking the Slicer button on the Insert tab of the Excel ribbon:
Creating three slicers on the Day, Month and Year columns allows you to filter the table like so:
The idea here is to use the filtered rows from this table as parameters to control what is loaded from the FactInternetSales table. However, if you try to use Power Query to load data from an Excel table that has any kind of filter applied to it, you’ll find that you get all of the rows from that table. Luckily there is a way to determine whether a row in a table is visible or not and I found it in this article written by Excel MVP Charley Kyd:
http://www.exceluser.com/formulas/visible-column-in-excel-tables.htm
You have to create a new calculated column on the table in the worksheet with the following formula:
=(AGGREGATE(3,5,[@DateKey])>0)+0
This calculated column returns 1 on a row when it is visible, 0 when it is hidden by a filter. You can then load the table back into Power Query, and when you do you can then filter the table in your new query so that it only returns the rows where the Visible column contains 1 – that’s to say, the rows that are visible in Excel. Here’s the code for this second query, called SelectedDates:
[sourcecode language=”text”]
let
Source = Excel.CurrentWorkbook(){[Name="Date"]}[Content],
#"Filtered Rows" = Table.SelectRows(Source, each ([Visible] = 1)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Visible"})
in
#"Removed Columns"
[/sourcecode]
This query should not be loaded to the Excel Data Model or to the worksheet.
Next, you must use this table to filter the data from the FactInternetSales table. Here’s the code for a query that does that:
[sourcecode language=”text”]
let
Source = Sql.Database("localhost", "adventure works dw"),
dbo_FactInternetSales = Source{[Schema="dbo",Item="FactInternetSales"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(dbo_FactInternetSales,
{"ProductKey", "OrderDateKey", "CustomerKey", "SalesOrderNumber",
"SalesOrderLineNumber", "SalesAmount", "TaxAmt"}),
Merge = Table.NestedJoin(#"Removed Other Columns",{"OrderDateKey"},
SelectedDates,{"DateKey"},"NewColumn",JoinKind.Inner),
#"Removed Columns" = Table.RemoveColumns(Merge,
{"ProductKey", "OrderDateKey", "CustomerKey"}),
#"Expand NewColumn" = Table.ExpandTableColumn(#"Removed Columns",
"NewColumn", {"Date"}, {"Date"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expand NewColumn",
{"Date", "SalesOrderNumber", "SalesOrderLineNumber",
"SalesAmount", "TaxAmt"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{
{"SalesOrderNumber", "Sales Order Number"},
{"SalesOrderLineNumber", "Sales Order Line Number"},
{"SalesAmount", "Sales Amount"},
{"TaxAmt", "Tax Amount"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",
{{"Date", type date}})
in
#"Changed Type"
[/sourcecode]
Again, most of what this query does is fairly straightforward: removing and renaming columns. The important step where the filtering takes place is called Merge, and here the data from FactInternetSales is joined to the table returned by the SelectedDates query using an inline merge (see here for more details on how to do this):
The output of this query is a table containing rows filtered by the dates selected by the user in the slicers, which can then be loaded to a worksheet:
The last thing to do is to cut the slicers from the worksheet containing the Date table and paste them onto the worksheet containing the Internet Sales table:
You now have a query that displays rows from the FactInternetSales table that are filtered according to the selection made in the slicers. It would be nice if Power Query supported using slicers as a data source direct without using this workaround and you can vote for it to be implemented here.
You can download the sample workbook for this post here.
Chris,
I like your technique. Since I am marooned on Excel 2010, I decided to build an analog to your function using a fake table slicer using a pivot table and pivot table slicer to define selections on the Date table.
My question is, (since I can’t operate the 2013 file), how does the slicer selection event automatically update the FactInternetSales query loaded to the sheet? For 2010, I will add an event associated with the slicer selection change
It doesn’t – you either have to refresh manually or use some VBA
How can I refresh just one of the queries using VBA e.g. in the example above, I assume we have to refresh only the 2nd query so mimicking Data -> Refresh All via VBA is not an option.
No, you need to refresh all queries in this example…? Anyway, there are some more details of how to use VBA with Excel 2016 here: https://blog.crossjoin.co.uk/2015/06/10/power-queryexcel-2016-vba-examples/
Oh Wow…this is a game changer. Amazing Technique Chris.
This technique is really interesting but… I had some bad experience with applying slicers directly to a Table. It works fine in Excel 2013 until you want to refresh data. The problem is a kind of freeze caused by slicers, no matter if I turned the filters off or not, I had no new data rows. When I deleted slicers it was all ok. I suppose it was not a problem of Power Query but rather cooperation between table and slicers. If it works right now, it will be great.
Very nice Chris. Thanks for sharing
Great!
Hi Chris,
Thanks for interesting post.
I use, several months, Slicers based on Pivot Table (or PTs) to pass parameters to the PQ (Excel 2010).
I use a technique based on the SheetName! _FilterDatabase, because I can put a Pivot Table (one or more) within the filtered range. This works pretty good for me.
But will be nice to use Slicers directly :-))
Regards
P.S. You can use also =(SUBTOTAL(103,[@DateKey])) instead of AGGREGATE (parentheses have to be used)
Will this technique work in my data source is a table that contains millions of records? will the Excel table just refresh automatically when the slicer is changed?
I think you would need to test it – I don’t have any experience with MySQL. There’s a good chance it will work even with a large table, but only if the parameter table is small.
will the table take a long time to refresh if my source data comes from a table in an MySQL database?
my source table has millions of records
If we use this technique for SSAS, will it apply query folding. How exactly can we tell if it’s doing that or not?
You will have to run a Profiler trace or use Extended Events to see the MDX that is beign generated.
What should I be looking for in the Profiler trace though that will confirm that query folding is happening?
You should see SQL queries that replicate some or all of the logic (filters, transformations etc) that you have in your queries.
Hi Chris,
Is it possible to use Excel Slicers to pass parameters to Microsoft Azure or other cloud based database to pull cubed data? Similar to what Smart View does? Thanks!
You can publish a DirectQuery dataset to Power BI and connect an Excel PivotTable to it and that’s exactly what will happen