Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
2 years ago
Solved

How to replace text in a comma separated column with new value

I need to replace the original column text values with the New ID.  I need to maintain the commas.    Original Column    contract_communities_list Avalon Wilton on River Rd (CT005), Avalon ...
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi vbowlen

     

    Here is my solution. Add a custom column with below code. ReferenceDataTable is another table which has two columns "Reference" and "New ID", just like you have shown. Please notice that the first column should have old values and the second column should have new values. 

     

    = Text.Combine(List.ReplaceMatchingItems(List.Transform(Text.Split([contract_communities_list], ","), Text.Trim), Table.ToRows(ReferenceDataTable)), ", ")

     

     

    Here is a blog for your reference: Replace Values in Power Query M (Ultimate Guide) - BI Gorilla

     

    Best Regards,
    Jing
    If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi vbowlen

 

Here is my solution. Add a custom column with below code. ReferenceDataTable is another table which has two columns "Reference" and "New ID", just like you have shown. Please notice that the first column should have old values and the second column should have new values. 

 

= Text.Combine(List.ReplaceMatchingItems(List.Transform(Text.Split([contract_communities_list], ","), Text.Trim), Table.ToRows(ReferenceDataTable)), ", ")

 

 

Here is a blog for your reference: Replace Values in Power Query M (Ultimate Guide) - BI Gorilla

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!