In Power Query, Group By collapses many transaction rows into one row per month and category. Add a Month column, choose Transform, Group By, switch to Advanced, group by Month and Category, then add Sum of Amount and Count Rows. Load the result as a table, and your workbook holds a few dozen summary rows instead of thousands.
What do I need before grouping?
A clean table with a date, a category and a numeric amount. Check three things in the Power Query Editor first:
- The Date column has type Date, not text.
- The Amount column has type Decimal Number, with no errors in it.
- Category has no null values. Use Transform, Replace Values to change null to Uncategorised, because otherwise they form a separate empty group.
Then add the month: select Date, go to Add Column, Date, Month, Start of Month. This gives the first day of each month, such as 01-04-2025, which sorts correctly and is easier to use than a month name.
How do I set up the Group By?
- Select the Month and Category columns, then choose Transform, Group By.
- Switch the dialog to Advanced. Month and Category should already be listed as grouping columns.
- First aggregation: new column name Total, operation Sum, column Amount.
- Click Add aggregation. Name it Transactions, operation Count Rows.
- Click OK, then sort Month ascending and Category as you like.
| Month | Category | Total | Transactions |
|---|---|---|---|
| 01-04-2025 | Groceries | 9200 | 14 |
| 01-04-2025 | Fuel | 3400 | 6 |
| 01-05-2025 | Groceries | 10100 | 16 |
The figures are made up, but the shape is the point: one row for each month and category pair that exists in your data. A pair with no spending simply does not appear.
What does the M code look like?
The editor writes it for you. It looks like Table.Group(Source, {"Month", "Category"}, {{"Total", each List.Sum([Amount]), type nullable number}, {"Transactions", each Table.RowCount(_), Int64.Type}}). Read it as: group Source by those two columns, then compute a sum and a row count inside each group. You can add a third aggregation by adding another pair inside the braces, such as the largest single amount with List.Max.
Why load the summary and not the detail?
- Speed: a workbook with a few hundred summary rows opens, refreshes and filters fast.
- Size: statements with years of rows make files heavy and slow to email.
- Clarity: a pivot table or chart built on a summary table has nothing to scroll through.
Use Home, Close and Load To, choose Table or Only Create Connection, and keep the detailed query as a connection if you want a second route to the raw rows later.
What goes wrong with grouping?
- Two groups that look the same: "Groceries" and "Groceries " (with a trailing space) are separate groups. Trim and clean the text first.
- Capital letters: grouping is case-sensitive, so groceries and Groceries are separate.
- Credits and debits: if refunds are positive and spends are negative, the sum nets them. Decide what you want before grouping, and perhaps split into two columns.
- Dates in a different region format load as text. Set the column type with a locale if needed.
What next?
Build a pivot table over the summary, with Category down and Month across, and a chart beside it. For a one-off view of spending by category and month, SpendixAI's reports already group statement transactions that way and let you download CSV, which you can bring into the same workbook.
Useful ideas start with a clearer picture of your own spending.
Explore your reports
