Conditional Column with Replace

Hello CherC 

add a custom column (not conditional column) and put this formula

if Text.StartsWith([Team],"GSA") then Text.Replace([Team],"GSA",",") else [Team]

Here a complete example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg921E1MSk5RitWBcAyNjMHsUA8PXVNTM6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Team = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Team", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Region", each if Text.StartsWith([Team],"GSA") then Text.Replace([Team],"GSA",",") else [Team])
in
    #"Added Custom"

transforms this

into this

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

The following should work

Basically it starts with the table to change, creates a second table with two columns, the strings to find and the the strings to replace with.

Then uses a List.Accumulate which loops through each record of the original table a number of times for each find/replace combination from the second table.

Chandoo has a great explanation here 

https://chandoo.org/wp/multiple-find-replace-list-accumulate/ 

Note the sequence of the "GSA-US-" and the "GSA-US" is very significant. 

"GSA-US" needs to go second. 

This code will also work if there are multiple find/replaces in each record.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg921HUMcHTWTa1IzC3ISTVUitWBiLr6ujrCRI3goqHBMDFjJDGokAlWzaZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Team = _t]), ReplaceTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7JL03RVdJRUorViVZyD3bUdQxwdEYRcPV1dUQRCA1G4wJ5FRUVGBr8nIMdlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Find = _t, Replace = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Accumulate(List.Numbers(0,Table.RowCount(ReplaceTable)-1),[Team], (state,current)=>Text.Replace(state,ReplaceTable[Find]{current},ReplaceTable[Replace]{current}))) in #"Added Custom"

Please mark as resolved if this works. Any Kudos's appreciated.

Hi Jimmy, I don't know what happened yesterday but it seems to be working today.  Gremlins!  Thank you so much for your help!  You're the Best!