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

[Syndicate_Admin]

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

[Anonymous · Accepted solution]

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!

[Syndicate_Admin]

I have over 300 + new id's.