Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 56 ยท Power Query

Group By and Aggregations in Power Query

Summarize tables with Group By in Power Query: sums and counts, the All Rows option, index and rank columns and running totals, with pandas stand-ins.

Sometimes you want a table with one row per customer, per month or per product instead of one row per order. Group By collapses rows into groups and calculates something for each, such as a sum, a count or the latest date. It is the Power Query version of a SQL GROUP BY or a pivot-table summary.

A word of caution before you start. In a Power BI report you normally let visuals and DAX measures do the summarizing (see DAX Basics), because they respond to slicers. Group By in Power Query produces a fixed table at refresh time. Use it when you need a pre-aggregated table (for performance or because the grain is part of the design), or when you need per-group logic such as "first order date per customer". For the editor itself, see The Power Query Editor.

Group By in one picture

Pick keys One or more columns define the groups.
Pick aggregations Sum, count, average, min, max.
One row per group The rest of the detail is gone.
1 / 2

Basic Group By

Choose Transform > Group By (or Home > Group By). In the dialog, pick the column(s) to group by, name the new column, pick an operation (Sum, Average, Median, Min, Max, Count Rows, Count Distinct Rows, or All Rows) and, for most, the column to summarize. The Advanced option adds more group columns and more aggregations.

M
#"Grouped Rows" = Table.Group(
    Source, {"Region"},
    {
        {"Total Revenue", each List.Sum([Revenue]), type nullable number},
        {"Orders", each Table.RowCount(_), Int64.Type},
        {"Last Order", each List.Max([OrderDate]), type nullable date}
    }
)

Inside each aggregation, _ is the sub-table for the current group, and [Revenue] is that table's column as a list. That is why the aggregators are list functions (List.Sum, List.Max) and the row count is a table function.

Two things to remember: groups are case-sensitive (clean the keys first, see Cleaning Data: Types, Nulls, Trim and Duplicates), and nulls in the summarized column are ignored by Sum, Average and Max, while Count Rows counts them.

The All Rows option

All Rows does not calculate a number. It keeps each group's rows as a nested table in a new column. That sounds odd, but it is the key to per-group logic, because you can then add a custom column that works on each nested table, and expand it again.

A classic use is "keep only the latest order per customer":

M
Grouped = Table.Group(Source, {"CustomerID"}, {{"Rows", each _, type table}}),
WithLatest = Table.AddColumn(
    Grouped, "Latest",
    each Table.Max([Rows], "OrderDate")
),
Expanded = Table.ExpandRecordColumn(WithLatest, "Latest", {"OrderDate", "Amount"})

Table.Max returns the record (row) with the largest OrderDate. You then expand its fields. The same approach gives the first order, the top three orders per group (Table.MaxN), or a count of rows that meet a condition.

Index columns

Add Column > Index Column numbers the rows. You can start from 0, from 1 or from a custom value with any increment.

M
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type)

An index gives you a stable row key, lets you restore an order after sorting, and supports tricks such as comparing a row with the previous one (merge the table with itself on Index and Index - 1). Add the index after sorting if you want the numbering to follow that sort.

Rank within groups

Power Query has a Rank Column command in recent versions (Add Column > Statistics > Rank), but a flexible and widely used approach combines Group By with All Rows, a sort, and an index:

M
Grouped = Table.Group(Source, {"Region"}, {{"Rows", each _, type table}}),
Ranked = Table.AddColumn(
    Grouped, "Ranked",
    each Table.AddIndexColumn(Table.Sort([Rows], {{"Revenue", Order.Descending}}), "Rank", 1, 1)
),
Result = Table.ExpandTableColumn(
    Table.SelectColumns(Ranked, {"Ranked"}), "Ranked", {"Product", "Revenue", "Rank"}
)

Here each region's rows are sorted by revenue and numbered from 1. Ties get consecutive numbers with this method, so decide how to handle them if ties matter. Rankings that must react to slicers belong in DAX instead; that is covered in Ranking, Top N and Percent of Total.

Running totals in M

A running total in Power Query is possible but heavier than in DAX, because M has no window functions. A common pattern uses an index and List.Sum over a slice of the list:

M
Sorted = Table.Sort(Source, {{"OrderDate", Order.Ascending}}),
Indexed = Table.AddIndexColumn(Sorted, "Index", 0, 1, Int64.Type),
Running = Table.AddColumn(
    Indexed, "Running Total",
    each List.Sum(List.FirstN(Indexed[Revenue], [Index] + 1)),
    type number
)

For each row it sums the first Index + 1 revenues. That works, but it rescans the list for every row, which is slow on large tables (the cost grows with the square of the row count). For big data, compute running totals in a DAX measure or in the database. Knowing the M version is still useful for small, one-off staging tables.

Use Power Query to aggregate when it reduces the data you load (daily totals instead of millions of transactions). Use DAX when users need to slice the result.

Group By in pandas

The pandas version uses groupby with named aggregations. This example reproduces the grouped table, and the next one shows the "latest row per group" and rank patterns.

Python groupby with several aggregations

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The sum of the group totals equals the overall total, a good sanity check after any Group By.

All Rows, rank, index and running total

Python Latest per group, rank and running total

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The last running total, 610, equals the sum of all revenue. As with the other lessons, the M code is what Power Query generates; pandas is a stand-in. Notice that pandas has a built-in cumsum, a convenience M lacks, which is why the M pattern above looks so manual.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does the All Rows operation in Group By produce?

    All Rows keeps each group's detail as a nested table, which you can process per group (for example take the latest row) and then expand.
  2. 2. Why might you prefer a DAX measure over Group By for totals in a report?

    A Group By table is computed at refresh. A measure is evaluated at query time in the current filter context, so it reacts to user selections.
  3. 3. In an aggregation inside Table.Group, what does the underscore _ refer to?

    Each aggregation function receives the group's rows as a table, referred to by _. Columns of that table are lists, such as [Revenue].
  4. 4. What is a downside of the List.FirstN running total pattern in M?

    Each row sums a growing slice of the list, so the work grows roughly with the square of the row count. For large data use DAX or the database.

Summary

  • Group By collapses rows into one row per group with sums, counts, averages, min, max or All Rows.
  • Inside an aggregation, _ is the group's table and [Column] is that column as a list.
  • All Rows plus a custom column enables per-group logic such as latest row or top N per group.
  • Index columns number rows; rank and running totals are possible in M but heavy, so DAX is usually better.
  • Aggregate in Power Query to reduce loaded data; use measures when users need to slice.

Next, learn the language underneath all of these steps in M Language Basics.