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
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]
Here, we will use a combination of List.Distinct and Text.Combine to produce a distinct list item:
each Text.Combine(List.Distinct([EventType]), ",")
each List.NonNullCount([EventType])
each Table.Max(_, each [StartTime])
each Table.Sort(_, "DamageProperty"){Number.RoundDown(Table.RowCount(_) * .75)}
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.