ReplaceValue multiple values from a mapping table

[Rozan]

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.

[ThxAlot]

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

[JoeBarry]

Hi @Rozan 

You could probably be able to use fuzzy merge https://learn.microsoft.com/en-us/power-query/merge-queries-fuzzy-match and be able to use a mapping table too. 

Joe

If this post helps, then please Accept it as the solution