Replacing multiple values in multiple columns

[squiddly]

https://community.powerbi.com/t5/Desktop/Replacing-multiple-values-in-multiple-columns/td-p/313013

Almost exactly like the above link, except I'm trying to only use 1 replacement table.  I know I need to remove a loop here somewhere, but not sure where!

#"CPE-Char-Subs-tbl-rev" is my translation table.

#"CPE-22-parsed" is the table that I need to transform.  Only need to transform the columns in the variable ColumnsToTransform.

let
    ColumnsToTransform = {"Vendor", "Product", "Version", "Update", "Edition"},
    TranslationLists = List.Buffer(Table.ToRows(#"CPE-Char-Subs-tbl-rev")),
    TransformSpecs = List.Buffer(List.Zip({ColumnsToTransform,TranslationLists})),
    Source = #"CPE-22-parsed",
    Replaced = 
        List.Accumulate(
            TransformSpecs,
            Source,
            (t1,r1) => 
                List.Accumulate(
                    r1{1},
                    t1,
                    (t2,r2) =>
                        Table.TransformColumns(
                            t2,
                            {r1{0}, 
                             each Replacer.ReplaceText(_,r2{0},r2{1})}
                        )
                )
        ),
    Result = Value.ReplaceType(Replaced,Value.Type(Source))
in
    Result

Thank you in advance for any pointers!

[RobertSlattery · Accepted solution]

Untested but, maybe in the right direction...

let
    ColumnsToTransform = {"Vendor", "Product", "Version", "Update", "Edition"},
    TranslationList = List.Buffer(Table.ToRows(#"CPE-Char-Subs-tbl-rev")),
    Source = #"CPE-22-parsed",
    Replaced = 
        List.Accumulate(
            ColumnsToTransform,
            Source,
            (sourceOuter,col) => 
                List.Accumulate(
                    TranslationList,
                    sourceOuter,
                    (sourceInner,translationCouple) =>
                        Table.TransformColumns(
                            sourceInner,
                            {col, 
                             each Replacer.ReplaceText(_,translationCouple{0},translationCouple{1})}
                        )
                )
        ),
    Result = Value.ReplaceType(Replaced,Value.Type(Source))
in
    Result

[squiddly]

That was it exactly Robert.  Thank you!  I only copied and pasted your code, will dive into it in a few minutes to understand 🙂

[Greg_Deckler]

@ImkeFor @MarcelBeug are probably 2 of the best people to help.