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 value New value ALE, AEX, DDI, ELV, PRT, ZAI, ZZI Allegro, Aero...
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"

     

     

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?

ronrsnfld's avatar
ronrsnfld
Super User
4 years ago

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

    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's avatar
      ronrsnfld
      Super User
      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"

       

       

      • CNENFRNL's avatar
        CNENFRNL
        Community Champion
        4 years ago

        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