I have a table ("Index") which I want to transform by dividing all columns except "Date" by columnspecific factors. The factors are stored in a table named "normfactor" which has the same columnheaders as "Index" and only one row.

Table "Index":

Table "normfactor":

I found a function I could use to divide the columns of "Index" by a fixed factor (here the normfactor of the first column):

let
        Source = Excel.CurrentWorkbook(){[Name="Index"]}[Content],
        ZoneColumnNames = List.RemoveMatchingItems(Table.ColumnNames(Quelle), {"Date"}),
        fn_devide_column_by_factor = (fnRec as record) as list => List.Transform(ZoneColumnNames, each {_, each _ / Table.Column(normfactor, ZoneColumnNames{0}){0}}),
        #"transform" = Table.FromRecords(Table.TransformRows(Source, (Rec) => Record.TransformFields(Rec, fn_devide_column_by_factor(Rec))))
    in
        #"transform"

How can I write this variable, so it doesn't always divide by the normfactor of the first column, but by the normfactor in the column with the same columnname?

CodePudding user response：

One way

// code for index table using normfactor table
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Columns = List.RemoveItems(Table.ColumnNames(Source),{"Date"}),
Transforms = List.Transform(Columns,(x)=>{x, each _ /  Table.Column(normfactor,x){0}  , type number}),
Transformit = Table.TransformColumns(Source, Transforms)
in Transformit

another way

// code for index table using normfactor table
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index", "Date"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "NewValue", each [Value] / Table.Column(normfactor,[Attribute]){0}),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "NewValue", List.Sum),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in  #"Removed Columns1"