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):
Connect them on the filter columns.
Then create your Pivot table and deselect “empty” values.
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:
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:
load & choose “Only create Connection”.
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:
Choose “Load To Datamodel” this time (avoiding duplication of data in the sheets & enabling compression):
You can access your data model without entering the Power Pivot editor like this:
Have a look at the file
Enjoy & stay queryious 🙂
I’ve finally found exactly what I needed! I had a suspicion this can be done via a query.
Thanks you for the post.
Was is the blank that gets added to the external list?
You have to deselect it to make the filter work. That’s essentially telling the system: “Only take what’s in the filter table.”
So it’s what makes the filter work.
Thanks for this! Solved my Problem!
This is great! Thanks for posting it
Hi I cannot add pivot table to Data Model :/