mardi 16 mars 2021

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.

Aucun commentaire:

Enregistrer un commentaire