Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Aggregation - "Group by" with dynamic column parameters

Hi!
I have Table A, B and C. Table A is table with source data. I would like to obtain a table where I see only columns that are written in Table B and to aggregate data from columns that are written in Table C.

I can't create in any way function List.Sum() with dynamic parameter -> so that when there are 2 names of columns in Table C, then it will aggregate both of them (when 3, 3 of them etc.) -> it results in error.
The case is simplified here, but I need it dynamic as there might be e.g. 20 columns with data and I would like to select which one of them should be used during THIS aggregation.

 

 

 

let
    Source = Excel.CurrentWorkbook(){[Name="TableA"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Heading A", type text}, {"Heading B", Int64.Type}, {"Heading C", type text}, {"Heading D", type text}, {"Heading E", Int64.Type}, {"Heading F", type text}}),
    Custom1 = List.Combine ( {ColToBePresented, ColToBeAggregated}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",Custom1),
    Function1 = (x) => each List.Sum([x]),
    #"Converted to Table" = Table.FromList(ColToBeAggregated, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Invoked Custom Function" = Table.AddColumn(#"Converted to Table", "Query2", each Function1([Column1])),
    #"Added Custom" = Table.AddColumn(#"Invoked Custom Function", "Custom", each type number),
    Custom2 = Table.ToRows(#"Added Custom"),
    Custom3 = Table.Group(#"Changed Type", ColToBePresented, Custom2)
in
    Custom3

 

 

This is what I would like to achieve (done with pivotTable, which is not an option in my case):

 

Can You Help me? Is it possible to do it?