Forum Discussion
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 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?
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/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] ),
4 Replies
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/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"})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/