none
Transform a set of columns RRS feed

  • Question

  • Hi
    What is you best suggestion to transform a large number of columns using another column:

    Source:

    ref  | pct   | col1 | col2 | ... | colN
    AA      14%    20     30            60
    AB      29%    35     55            28
    ...

    Result:

    ref  | pct    | col1          | col2         |        ... | colN
    AA     14%    20*14%     30*14%            60*14%
    AB     29%    35*29%     55*29%            28*29%
    ...



    • Edited by anthony34 Wednesday, May 22, 2019 1:28 PM
    Wednesday, May 22, 2019 1:25 PM

Answers

  • Hi Anthony,

    You may use standard unpivot-->transform-->pivot technique or following one:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
        replace = Table.ReplaceValue(Source,null,each [pct],(a,b,c) => a*c,List.Skip(Table.ColumnNames(Source),2))
    in
        replace


    Wednesday, May 22, 2019 1:47 PM