Want to filter a PivotTable based on an external range or list?

In order to pass a list of filter arguments to your pivot table instead of selecting them all manually you can do the following:

1) Pass your filter table and your data table to the Data Model (Power Pivot):

2AddDataModel

Connect them on the filter columns.

3Connection

Then create your Pivot table and deselect “empty” values.

4Filter

If this a report to be repeated with different data in your table, you need to make sure that new values will also be taken into account:

5FieldSettings

 6IncludeNewElements

Are you thinking: “What a pretense – that’s a workaround, but not filter function….?!”

In my eyes it’s good enough, but there’s actually a leaner solution to it:

2) Pass your filter table to Power Query:

PQ1

load & choose “Only create Connection”.

PQ2OnlyCreateConnection

Then load your data table to Power Query. Merge (1) with filter table (2) on filter column (3). Choose “JoinKind.Inner” (4). This will then only return the rows that match in both tables (5). This way you don’t have to deselect the “empty” fields manually:

PQ3FullMonty

Choose “Load To Datamodel” this time (avoiding duplication of data in the sheets & enabling compression):

PQ4Load

You can access your data model without entering the Power Pivot editor like this:

PQ4ChooseConnection

PQ5Load

Have a look at the file

Enjoy & stay queryious 🙂

Leave a Reply