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
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