
π This article includes 2 downloadable practice files β
In this article
Sometimes you don’t need every order, just totals per region or per customer: for a smaller file, a feed into another report, or a summary table to merge. Group By does that inside the query.
Basic
Home βΊ Group By βΊ Basic βΊ Group by: Region βΊ New column name: Amount βΊ Operation: Sum βΊ Column: Amount.
Advanced: several results
Choose Advanced and add aggregations:
| New column | Operation | Column |
|---|---|---|
| Orders | Count Rows | |
| Qty | Sum | Qty |
| Amount | Sum | Amount |
| Customers | Count Distinct Rows | (on a table with only Customer selected) or use Count Distinct in M |
Add a second grouping column (Region, then Customer) for a two-level summary.
Group here or in a pivot?
| Group in Power Query when | Use a pivot table when |
|---|---|
| You need a fixed summary table to merge or export | People want to slice and drill interactively |
| Raw data is too big to load | Several views of the same data |
| Feeding another query | Ad-hoc questions |
Where beginners go wrong
| Problem | Cause |
|---|---|
| Sum gives errors | Column type is text; set it to a number first |
| Too many groups | Grouping by a column with spaces/case differences; clean first |
| Detail lost that you later need | Keep the detailed query too; group in a referenced copy (right-click βΊ Reference) |
Practice
Download the source file(s) and the expected-results workbook below. Build the query in Excel (Data βΊ Get Data), load it to a sheet, and compare your row count and totals with the Checks sheet.
π Practice files for this article
β¬ Download all 2 files (ZIP Β· 8 KB)
- π§Ύpq-07-sales.csv180 orders.β¬ CSV Β· 10 KB
- πExpected resultsWhat your query should produce, with check totals.β¬ XLSX Β· 6 KB
Free to use for learning. Files with macros (.bas) are plain text β import them with Alt+F11 β File β Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.