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 ...
Need Help
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