Replacing multiple values in multiple columns

[dpbi]

Hi

I need to replace multiple values in multiple columns based on a couple of translation tables.

The following solution provied values replacement on the entire table based on one translation table.

My question: is it possible, (based on the following solution or another) to control which column will be update i.e. - 

Replace: Table [ Column1 ] values with 'Translation table1' values,

              Table [ Column2 ] values with 'Ttranslation table2'....etc.

Here is @MarcelBeug great solution for the entire table (without controling which column will be updated)

List.Accumulate(Table.ToRows(Translations),Table1,(t,r) => Table.ReplaceValue(t,r{0},r{1},Replacer.ReplaceValue,Table.ColumnNames(t)))

http://community.powerbi.com/t5/Desktop/Power-Query-Batch-Replace-String/td-p/164402

Explanation:

• Translations is a table with translations (foreign - English), which is converted to a list of lists with replacements using Table.ToRows
• Table1 is your table. For List.Accumulate this table is the start value.
• The third argument for List.Accumulate is a function using a "state" and "current" parameter (i.c. t and r)
  t is the table before each iteration and r is the foreign - English pair of the current iteration.
• List.Accumulate will loop over the translation list and for each entry (or iteration), it will update your table (t) by replacing the old value (r{0}) ny the new value (r{1}), using function Replacer.ReplaceValue, and for all table columns (Table.ColumnNames(t)).

[MarcelBeug · Accepted solution]

If each translation table is for 1 column, then I would suggest to use Table.TransformColumns instead of Table.ReplaceValues.

The query below has a double loop:

1. Outer loop over the columns in which values must be replaced (in the example: Name and Surname)

    with the translation tables to use (in the example TranslationTable1 and -2),

2. Inner loop over each entry in the translation list.

r1 = Transformspecs, r1{0} = Column name to transform, r1{1} = translation list

t1/t2 = the table that is being transformed

r2 = translation list: r2{0} = old value, r2{1} = new value.

let
    ColumnsToTransform = {"Name", "Surname"},
    TranslationLists = List.Buffer({Table.ToRows(TranslationTable1),Table.ToRows(TranslationTable2)}),
    TransformSpecs = List.Buffer(List.Zip({ColumnsToTransform,TranslationLists})),
    Source = Table1,
    Replaced = 
        List.Accumulate(
            TransformSpecs,
            Source,
            (t1,r1) => 
                List.Accumulate(
                    r1{1},
                    t1,
                    (t2,r2) =>
                        Table.TransformColumns(
                            t2,
                            {r1{0}, 
                             each Replacer.ReplaceValue(_,r2{0},r2{1})}
                        )
                )
        ),
    Result = Value.ReplaceType(Replaced,Value.Type(Source))
in
    Result

[dpbi]

@MarcelBeugThanks a lot for your fast reply and brilliant solution.

Well appriciated.