Power Query - Replace multiple substrings in one list column

[E_K_]

I am trying to do this for a column where the are text values listed in all sorts of ways, with comma as delimiter.

Eg

Old value	New value
ALE, AEX, DDI, ELV, PRT, ZAI, ZZI	Allegro, Aeronautix, Didatic, EalingAdvantage, Preet, Zone arti, Zanzibar
AEX, PRT, ZZI	Aeronautix, Preet Zanzibar
DDI, ELV	Didactic, EalingAdvantage

I found this solution for one where it is less complex https://community.powerbi.com/t5/Desktop/Power-Query-Replace-multiple-substrings-in-one-column/m-p/5...

however I do not want to split by delimiter into rows as that duplicates rows sometimes 100 times over if 100 values are replaced. for one single row. There are a lot of rows and this would make my dataset too large to perform efficiently.

There are around 150 value replacements needed in each cell that require replacement. Worth noting that all the values in any cell in this column are alphabetical

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"}}), ",")

[artpil]

Hi,

Chandoo created video "Multiple find/replace with List.Accumulate()" 

https://youtu.be/YWAMaas_1AU 

I think this approach will help you solve your problem. 

Artur

[Vijay_A_Verma]

Need some clarity on this requirement. If it is a matter of replace A with Aa and so on, it is very easy. But I believe this example is not right as in below code, you are replacing TRACY with TRACI and so on.

I would like to know that do you have a mapping table / list where I know TRACY has to be replaced with TRACI OR Y with I.....If yes, can you post this mapping table here?

[E_K_]

Te TRACY/I solution is from the link I posted - different dataset, slightly different requirements than mine, I tried sustituting my values in and playing around a little with the syntax but it did not work. https://community.powerbi.com/t5/Desktop/Power-Query-Replace-multiple-substrings-in-one-column/m-p/5...

I don't have a distinct list of the values within my dataset and don't want to break out into another one as a dimension table unless I absolutely have to. 

Not sure what a mapping table is?

[ronrsnfld]

I'm guessing your actual desired substitutions are not as simplistic as your example, which shows single character Upper Case => Upper & Lower case. How is the computer to "know" what you are substituting?

You'll need to refer to some kind of list or table or database.

[E_K_]

How would I map that in the data model? Split out into a distinct list, make the substitutions, and somehow dummy that back into the dataset? Trying to imagine how to do this

[ronrsnfld · Accepted solution]

One way:

• Create a two column replacement table
  • I named the columns "LookFor" and "Replace"

Then you can use code like below in your 'Main Query'

//create a Replacement List from the Replacement Table
#"Replacement List"=List.Zip({Replacements[LookFor],Replacements[Replace]}),

//Do the actual replacements using the TransformColumns method
    #"Replace Multiple" = Table.TransformColumns(#"Previous Step", 
    {"Column1", (s)=> 
        Text.Combine(
            List.ReplaceMatchingItems(
                List.Transform(Text.Split(s,","), Text.Trim),
                #"Replacement List"),
            ",")
    })
in
  #"Replace Multiple"

[CNENFRNL]

You can simplify it,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvRx1VFwdI3QUXBx8dRRcPUJ01EICArRUYhyBHKjojyVYnWAqkAKIMJQEZhqpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Old value" = _t]),
    Lookup = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcsxCsMwDIXhu2jOJQz2UMgQQinFwYOaCCMwMgi1lJ4+jjq+7+dtG4Q5wQShNaraoUxD0vMS0i74Nv46xngbGPlA490lzY8hCRtLDccHxbCSl2W9j7IokfnO4frmLoRq/KfshPLjFyqUcgI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [From = _t, To = _t]),

    Replaced =
        let
            lookup = Table.ToRows(Lookup)
        in
            Table.AddColumn(Source, "New", each Text.Combine(List.ReplaceMatchingItems(Text.Split([Old value], ","), lookup, each Text.Trim(_)), ", "))
in
    Replaced

[Vijay_A_Verma]

I want to see one actual cell value (if this is confidential, just change few alphabets) and the result against that actual cell value. 

[E_K_]

Oh I see! An M query that can replace these cell inputs with the key provided below,ideally in as few steps as possible. There are about 150 different values to replace

Cell 1 - ALE, AEX, DDI, ELV, PRT, ZAI, ZZI

Cell 2 -AEX, PRT, ZZI

Replace to Allegro, Aeronautix, Didactic, Ealing advantage,  Preet, Zone arti, Zanzibar

[Vijay_A_Verma]

Can you also post one example where brackets etc. are there i.e. other than alphabets?

[E_K_]

Edited initial question with an example

[Vijay_A_Verma]

@ronrsnfld 's solution is good one. You must go ahead with that.