Forum Discussion
Applying a common filter to multiple tables
Hi,
I need some help with the below example. We currenlty have a dashboard which uses multiple tables from different dataflows. These tables have data of different customers and for each dashboard we are using this data of different customers.
we are currently applying the customer filter in each table query. Something like below
Table 1:
Table.SelectRows(#"Changed Type", each Text.StartsWith([customer], "A") or Text.Contains([customer], "B") )
Table 2:
Table.SelectRows(#"Changed Type", each Text.StartsWith([customer], "A") or Text.Contains([customer], "B") )
Table 3:
Table.SelectRows(#"Changed Type", each Text.StartsWith([customer], "A") or Text.Contains([customer], "B") )
So everytime a new customer needs to be added or removed all the queries have to be manually updated and refreshed in local.
IS there a way where i can maintain a seperate customer table like below and pass this as a parameter to all the table level queries?
You will need to create 2 tables as you will not know the position of values for Text.StartsWith and for Text.Contains.
Let's call them CustomerList1 and CustomerList2. After creating them, right click on the column name - Drill down - This will convert both tables into lists.
Now, below formula will be used
= Table.SelectRows(#"Changed Type", each List.Contains(CustomerList1,[Customer],(x,y)=>Text.StartsWith(y,x)) or List.Contains(CustomerList2,[Customer],(x,y)=>Text.Contains(y,x)))Now, you will update/delete/add in these 2 tables only and queries will not need to be touched.
I have created an example in an Excel and uploaded to https://1drv.ms/x/s!Akd5y6ruJhvhug0s4OUNxC-itvnw?e=2J4cYM
In that Excel - Data menu - Queries and connections - Click on any query to test the logic.
You may be prompted for for Security Warning that External data connections have bene disabled - Click Enable Content
6 Replies
Yes, let's call this table as CustomerTbl with field name Active Customer. Then your query would be
Table.SelectRows(#"Changed Type", each Text.StartsWith([customer], CustomerTbl[Active Customer]{0}) or Text.Contains([customer], CustomerTbl[Active Customer]{1}) )
Another way is to create 2 parameters named Customer1 and Customer2 and then your query becomes
Table.SelectRows(#"Changed Type", each Text.StartsWith([customer], Customer1) or Text.Contains([customer], Customer2)
How to create parameter - https://docs.microsoft.com/en-us/power-query/power-query-query-parameters
but even in this case I have to update all the Query if i add another customer to the cusotmer table. Say if i add third customer then all table queries have to be updated with :
Table.SelectRows(#"Changed Type", each Text.StartsWith([customer], CustomerTbl[Active Customer]{0}) or Text.Contains([customer], CustomerTbl[Active Customer]{1} or Text.Contains([customer], CustomerTbl[Active Customer]{2}) )
Is there a way i can pass this customer list from customer table as a list for the filter?
I need to know following before creating a query -
Text.StartsWith - Will it use only 1 customer or can use multiple customers?
Text.Contains - This can anyway use multiple customers as demonstrated by you.
You could create 3 new queries, one per base table, by referencing the existing query. Remove all the columns except the customer column, append all 3 queries together and then remove duplicates.
