Power Query - Replace multiple substrings in one column

[mwaltercpa]

I am trying to write M in Power Query that passes the prior step #"Replaced Value", then proceeds to replace the names:

TRACY, MARCY, BARY with TRACI, MARCI, BARI. 

I found this code, but get the error: We can not convert a value of type list to type table.

Any thoughts on how to make this work?  -- Many Thanks for looking!

Here is my code:

    #"SubColVals" =
    Table.FromColumns(#"Replaced Value",
      let
        Source = ({{"TRACY","MARCY","BARY"}}),
        Substitutions = [
           TRACY = "TRACI",
           MARCY = "MARCI",
           BARY = "BARI"],
       Substituted = Table.TransformColumns(Source,{{"NAME",
        each Record.FieldOrDefault(Substitutions, _, _)}})
    in
      Substituted)
      in
        #"SubColVals"

[v-jiascu-msft · Accepted solution]

Hi @mwaltercpa,

Please download a demo from the attachment that has a solution of other approaches different from yours. 

Just at a glance, your code here may not work the way you need. And I think it's hard to replace in place. So I add a column with the desired result. Finally, you can delete the old column.

Since your actual data isn't clear, I created two possible types. Please refer to the snapshot below.

let index = [Index] in List.First(List.ReplaceMatchingItems(Table.SelectRows(#"Added Index", each [Index] = index)[Column1], {{"TRACY", "TRACI"}, {"MARCY","MARCI"}, {"BARY", "BARI"}}))

Text.Combine(List.ReplaceMatchingItems(Text.Split([Column2], ","), {{"TRACY", "TRACI"}, {"MARCY","MARCI"}, {"BARY", "BARI"},{" TRACY", "TRACI"}, {" MARCY","MARCI"}, {" BARY", "BARI"}}), ",")

Best Regards,
Dale

[GilbertQ]

Hi there

Could you not right click on an item in the table, select replace value and then do the following:
- What to find "CY:
- Replace with "CI"

[mwaltercpa]

Hey Gilbert, thanks for the response. Yes i could do that, however my goal is to use this simple example learn to write M and apply this to more complex requirements. Thanks!

[GilbertQ]

Hi there

I always suggest using the easiest way to get the job done. There will be times when there might be a more complex requirement. My suggestion is always to keep it as simple as possible and if I can do things via the GUI I do.

[mwaltercpa]

Thanks Gilbert, I do have a complex requirment beyond the UI capabilities. I'm keeping this simple for the person that can hlep me work with this. Thanks for your thoughts 🙂