Forum Discussion

Rozan's avatar
Rozan
New Member
2 years ago

ReplaceValue multiple values from a mapping table

Hello everyone, Would appreciate some hints on how to replace multiple values (ideally from another mapping table).

 

The challenge I'm facing is: to replace several values in two columns ("Column 1","Column 2"), I used a stupid way to use several ReplaceValue lines and pls find a sample as below:

 

#"Replaced Value1" = Table.ReplaceValue(#"Added Custom1","Ltd.","",Replacer.ReplaceText,{"Column 1","Column 2"}),

#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Co.","",Replacer.ReplaceText,{"Column 1","Column 2"}),

#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","China","CN",Replacer.ReplaceText,{"Column 1","Column 2"}), #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","CHN","CN",Replacer.ReplaceText,{"Column 1","Column 2"}), #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","(","",Replacer.ReplaceText,{"Column 1","Column 2"}),

#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5",")","",Replacer.ReplaceText,{"Column 1","Column 2"}),

...

 

Is there a simple way that I can put all replacement values to a mapping table (from "Ltd." to "", from "Co." to "", etc), and in the future, I can easily add more mapping values? thanks a lot.

Need Help

2 Replies

  • ThxAlot's avatar
    ThxAlot
    Super User
    2 years ago

    Simple enough, use embedded Table.ReplaceValue().

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ilJ0VPSUXLO11OK1YFwFUAcoJCHH1jIOSMzLxHI19RUcIEIACV0lDRASjXBAo5OzgoaQE2aQOH8ikKwWEREBMgMsN7YWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    Replacement = {
        {{"Ltd.","Co.","(",")"}, ""}
        , {{"China","CHN"}, "CN"}
    },
    Replaced = List.Accumulate(Replacement, Source, (s,c) => Table.ReplaceValue(s, c{0}, c{1}, (x,y,z) => List.Accumulate(y, x, (s,c) => Text.Replace(s, c, z)), {"Column1","Column2"}))
in
    Replaced