Power Query dynamic grouping by combining lists

Question

I want to create dynamic Group By in Power Query with function Table.Group

Table.Group(table as table, key as any, aggregatedColumns as list, optional groupKind as nullable number, optional comparer as nullable function) as table

#"Grouped Rows" = Table.Group(Source, {"name"}, 
                        {{"col1", each List.Max([col1]), type text},  //I used here max for simplification (otherwise text concat)
                         {"col2", each List.Sum([col1]), type number}}),

However list aggregatedColumns I need to generate from Source table. I can get 3 lists:

ColumnNames = Table.ColumnNames(Source),
ColumnTypes = Table.Schema(Source)[TypeName],
ColumnFx = List.Max (type of function may differ based on column data type)

To create aggregatedColumns list I would need to generate list like

aggCols = {ColumnNames{0}, ColumnFx{0}, ColumnTypes{0}} (I know it is nonsense in this format)

In another language I would use loop for it but with M it seems to me not possible.

sample input:

name    col1    col2
n1    name11    1
n1    name12    2
n1    name13    3
n2    name21    4
n2    name22    5
n3    name32    6

sample output:

name    col1    col2
n1    name13    6
n2    name22    9
n3    name32    6

Any ideas?

A simple example of Source and final result would be helpful to understand what you're asking. — Alexis Olson
I am not sure if it would help as it should be really dynamic and autonomous regardless table content. Column name, data type and even function (sum for number, concat for text) may vary - only first column (name) is fixed. — Michal Palko
I think a representative example would help. It's hard to write a general solution without a specific one first. Just specify which parts of the example are not fixed. — Alexis Olson
Your specifications are still unclear. Will you have multiple columns of the same type or do you just need to make the aggregation dynamically dependent on the type? Do you have multiple numerical types (e.g. percentage, integer, fixed decimal)? If so, should they be treated the same or differently? — Alexis Olson

horseyride horseyride · Accepted Answer · 2020-11-18T21:36:05

Are you trying to get the maximum of each column, grouped by "name", regardless of number of columns?

If so,

Right click name column and unpivot other columns

Click select name and Attribute columns, group, and for operation use max of the Value column, calling the new column "Max"

Click select Attribute column, transform ... pivot column ... and choose the Max column sum

sample code

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"name"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"name", "Attribute"}, {{"Max", each List.Max([Value]), type number}}),
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Attribute]), "Attribute", "Max", List.Sum)
in #"Pivoted Column"

Power Query dynamic grouping by combining lists

1 Answers