Power Query: Table.Group with a dynamic list of aggregated columns

Question

Been struggling for a few hours to reach my goal.

The objective is to use a list with the column names to aggregate when using Table. Group instead of having a fix list of columns.

I've defined already GroupColumns as a list for the "key" and it works well.

Example:

= 
Table.Group(
   Source,
   GroupColumns,
   {{
    "Sales (M€)", each List.Sum([#"Sales (M€)"]), type number}, 
    {"Sales (Qty MU)", each List.Sum([#"Sales (Qty MU)"]), type number}
   })

I have defined a list named SumColumns:

SumColumns = {"Sales (M€)","Sales (Qty MU)"}

Now I can't find the syntax to create a list that would dynamically create all the columns I want based on the SumColumns list.

I assumed I would get the answer here but I don't understand the "LocalContext" part: How do I properly use table.group in a PowerQuery query to dynamically summarize different rows and columns?

I tried this:

= Table.Group(
    Source,
    {GroupColumns},
    {{
        SumColumns,
        (LocalContext) => List.Sum(Table.Column(LocalContext, SumColumns)),
        type number
    }}
)

But I get the error "cannot convert a value of type List to type Text"

I tried going through "unpivot" to do the trick, but I get the same issue when I want to pivot back the attribute to columns, and it would be much slower anyway.

Any ideas?

Thanks

Aurelien

Alexis Olson Alexis Olson · Accepted Answer · 2020-12-18T01:02:42

It does get a bit confusing trying to keep track of the environment context.

This question is one step beyond my answer in the post you linked since we have multiple columns instead of just the single one. Therefore we need to generate the list

{
    {"Sales (M€)", each List.Sum([#"Sales (M€)"]), type number}, 
    {"Sales (Qty MU)", each List.Sum([#"Sales (Qty MU)"]), type number}
}

From just the list

SumColumns = {"Sales (M€)", "Sales (Qty MU)"}

The rule is that we need to replace a column C with a list that contains C, a function, and a type. I.e.,

C --> {C, function, type}

To do this, I'll use a List.Transform on SumColumn where the second argument is a function that defines the transformation that is needed.

List.Transform(SumColumns, (C) => {C, each List.Sum(Table.Column(_, C)), type number})

In this expression, C represents the column name and _ takes the place of the subtable we're picking the column C from in order to sum over.

Putting this into your query looks like this:

Table.Group(
    Source, 
    GroupColumns,
    List.Transform(SumColumns, (C) => {C, each List.Sum(Table.Column(_, C)), type number})
)

This assumes GroupColumns is a list of columns.

Note that this time I've chosen to use the each _ (which is equivalent to (_) => _) construction but I could have written more similarly to how I did previously to emphasize the capturing of the local context.

Table.Group(
    Source, 
    GroupColumns,
    List.Transform(
        SumColumns,
        (C) => {C, (LocalContext) => List.Sum(Table.Column(LocalContext, C)), type number}
    )
)

Note: In both of these anonymous/lambda function definitions, C and LocalContext are essentially variable names that I happen to have chosen. They aren't any kind of keywords or built-in functions.

Power Query: Table.Group with a dynamic list of aggregated columns

1 Answers