Conditional Find/Replace for Multiple Values

[CJ-W]

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 located in, I want it to replace the value in the Project Manager column with either Seattle / [Project Manager], Bellevue / [Project Manager], or if neither, then South / [Project Manager]. Sample data and intended result column below. 

Jobsite Address	Project Manager	Project Manager (intended result)
Address, Bellevue, WA 98005, USA, 98005	PM1	Bellevue / PM1
Address, Bellevue, WA 98005, USA, 98005	PM2	Bellevue / PM2
Address, Auburn, WA, USA, 98002	PM3	South / PM3
Address, Seattle, WA 98107, USA, 98107	PM3	Seattle / PM3
Address, SeaTac, Washington 98198, USA, 98198	PM3	South / PM3
Address, Seattle, WA 98116, USA, 98116	PM4	Seattle /
Address, Seattle, WA 98116, USA, 98116	PM4	Seattle / PM4
Address, Seattle, WA 98104, USA, 98104	PM4	Seattle / PM4
Address, BELLEVUE WA 98006, 98006	PM1	Bellevue / PM1

What I have so far is based off this solution, with my modifications below: 

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

However, Replaced Value1 is undoing the work of Replaced Value.  So... thoughts?

[tackytechtom · Accepted solution]

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/

[tackytechtom · Accepted solution]

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]

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"})

[tackytechtom]

Hi @CJ-W ,

Did you solve it in the end? If not, let me know or start a new question 🙂

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

[CJ-W · Accepted solution]

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]
        ),