#PowerQuery – Filter a table based on another table column or list – and some Filter aha’s

One of my favourite features in Excel and Power BI is Power Query / M – and I just wanted to share a small trick when you want to filter a table on specific items.

Let’s imagine you want to filter a list of customers based on different CustomerCategoryID’s –

Using the interface, you would select the different categories via the filter menu

If you select 3, 4 and 5 – you will get this filter

= Table.SelectRows(Sales_Customers, each ([CustomerCategoryID] <> 6 and [CustomerCategoryID] <> 7))

Notice that it M creates an expression that excludes 6 and 7 and not specifically selects the 3, 4 and 5 – this means that when new customer categories is created they will be included in your query as well – perhaps not what you intended!!

If you only select 3 and 4 the expression built will be

= Table.SelectRows(Sales_Customers, each ([CustomerCategoryID] = 3 or [CustomerCategoryID] = 4))

So, it seems that if you pick more than half it will build and expression with and <> statement instead of and equal statement.

To make sure that only categories that you want to include or exclude you can use a list to specify the keys to be included

To create a list you can use this expression to

= {3..5} – will give you values from 3 to 5

Or

= {3,6,5} – will give you 3, 6 and 5

To filter your table, you now need to modify the Table.SelectRows expression

= Table.SelectRows(Sales_Customers, each ([CustomerCategoryID] = 3 or [CustomerCategoryID] = 4))

To

= Table.SelectRows(Sales_Customers, each List.Contains(Query1, [CustomerCategoryID]))

The List.Contains will check whether each row in the table will have a CustomerCategoryID number that exists in the list and return true if it does and your table will then only contains rows where True is returned

If you wanted to exclude the values that you have in your list you can change the expression to

= Table.SelectRows(Sales_Customers, each List.Contains(Query1, [CustomerCategoryID]) = false

Happy Querying