Forum Discussion

Alicia83B's avatar
Alicia83B
Helper I
4 years ago
Solved

Replace Values in Power Query Based on Conditions

I have an Opportunities Table with a column for Territory and Seller. I currently am replacing Mary’s territory from 1 to 16 using the below formula.

= Table.AddColumn(#"Changed Type1", "Chng Territory", each if [Assigned To] = "Mary" and [Territory] >=1 then "16" else [Territory])

However, I have two more sellers, Tim and Robin, that I need to add to the formula, but get an error message when I add “Mary” or “Tim” or “Robin” to my above formula.

Thank you in advance for taking a look at this and providing any help.

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    Replace if condition with either of these (2nd one is useful if you have many names)

    if ([Assigned To] = "Mary" or [Assigned To] = "Tim" or [Assigned To] = "Robin") and [Territory] >=1 then "16" else [Territory]

    if List.Contains({"Mary","Tim","Robin"},[Assigned To]) and [Territory] >=1 then "16" else [Territory]

2 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    Replace if condition with either of these (2nd one is useful if you have many names)

    if ([Assigned To] = "Mary" or [Assigned To] = "Tim" or [Assigned To] = "Robin") and [Territory] >=1 then "16" else [Territory]

    if List.Contains({"Mary","Tim","Robin"},[Assigned To]) and [Territory] >=1 then "16" else [Territory]