Your raw data has 50,000 transaction rows. You need total revenue by region, average order value by product, and customer counts by month — all in one clean summary table. Power Query Group By builds this summary in a few clicks. It collapses your detailed data into aggregated rows, applying SUM, COUNT, AVERAGE, MIN, MAX, or custom M expressions to each group. Unlike PivotTables, the result is a real table you can join, extend, and load anywhere.
This guide covers the UI dialog, Advanced mode for multiple aggregations, All Rows for custom M calculations, and six worked examples. These include regional revenue summaries, customer order frequency, multi-column grouping, conditional aggregation, and a weighted average pattern.
What Does Group By Do in Power Query?
Group By collapses a table so that rows sharing the same group value become one summary row. For each group, you specify one or more aggregations. Each becomes a separate output column. The output table has one row per unique group combination.
Group By is not a PivotTable replacement. PivotTables are interactive and visual. Group By produces a flat table you can load, merge, or use as a lookup source. Specifically, Group By is the right tool when you need an aggregated table as an input to further transformations, not just a visual summary.
| Aggregation | What it computes | M function |
|---|---|---|
| Sum | Total of all values in the group | List.Sum |
| Average | Mean of all values in the group | List.Average |
| Min | Lowest value in the group | List.Min |
| Max | Highest value in the group | List.Max |
| Count Rows | Number of rows in the group | Table.RowCount |
| Count Distinct Rows | Unique row count in the group | Table.Distinct, Table.RowCount |
| All Rows | Nested table of all rows (for custom M) | Table reference |
How to Use Group By in Power Query
Examples 1–4: Group By in Practice
The simplest case is collapsing a transaction table to total revenue per region. Group By on Region with a Sum aggregation on Amount produces a clean two-column output — one row per region.
Advanced mode lets you add several aggregations at once. A single Group By step can show Total Revenue, Order Count, and Average Order Value per region. The result is a four-column table: Region plus the three metrics.
Grouping by two columns gives one row per unique combination of both. Grouping by Region and Product gives one row per Region-Product pair. This is more granular than Region alone but more compact than the original detail rows.
Count Rows counts all rows in each group, including duplicate customer IDs. To count distinct customers — unique CustomerIDs only — use the All Rows aggregation type and apply a custom M expression that deduplicates the nested table before counting.
Examples 5–6: Conditional Aggregation and Weighted Average
Power Query’s Group By dialog does not have a native SUMIF equivalent. However, the All Rows aggregation combined with a Table.SelectRows filter achieves conditional aggregation inside the grouped sub-table. This produces a SUMIF-style result: sum of Amount where Status = "Completed", grouped by Region.
A simple average of price per region treats a £1 order the same as a £100,000 order. A weighted average uses quantity or revenue as the weight. In Power Query, this requires summing (Price * Quantity) and dividing by total Quantity for each group — both calculated inside the All Rows aggregation.
Common Issues and How to Fix Them
Group By shows fewer rows than expected
The likely cause is two values that look identical but differ in spacing, capitalisation, or hidden characters. Apply Trim, Clean, and Text.Lower to the grouping column(s) before applying Group By. After normalising, previously distinct values collapse into the correct group.
Group By lost the original row detail
Group By is a destructive aggregation — the grouped output only contains the aggregated values, not the original rows. To keep both detail and summary, create two queries from the same source. Then merge the grouped query back to add group-level metrics as extra columns on each row.
Frequently Asked Questions
-
How does Group By work in Power Query?+Group By collapses a table to one row per unique combination of the grouping columns. For each group, you specify one or more aggregations — Sum, Average, Count, Min, Max, or a custom M expression. The result has one column per aggregation. Use Basic mode for a single aggregation and Advanced mode for multiple aggregations or multiple grouping columns. The M function is Table.Group(table, {"GroupColumn"}, {"NewColumn", each List.Sum([ValueColumn]), type number}).
-
How do I count distinct values within each group?+Use the All Rows aggregation type and apply a custom M expression. In the M code: Table.Group(Source, {"Region"}, {"UniqueCustomers", each Table.RowCount(Table.Distinct(_, {"CustomerID"})), type number}). The underscore represents the nested sub-table for each group. Table.Distinct removes duplicate CustomerID rows within that sub-table, and Table.RowCount counts the remaining unique rows.
-
How do I do a SUMIF equivalent in Power Query Group By?+Use All Rows aggregation with a Table.SelectRows filter inside the M expression: each List.Sum(Table.SelectRows(_, each [Status] = "Completed")[Amount]). This sums the Amount column only for rows where Status equals "Completed" within each group. It is equivalent to SUMIF but applies to every group simultaneously in one step.
-
Can I group by multiple columns?+Yes. In Advanced mode, click "+ Add Grouping" to add more group-by columns. Each additional column further subdivides the groups. For example, grouping by Region and Product produces one row per Region-Product combination. In M code, pass a list of column names as the second argument: Table.Group(Source, {"Region", "Product"}, {aggregations}).