Grouping and Aggregation

Group By is Power Query's way of summarising rows — collapsing many rows into fewer rows by aggregating values. It is the equivalent of a SQL GROUP BY or an Excel PivotTable, but it runs before data ever reaches your model. Knowing when to group inside Power Query versus letting DAX handle aggregation later is one of the most important judgement calls in building efficient Power BI reports.

What Group By Does

Group By takes a table with many rows and produces a smaller table where each row represents a unique combination of the grouping columns, with one or more aggregated values computed from all the rows that shared that combination.

The Group By Dialog

Group By is accessed from the Transform tab → Group By. The dialog has two modes — Basic (one group column, one aggregation) and Advanced (multiple group columns and multiple aggregated output columns). You will almost always use Advanced.

All Available Aggregation Functions

Multiple Aggregations in One Step

One of the biggest advantages of the Advanced mode is computing several aggregations simultaneously in a single Group By step. This is more efficient than chaining multiple separate steps and produces a clean summary table with all the metrics you need in one pass.

// Group By: Region — compute 4 aggregations at once
// Transform tab → Group By → Advanced

Group by columns:
  Region

Aggregated output columns:
  Name             Operation          Column
  ─────────────────────────────────────────────────
  Total Revenue  → Sum             → Revenue
  Order Count    → Count Rows      → (none needed)
  Avg Order Val  → Average         → Revenue
  Max Order      → Max             → Revenue

M code generated:
= Table.Group(
    #"Filtered Rows",
    {"Region"},
    {
        {"Total Revenue", each List.Sum([Revenue]),    type number},
        {"Order Count",   each Table.RowCount(_),      Int64.Type},
        {"Avg Order Val", each List.Average([Revenue]),type number},
        {"Max Order",     each List.Max([Revenue]),    type number}
    }
)

When to Group in Power Query vs DAX

Both Power Query and DAX can aggregate data — but they do it at different points in the pipeline and for different reasons. Choosing the wrong tool leads to either wasted model memory or inflexible reports that cannot respond to slicer selections.

Practical Pattern — Building a Customer Summary Table

A common pattern is to use Group By to create a customer-level summary table alongside the detail Orders table. The summary table gives you pre-computed customer metrics — lifetime value, first purchase date, order count — that are fast to display in cards and tables without complex DAX.

Pattern: Create CustomerSummary as a reference query
─────────────────────────────────────────────────────

Step 1 — Right-click the Orders query → Reference
         Rename the new query: CustomerSummary

Step 2 — Transform tab → Group By → Advanced
         Group by: CustomerName

         Aggregated columns:
           Lifetime Value  = Sum        of Revenue
           Order Count     = Count Rows
           First Order     = Min        of OrderDate
           Last Order      = Max        of OrderDate
           Avg Order Value = Average    of Revenue

Step 3 — Click OK
         CustomerSummary now has one row per customer
         with all five metrics pre-computed

Step 4 — Close & Apply
         Both Orders (detail) and CustomerSummary load
         into the model

Step 5 — In Model View
         Connect CustomerSummary.CustomerName (1)
         to Orders.CustomerName (*) — one-to-many
         relationship

Result in reports:
  Cards showing Lifetime Value respond to customer
  selection even though values are pre-computed —
  because the relationship propagates the filter

Counting vs Counting Distinct

The difference between Count Rows and Count Distinct Rows is one of the most common sources of incorrect metrics in Power BI reports. Understanding which one you need before you build prevents a whole category of wrong-number errors.

Practice

Lesson Quiz

Next up — Lesson 21 covers Splitting and Merging Columns — every technique for separating values crammed into one column and combining values spread across multiple columns, including delimiter splits, character-count splits, and the Column from Examples shortcut.