Powerquery IN operator in conditional column

Question

How this m Power BI M code can be modified

= Table.AddColumn(#"PreviousStep", "Tag", each 
     if Text.Contains([Column1]) = "value1" then "bingo" 
else if Text.Contains([Column1]) = "value2" then "bingo"
else if Text.Contains([Column1]) = "value3" then "bingo"
else ["Some other value"])

into a one line code similar to SQL

case when [Column1] in ("value1", "value2", "value3") then "bingo" else "Some other value" end

I would not like to repeat the lines else if but have it in a similar way as

List.Contains({'Value1', 'Value2', 'Value3'}, [Column1])

used here: https://stackoverflow.com/a/51749519/1903793

Answer 1

If you want to compare whole word, you should use List.ContainsAny function

let
    haystack = #table({"col"}, {{"qwer"}, {"asdf"}, {"zxcv"}, {"zxwecv"}, {"other"}}),
    needles = {"qwer", "zxcv"},
    add = Table.AddColumn(haystack, "Tag", each if List.ContainsAny(needles, {[col]}) then "bingo" else "Some other value")
in
    add

But if you search a part of word, the answer becomes a little more complicated

let
    haystack = #table({"col"}, {{"qwer"}, {"asdf"}, {"zxcv"}, {"zxwecv"}, {"other"}}),
    needles = {"we", "as"},
    add = Table.AddColumn(haystack, "Tag", each if List.MatchesAny(needles, (s)=>Text.Contains([col], s)) then "bingo" else "Some other value")
in
    add

Answer 2

Or if you want to return the string that matches, you can use the List.Accumulate function:

List.Accumulate(YOUR_LIST_OF_STRINGS, null, (state, current) => if Text.Contains([YOUR COLUMN NAME], current) then current else state)

The only disadvantage to this method is that if there are multiple matches it will return only the last...

Here's a more complex version that returns a list of the matched strings:

List.Accumulate(YOUR_LIST_OF_STRINGS, {}, (state, current) => if Text.Contains([YOUR COLUMN NAME], current) then List.Combine({{current}, state}) else state)

Or you could amend that so that it returns a comma delimited list as a string etc., or whatever.

Answer 3

You have to use List.Transform to generate the Text.Contains function call, then use List.AnyTrue to check if Column1 contains any of the text.

= Table.AddColumn(#"PreviousStep", "Tag", each if List.AnyTrue(List.Transform({"value1", "value2", "value3"}, (s) => Text.Contains([Column1], (s)))) then "bingo" else "Some other value")

Results:

Reference