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:
- Import your calendar table into the data model (load only)
- Create a pivot on it with just one field: Date
- Put your timeline on it
- 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)
- 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 🙂