Use Excel Tables to Filter a Power Query

A question came up in the Excelguru forums today about how to use Excel tables to filter a Power Query.  While Power Query can't read a filter from an Excel table natively, there is a cool little trick that you can do to flow that information through though.

Data Background

The data footprint I'm working with looks like this:

3 tables showing the original data, a table of just years, and the final output with all rows

The Data query is a fairly simple staging query, pulling the data from the Excel table on the left, setting data types, and loading as Connection Only.

The YearFilter query is a little more complicated, as it pulls the data, removes duplicates, and then drills down into the Year column (right click the header -> Drill Down), resulting in a unique list of the Years:

The YearFilter results in a unique list of years in Power Query

And finally the Sales Query, which - shown in an indented and 'colourfied' format thanks to MonkeyTools QuerySleuth - looks like this:

The M code of the Sales query shown in Monkey Tools

The important things to notice about this query are:

  • It references the Data query (no new data is added here)
  • The Filtered Rows step filters to include any item that is in the list generated by the YearFilter list
  • The Filtered Rows step had to be adjusted manually to add the List.Contains function
  • The [Year] column refers to the [Year] column of the Sales query (which flows through from the original data)

So What's the Issue?

We want to use the filter on the YearFilter table in Excel to filter our Power Query.  Unfortunately, that doesn't happen... despite a refresh, all the years are still in the worksheet after setting that filter:

Despite filtering the Excel table, the output isn't filtered

The challenge, when you are attempting to use Excel tables to filter a Power Query, is that Excel can't read the filter.  In fact, Power Query can't access any of the table's metadata about filters or the visible state of the rows.  It therefore brings in all rows from the table whether they are hidden or not.

Using Excel Tables to Filter a Power Query

The secret here is that we need a way to tell Power Query which rows are visible versus which are hidden.  Something we can do by leveraging the AGGREGATE function, since it has the ability to count only visible rows.

The formula I used was =AGGREGATE(3,5,[@Year]) where:

  • 3 indicates the COUNTA() function
  • 5 sets it to ignore Hidden rows
  • [@Year] points to the current row of the Year column

The weird part, if you've never done this before, is that all the visible rows in Excel will always show a 1. But look what happens when you filter to only a couple of years, then edit the YearFilter Query and select the Source step:Using AGGREGATE, Power Query lets us see the visible and hidden rows in the table Boom!  We can see which rows are visible (indicated with a 1) and which are hidden (indicated with a 0).  This now becomes a pretty easy fix:

  • Filter the Display column to 1

And you're done.  The rest of the query will still work, as it drills in to the list of years, so we don't even need to remove this new column.

And just like that, we can now use Excel tables to filter a Power Query:

Setting a filter on the Excel table now filters our Power Query

 

Update to Monkey Tools QuerySleuth

We've been kind of quiet here, but we're excited to announce that we've just published an update to Monkey Tools QuerySleuth feature.  It now contains an "tabbed" experience so that you can easily flip back and forth between queries, "pinning" the ones you want to see and compare.

The Updated QuerySleuth Interface

In this case you'll notice that I pinned The ChitDetails and ChitHeaders queries, then selected the Locations query from the left menu.

An image of the update to Monkey Tools QuerySleuth showing the new tabbed interface indicating two pinned queries and two modified queries

Why does this matter?  Did you notice that the ChitDetails and Locations tab names are both red?  That's because I made changes to both of them to update a data type... I can now hold onto those changes as I flip back and forth between JUST the queries I want to keep in focus.

Updating Multiple Queries

But now, of course, I want to commit my changes and force the data model to update to reflect those changes.  In this image, I'm doing just that, with three queries:

An image of the QuerySleuth prompting the user to ask which queries they want to save and refresh

And due to the selection pointed out by the arrow, each of these queries will not only get saved back to the Power Query engine, but a refresh of each query will be triggered as well.

So how do you get this update to Monkey Tools QuerySleuth?

This update to Monkey Tools QuerySleuth is available in Monkey Tools 1.0.7553.5975 or higher.  And it's available in both the free and Pro versions of the tool.  (Of course, you will still need a Pro version in order to actually save your queries.)

To try our free trial, head over to the Monkey Tools product page to download your copy.

If you already have Monkey Tools installed, it will automatically update within a couple of weeks, or you can request the update now by going to Monkey Tools -> Options -> Check For Update Now…

Creating Power Query Based Calendars

We’re super excited to announce that we have given the Monkey Tools calendar creator feature an upgrade.  In fact, it is so much of a power up, that this feature has graduated into its own full grown monkey!  We call it the Calendar Monkey.

