cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mwaltercpa Regular Visitor
Regular Visitor

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"

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-jiascu-msft
Microsoft

Re: Power Query - Replace multiple substrings in one column

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5

Re: Power Query - Replace multiple substrings in one column

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"

Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"
Power BI Blog
mwaltercpa Regular Visitor
Regular Visitor

Re: Power Query - Replace multiple substrings in one column

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!

Re: Power Query - Replace multiple substrings in one column

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.

Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"
Power BI Blog
mwaltercpa Regular Visitor
Regular Visitor

Re: Power Query - Replace multiple substrings in one column

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 🙂

Microsoft v-jiascu-msft
Microsoft

Re: Power Query - Replace multiple substrings in one column

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,518)