Forum Discussion

E_K_'s avatar
E_K_
Helper III
4 years ago
Solved

Power Query - Replace multiple substrings in one list column

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 valueNew value
ALE, AEX, DDI, ELV, PRT, ZAI, ZZIAllegro, Aeronautix, Didatic, EalingAdvantage, Preet, Zone arti, Zanzibar
AEX, PRT, ZZIAeronautix, Preet Zanzibar
DDI, ELVDidactic, 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/589062

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

 

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    4 years ago

    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"

     

     

12 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    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_'s avatar
      E_K_
      Helper III
      4 years ago

      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/589062

       

      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?

      • Vijay_A_Verma's avatar
        Vijay_A_Verma
        Most Valuable Professional
        4 years ago

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