Forum Discussion
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 New Canaan (CT014), Avalon Darien (CT017), Avalon Norwalk Retail (CT021) |
| Avalon East NorwalkSOLD (CT026), Avalon New Canaan (CT014), Avalon NorwalkSOLD (CT022), Avalon Wilton on DanburySOLD (CT024) |
| Avalon East NorwalkSOLD (CT026), Avalon New Canaan (CT014), Avalon Wilton on River Rd (CT005), Avalon Norwalk Retail (CT021) |
| Avalon East NorwalkSOLD (CT026), Avalon New Canaan (CT014), Avalon Wilton on River Rd (CT005), Avalon Norwalk Retail (CT021) |
| AVA DoBro (NY037), Avalon Clinton North (NY533), Avalon Clinton South (NY534), AVA Fort Greene (NY026), Avalon MamaroneckSOLD (NY006), Avalon Midtown West (NY525), Avalon Morningside Park (NY823), Avalon OssiningSOLD (NY035), Avalon Riverview (NY011), Avalon Riverview North (NY018), Avalon West Chelsea (NY829), Avalon White Plains (NY022), Avalon Willoughby Square (NY039), Avalon Yonkers (NY047), Avalon Bowery Place II - JV (NY821), Avalon Green ISOLD (NY003), Avalon Green IISOLD (NY032), Avalon Green IIISOLD (NY038), AVA High Line (NY834), Avalon Bowery Place I (NY815) |
Reference Data to use for replacement
| Reference | New ID |
| eaves Quincy (MA003) | MA003 eaves Quincy |
| Avalon at The Pinehills (MA016) | MA016 Avalon at The Pinehills |
| Avalon at the Hingham Shipyard (MA027) | MA027 Avalon at the Hingham Shipyard |
| Avalon Northborough (MA030) | MA030 Avalon Northborough |
| Avalon Northborough II (MA032) | MA032 Avalon Northborough II |
| Avalon at The Pinehills II (MA033) | MA033 Avalon at The Pinehills II |
| Avalon Natick (MA037) | MA037 Avalon Natick |
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!
2 Replies
I have over 300 + new id's.
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!
