Custom Aggregations in ‘Group By’

[artemus]

Grouping Basics

You can find the Group By button on the Power BI Query Editor window in 2 places:

Group by in Home menu

Group by in the Transform menu

In the result dialog box, you can choose your key columns and aggregates. The key columns will default to what columns you currently have selected:

Group by dialog box

From here, you will get the grouped result:

Result of group by count

Customize your Group Aggregates

Count, Max, Average, etc.... are all good options, but what if we want something else? All we need to do is edit the formula generated when we do a group:

From here we can edit the "each" parameter.

With the default variable "_", we can use any table, row, or column function for summary (or a combination of multiple).

The list of functions you can use are here:

https://docs.microsoft.com/en-us/powerquery-m/power-query-m-function-reference

Column aggregations are the most common kind of aggregation. They use a single column and generate a single value from that. In Power Query, a column is represented as a list, and you can refer to a column as:

table[ColumnName]

In the case of groups, the default variable "_" refers to a table, and when using the default variable, the table part of the syntax is optinoal. Hence: _[ColumnName] = [ColumnName]

Sample Aggregations

Concatenated List of Values

Here, we will use a combination of List.Distinct and Text.Combine to produce a distinct list item:

each Text.Combine(List.Distinct([EventType]), ",")

NonNull count

each List.NonNullCount([EventType])

 Row with latset time stamp

each Table.Max(_, each [StartTime])

75th percentile

each Table.Sort(_, "DamageProperty"){Number.RoundDown(Table.RowCount(_) * .75)}