Pass a list as filter argument in Power Query

Hello,

The initial solution does work. Here are some details to make it work.

In Power Query, you must load a table containing your facts (lots of rows) and an other table containing the filter criterias (a few rows). Your Filter table must be converted to a List. The filter table will contain only one column, right-click on the column header and select Drill Down: the filter table is now a List.

List.Contains already includes the notion of "any". The function will go through each row in the fact table (in a specified field) and look for any of the values listed in the Filter List.

List.ContainsAny must be used to compare two lists, not a applicable for the example listed above.

Here is an example using BOTH functions:

My Fact Table named CRM contains 3 fields: Client, Date, Message

My Filter Table named Filter contained 1 field with 2 entries: "waiting" and "pending".

The Filter Table was drilled down to become a List.

The Message field contains different entries such as 

- waiting

- pending

- waiting for call back

- resolved

You must select New Source > Blank Query and type the following: 

SCENARIO 1

= Table.SelectRows(CRM, each List.Contains(Filter, [Message]))

The resulting table will contain all 3 fields from CRM table, and only the rows where the Message value is an exact match (this is case-sensitive) to either :

"waiting"

OR

"pending"

SCENARIO 2

If I want my filter to look into any of the words listed in a Message, I can transform each entry under Message into a "list" with Text.Split function. I will use " " (space) as a delimiter, therefore a Message value like "waiting for call back" will become a list of those 4 words.

= Table.SelectRows(CRM, each List.ContainsAny(Filter,Text.Split([Message]," ")))

The resulting table will contain all 3 fields from CRM table, and only the rows where the Message value contains either "waiting" or  "pending" :

"waiting"

"pending"

"waiting for call back"

Julie at Daxel.ca, offering training in Ottawa, Canada

Drew248