In our initial version, the Calendar Creator would create the queries necessary to load a single column calendar of unique dates into the data model and add PeriodID columns for 364-day calendars like 445 and 13 fiscal periods.

The Calendar Monkey adds a couple of pretty powerful features to this original mix.  Let’s walk through the experience quickly to see what I mean.

Step 1:  Define the Calendar Boundaries

Step 1 screen of the Calendar Monkey, allowing you to choose your calendar type, the calendar start/end boundaries and year end

Not much has really changed here.  You can still:

  • Pick your calendar type (12 month, 13 month, 445, 454, 544), and define your custom year end (including a different month for 12 month calendars.)
  • Define a name for your calendar table query
  • Choose the load destination
  • Pick any valid date columns for the Start and End date of your data

These settings allow our monkey to build the calendar to dynamically span the entire range of your data on every refresh.

The only real difference here is that we’ve added a checkbox and a Next button.  (Notice that Create is still available, if you just want to accept the monkey’s default choices for the rest of the options you’re about to see.)

Step 2:  Choose Calendar Columns

One of the things that always bothered us about our original version is that it created the calendar’s Date column, but then left it up to you to add the different date formats that you wanted.  So we decided to improve that, as you can see here:

Step 2 of the Calendar Monkey screen provides a series of check boxes allowing you to specify which date format columns you'd like to add to your model

Our monkey pre-selects the most common date formats, but if you ever check/uncheck one, it will learn your preferred defaults and provide those next time you go to inject a calendar.  The columns shown dynamically react to your choices in Step 1 as well… if you use a Dec 31 year end for a 12 month calendar, you only need the first two columns – so that’s what the Calendar Monkey will show you.  If you have a custom year end (like Jun 30 or Sep 30), you may also want Fiscal columns, so the monkey provides those as options too.  And if you work with a 364-day calendar like a 445 variant or a 13 fiscal periods calendar… there is a final column of PeriodID’s that shows up in that blank spot too.

Step 3: Adding Relationships

Depending on your choices in Step 1, the Calendar Monkey will determine if you will be presented with this page or if it will be skipped.  If you choose to load your calendar to the Data Model (or Data Model & Worksheet), the monkey will list every date column loaded to the data model.  The purpose of this is simple; let you decide if your new calendar table should be linked to any of those columns listed.

For any columns you check, Calendar Monkey will do its best to create those relationships after loading your table to the data model.  (There are some things that can prevent the monkey from accomplishing this, such as creating an inactive relationship.)

Step 3 of the Calendar Monkey provides checkboxes for each date column in the data model, allowing you to declare which tables you'd like to relate your calendar table to.

Step 4: Creating the Calendar

Even though the Calendar Monkey has a lot of work to do when you click create, it also knows the value of good feedback.  For this reason, it will update you as to the progress as it completes all the individual tasks, as you can see here:

This screen shows feedback from the Calendar Monkey, letting you know what has been done, as well as what you need to do need.

Now unfortunately, there are a couple of things that the monkey is unable to do (thanks to a lack of security clearance with the Excel data model).  Rather than just ignore these essential tasks, however, it will tell you what needs to be done, with the exact steps to do so.

As a bit of a pro-tip here… you don’t actually need to close the Calendar Monkey window to take action on those steps… so keep it open until you’ve hopped into the data model and made the advised changes!  Of course, if you understand what’s happening, and know the steps you’ll need to take, there is also an option to automatically close the summary screen upon completion as well.

Looking at the data model with the new calendar table already related to the other tables

What the Calendar Monkey cannot do

There are three things that the Calendar Monkey can’t do at this time:

  1. Automatically hide the keys on the “many” side of the relationship (the foreign keys)
  2. Automatically create the sorting hierarchies to sort Month Name by Month Number and Day Name by Weekday Number
  3. Create the calendar in a Power BI file

Honestly, while we joked earlier that the Calendar Monkey doesn’t have security clearance, the reality is that there is a limitation with the data model’s extensibility model which is preventing us from solving the first two items.  The third… its on our backlog.

So how do you get the new Calendar Monkey?

You need Monkey Tools version 1.0.7493.29574 or higher, and you'll have the Calendar Monkey ready to do your bidding.

If you haven’t already, head over to the Monkey Tools product page to download a copy

If you already have Monkey Tools installed, it will automatically update within a couple of weeks, or you can request the update now by going to Monkey Tools -> Options -> Check For Update Now…