Forum Discussion

squiddly's avatar
squiddly
Advocate I
8 years ago
Solved

Replacing multiple values in multiple columns

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 Help
  • RobertSlattery's avatar
    RobertSlattery
    8 years ago

    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
RobertSlattery's avatar
RobertSlattery
Responsive Resident
8 years ago

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's avatar
squiddly
Advocate I
8 years ago

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