cancel
Showing results for 
Search instead for 
Did you mean: 

Custom Aggregations in ‘Group By’

Grouping Basics

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

image.pngGroup by in Home menu

image.pngGroup 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:

image.pngGroup by dialog box

From here, you will get the grouped result:

image.pngResult 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)}