📎 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
- 🧾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.