Forum Discussion
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 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"
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
5 Replies
- 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"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!
- 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.
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
