Forum Discussion
Applying a common filter to multiple tables
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
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.
Will use multiple customers sir.
maintable should filter based on all customers available in customer table
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