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 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!

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

3 Replies

  • 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 :)