Forum Discussion

mwaltercpa's avatar
mwaltercpa
Advocate III
7 years ago
Solved

Power Query - Replace multiple substrings in one column

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...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

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

    Power-Query-Replace-multiple-substrings-in-one-column

     

    Best Regards,
    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

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

Power-Query-Replace-multiple-substrings-in-one-column

 

Best Regards,
Dale