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

score 4 · Answer 1 · edited Jun 20 '20 at 09:12

4

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

edited Jun 20 '20 at 09:12

Community

1
1

answered Aug 31 '18 at 23:54

Sergey Lossev

1,430
10
20

Your solution for part of phrase looks very similar to Foxan Ng answer `(s) => Text.Contains([Column1], (s))`. Would you explain the difference? – Przemyslaw Remin Sep 03 '18 at 07:51
The difference is that Foxan Ng makes two evaluations - 1) transforms list to true/false if item contains text passing check-function, 2) checks if any of them is true. In my version I check `if List.MatchesAny` and pass check-function (like Foxan Ng does) – Sergey Lossev Sep 03 '18 at 08:04

score 1 · Answer 2 · answered Jun 27 '19 at 03:44

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.

score 0 · Accepted Answer · answered Aug 20 '18 at 10:52

0

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

answered Aug 20 '18 at 10:52

Foxan Ng

6,883
4
34
41

Thanks. The one line solution looks complex. Really no simpler way? – Przemyslaw Remin Aug 20 '18 at 11:54

Powerquery IN operator in conditional column

3 Answers3

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

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

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