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:

Group by in Home menuGroup by in Home menu

Group by in the Transform menuGroup 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 boxGroup by dialog box

From here, you will get the grouped result:

Result of group by countResult 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:


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:








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)}