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

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