Forum Discussion

CJ-W's avatar
CJ-W
New Member
1 year ago
Solved

Conditional Find/Replace for Multiple Values

I'm trying to set up a formula to search one column (Jobsite Address) for one of two different values (Seattle or Bellevue), or for the absence of both. After identifying the city that the address is...
Need Help
  • tackytechtom's avatar
    tackytechtom
    1 year ago

    Hi CJ-W ,

     

    I think the problem is that your code references the same step "Sorted Rows" twice. Try to substitute the second "Sorted Rows" with "Replaced Value":

     

    Otherwise you are not working further with the result from the first "Replace Value" step, but instead from the "Sorted Row" step. Thats giving the notion of overwriting. 

    Referencing different steps in queries can be very powerful though. I use it all the time when I need to join the result of different steps e.g. after grouping and such.

     

    Let me know if this helps 🙂

    Also, I have seen this has been your first post on the community forum! A warm welcome 🙂

     

    /Tom
    https://www.tackytech.blog/
    https://www.instagram.com/tackytechtom/

  • CJ-W's avatar
    CJ-W
    1 year ago

    I did, thank you! I was over-complicating things. It was much easier to just create a custom column with the conditionals I needed, then delete the old columns. Code below for anyone else that needs a solution. 

     

        #"Added Custom" = Table.AddColumn(
        #"Changed Type", 
        "Team & PM", 
        each if Text.Contains([Jobsite Address], "Seattle", Comparer.OrdinalIgnoreCase) then "Seattle / " & [Project Manager]
        else if Text.Contains([Jobsite Address], "Bellevue", Comparer.OrdinalIgnoreCase) then "Bellevue / " & [Project Manager]
        else if Text.Contains([Jobsite Address], "Yarrow", Comparer.OrdinalIgnoreCase) then "Bellevue / " & [Project Manager]
        else if Text.Contains([Jobsite Address], "Kirkland", Comparer.OrdinalIgnoreCase) then "Bellevue / " & [Project Manager]
        else if Text.Contains([Jobsite Address], "Medina", Comparer.OrdinalIgnoreCase) then "Bellevue / " & [Project Manager]
        else "South / " & [Project Manager]
        ),
tackytechtom's avatar
tackytechtom
Most Valuable Professional
1 year ago

Hi CJ-W ,

 

I think the problem is that your code references the same step "Sorted Rows" twice. Try to substitute the second "Sorted Rows" with "Replaced Value":

 

Otherwise you are not working further with the result from the first "Replace Value" step, but instead from the "Sorted Row" step. Thats giving the notion of overwriting. 

Referencing different steps in queries can be very powerful though. I use it all the time when I need to join the result of different steps e.g. after grouping and such.

 

Let me know if this helps 🙂

Also, I have seen this has been your first post on the community forum! A warm welcome 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

  • CJ-W's avatar
    CJ-W
    New Member
    1 year ago

    Thank you for the solution and the welcome! That worked immediately, now I'm trying to figure out how to replace on multiple conditions not being met. I think I'm on the right track, but something isn't working. I think I may be using "or" incorrectly. 

        #"Replaced Value" = Table.ReplaceValue(
        #"Replaced Value - Bellevue",
        each if not Text.Contains([Jobsite Address], "Seattle" or "Bellevue", Comparer.OrdinalIgnoreCase) then [Project Manager] else false,
        each "South / " & [Project Manager],
        Replacer.ReplaceText,
        {"Project Manager"})

     

      • CJ-W's avatar
        CJ-W
        New Member
        1 year ago

        I did, thank you! I was over-complicating things. It was much easier to just create a custom column with the conditionals I needed, then delete the old columns. Code below for anyone else that needs a solution. 

         

            #"Added Custom" = Table.AddColumn(
        #"Changed Type", 
        "Team & PM", 
        each if Text.Contains([Jobsite Address], "Seattle", Comparer.OrdinalIgnoreCase) then "Seattle / " & [Project Manager]
        else if Text.Contains([Jobsite Address], "Bellevue", Comparer.OrdinalIgnoreCase) then "Bellevue / " & [Project Manager]
        else if Text.Contains([Jobsite Address], "Yarrow", Comparer.OrdinalIgnoreCase) then "Bellevue / " & [Project Manager]
        else if Text.Contains([Jobsite Address], "Kirkland", Comparer.OrdinalIgnoreCase) then "Bellevue / " & [Project Manager]
        else if Text.Contains([Jobsite Address], "Medina", Comparer.OrdinalIgnoreCase) then "Bellevue / " & [Project Manager]
        else "South / " & [Project Manager]
        ),