Use Timeline or Slicers to filter your Power Query import

A question in the Mr. Excel forum this morning reminded me that the technique I’ve blogged about here could also be used to do simpler things like using a timeline slicer to filter your Power Query imports. So here’s a quick shot on this:

  1. Import your calendar table into the data model (load only)
  2. Create a pivot on it with just one field: Date
  3. Put your timeline on it
  4. Return the resulting filtered pivot back to PQ by using an offset-function in a named range (no way to push this pivot directly back into PQ)
  5. Import the table to be filtered in the next step and merge in modus: JoinKind.Inner. This will only return the rows that have a match on both sides, thereby act as the filter we want. As this will allow query folding to happen (speed up your queries if accessing a SQL relational DB), do this as your first step before doing any other transformations on your source data.

Have a look at the file:

TimelineFilterPQ.xlsx

 

You will also find some exercises on filters on multiple columns in there if that is of interest.

Enjoy & stay queryious 🙂

Leave a Reply