CODEWITHSUNDEEP
if-statementpowerbipowerquery
doubleD
doubleD

Reputation: 269

conditional column in M query if text.contain using OR function

I added a column in powerquery and the conditionals contain texts with varying values. I can add each phrase for each conditional but I am thinking of writing it in a more efficient way using the OR function.

so far I have the following:

= Table.AddColumn(#Changed Type", "Assigned Group", each if Text.Contains([Assigned to], "John") Then "Group 1" else if Text.Contains([Assigned to], "johnny") Then "Group 1" else if Text.Contains([Assigned to], "jane") Then "Group 1" else if Text.Contains([Assigned to], "sean") Then "Group 1" else if Text.Contains([Assigned to], "Mary") Then "Group 2" else if Text.Contains([Assigned to], "Pete") Then "Group 2" else "Group 3")

Like mentioned, is there a way to write this more efficiently without the long text like above?

Somehow I tried:

= Table.AddColumn(#Changed Type", "Assigned Group", each if Text.Contains(OR(([Assigned to], "John"), ([Assigned to], "johnny"), ([Assigned to], "jane"), ([Assigned to], "sean"))) Then "Group 1" else if Text.Contains(OR(([Assigned to], "Mary"), ([Assigned to], "Pete"))) Then "Group 2" else "Group 3")

It says that the Or is not recognized. I also tried the OR function before the text.contains phrase then it still not works.

How do I enter the OR function like it is in excel or maybe in power query.

Upvotes: 1

Views: 2980

Answers (1)

Aleksei Zhigulin
Aleksei Zhigulin

Reputation: 1634

First of all, keep in mind: M is case-sensitive language. So, Then isn't correct syntax. Also there are no OR function in PQ, but there is or operator.

You may use following code:

  = Table.AddColumn(
    #"Changed Type",
    "Assigned Group",
    each 
      let
        fn = (list) =>
          List.AnyTrue(
            List.Transform(list, (x) => Text.Contains([Assigned to], x, Comparer.OrdinalIgnoreCase))
          )
      in
        if fn({"John", "Jane", "Sean"}) then
          "Group 1"
        else if fn({"Mary", "Pete"}) then
          "Group 2"
        else
          "Group 3"
  )

Upvotes: 2

Related Questions