Forum Discussion
PowerQuery -How to apply a parameter to all tabs within a spreadsheet
One approach to using a list of values as a filter is to create a filter table and use Merge with an inner join to filter the queries.
I created a filter table on a separate sheet in my workbook and put two countries in there:
Now I select the PopulationStatistics query and use Home>Merge Queries and configure it like this:
Because I'm using an inner join, this has the effect of removing rows from the PopulationStatistics table that don't exist in the CountryFilter query.
This will add a column to the right of the PopulationStatistics query called CountryFilter, but we don't need that, so just right-click it and select Remove.
I then repeat the whole merge process for the LandUse query (or whatever other query has country in it), then use Home>Close&Load.
When the Close&Load has finished, you can delete the additional sheet created for the filter query so that you just have one sheet for each of your original queries and one sheet for your filter table and the list of queries will show "Connection only" against the filter:
Now, when you change the rows in the filter table (add/remove/edit), you can refresh all queries to re-filter them.
Thanks, This is a great Idea. I am trying to create a list of Inventory Number as a parameter. However, it seems I could only choose one inventory number at a time. Does that mean I need to create a parameter for each Inventory Number? Or if there is a way to create a list of Inventory Number at once?
One approach to using a list of values as a filter is to create a filter table and use Merge with an inner join to filter the queries.
I created a filter table on a separate sheet in my workbook and put two countries in there:
Now I select the PopulationStatistics query and use Home>Merge Queries and configure it like this:
Because I'm using an inner join, this has the effect of removing rows from the PopulationStatistics table that don't exist in the CountryFilter query.
This will add a column to the right of the PopulationStatistics query called CountryFilter, but we don't need that, so just right-click it and select Remove.
I then repeat the whole merge process for the LandUse query (or whatever other query has country in it), then use Home>Close&Load.
When the Close&Load has finished, you can delete the additional sheet created for the filter query so that you just have one sheet for each of your original queries and one sheet for your filter table and the list of queries will show "Connection only" against the filter:
Now, when you change the rows in the filter table (add/remove/edit), you can refresh all queries to re-filter them.
Thanks a lot. This works like a charm.
On a separate note, I am little surprised that we cannot create a list as a filter in power query. I thought it'd be easy if we could create a parameter equivalent to SQL like Inventory_number in ('aaaaa','bbbb','cccc')
I see nothing complex here as well. If we have such source and table with filter
that is one step to apply the filter and receive the result. Entire script is
let GetFilter = Excel.CurrentWorkbook(){[Name="tblFilter"]}[Content], Source = Excel.CurrentWorkbook(){[Name="tblSource"]}[Content], FilterTable = Table.SelectRows(Source, each List.Contains(GetFilter[A],[B])) in FilterTable
