Filter multiple columns by partially matching values from a list in M Language

[freelensia]

Hi,

In M Language I know that I can filter multiple values from multiple columns like this:

    FilterColsByKeywords = Table.SelectRows(GetWordsTbl, each Text.Contains([English], "system") or Text.Contains([Vietnamese], "hệ thống") or Text.Contains([Japanese], "システム"))

Now imagine  the keywords are not hard-coded, but come from a list like this:

system
hệ thống
システム
another keyword 1
another keywords 2

How can I keep only the rows whereby at least one column among [English], [Vietnamese] [Japanese] match at least one of those keywords?

Any ideas, @ImkeF @v-juanli-msft @Nolock @Mariusz  ?

Thanks and I hope everybody is safe during coronavirus outbreak

[v-frfei-msft]

Hi @freelensia ,

I have created a sample for your reference. Please check the following steps as below.

1. create a custom column as below.

List.Contains(ValuesTable[Results],[Code 1]) and List.Contains(ValuesTable[Results],[Code 2]) and List.Contains(ValuesTable[Results],[Code 3]) and List.Contains(ValuesTable[Results],[Code 4])

2. Filter the table based on the custom column.

Full code for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSjRU0lECoiQjCK0UqxON4CCEwOpSTIBEsjGMwC8BE0YypMrIDC5QaWSKpBIhABKLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Code 1" = _t, #"Code 2" = _t, #"Code 3" = _t, #"Code 4" = _t, #"Code 5" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Contains(ValuesTable[Results],[Code 1]) and List.Contains(ValuesTable[Results],[Code 2]) and List.Contains(ValuesTable[Results],[Code 3]) and List.Contains(ValuesTable[Results],[Code 4])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true))
in
    #"Filtered Rows"

Btw, please find the pbix as attached.

[freelensia]

Hi @v-frfei-msft thanks for the quick support.

I tried your solution but I think it is not getting me the desired results.

each List.Contains(ValuesTable[Results],[Code 1])

In this code the system checks in ValuesTable[Results] (a list) whether [Code 1] (a value) is found, yes?

I want the reverse.

I want to check in [Code 1] whether any of the values in ValuesTable[Results] are found.

The difference is not shown in your file because your values are simply a1, b2, c3 for both ValuesTable[Results] and [Code 1].

If you make [Code 1] as:

a1, b2, c3, d4
a1, d4
c3
b2, c3, d4

 Then it will not work as I desire.

Could you check once more?
Thanks!