Pivot a column with an aggregation
Pivot long data into a cross-tab with the right row grain.
~10 min read · intermediate · Module 14: Pivot and unpivot
By Tahir Nas · Examples run in our simulator · Published · Updated
Before this: Group by with several aggregations
Pivoting turns distinct values of one column into new columns, filled from a value column. It's the opposite of unpivot, and it's mostly used for presentation: a cross-tab of regions by status, for example.
let
Tickets = #table({"Ticket", "Region", "Status", "Agent"}, {
{"T-1", "North", "Open", "Ana"},
{"T-2", "North", "Closed", "Ben"},
{"T-3", "South", "Open", "Ana"},
{"T-4", "North", "Open", "Cleo"},
{"T-5", "South", "Closed", "Ben"}
}),
Kept = Table.SelectColumns(Tickets, {"Region", "Status", "Ticket"}),
Pivoted = Table.Pivot(Kept, List.Distinct(Kept[Status]), "Status", "Ticket", List.Count)
in
PivotedResult
| Region | Open | Closed |
|---|---|---|
| North | 2 | 1 |
| South | 1 | 1 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Row grain comes from the other columns
Every column that isn't the pivot column or the value column becomes part of the row key. Leave Agent in and you get one row per region per agent. That's why the example selects only Region, Status and Ticket first:
let
Tickets = #table({"Ticket", "Region", "Status", "Agent"}, {
{"T-1", "North", "Open", "Ana"},
{"T-2", "North", "Closed", "Ben"},
{"T-3", "South", "Open", "Ana"},
{"T-4", "North", "Open", "Cleo"},
{"T-5", "South", "Closed", "Ben"}
}),
Pivoted = Table.Pivot(Table.RemoveColumns(Tickets, {"Ticket"}), List.Distinct(Tickets[Status]), "Status", "Agent", List.Count)
in
Table.RowCount(Pivoted)Result
2
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Aggregation
If several rows share a key and pivot value, Power Query needs to combine them: count, sum, max and so on. Choosing Don't aggregate puts an error in any cell that meets more than one value (next lesson).
New values create new columns
The pivoted columns come from the value list at refresh time (List.Distinct here), so a new status such as Escalated appears as a new column. That's convenient for exploration and awkward for reports that expect fixed columns. If the layout must be fixed, pass a fixed list, {"Open", "Closed"}, and decide what happens to other statuses.
Where to find this in Excel
- Excel for Windows
- Transform > Pivot Column (Any Column group)
- What you see
- Pick the values column; Advanced options let you choose the aggregate, such as Sum or Count.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Pivot columns (Microsoft Learn).
Common mistakes
- Pivoting with extra columns still in the table, so the rows split by them.
- Hard-coding the pivot value list and silently dropping a new category.
- Using Don't aggregate on data with repeated key/value pairs.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: select the column whose values become headers → Transform → Pivot Column; choose the values column and the aggregation under Advanced options.
- The editor writes
List.Distinct(Previous[Status])as the column list, so new values become new columns on refresh. - The simulator supports Table.Pivot with or without an aggregation function.
Next step
Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's part of Pro.
Practise: Pivot order statuses into columns (kite shop)Practice this
Each exercise runs in the browser and is checked against fresh data, not just the example.
- advanced~10 minPro
Pivot order statuses into columns (kite shop)
Count orders per region with one column per status (Complete, Cancelled, Pending).
- advanced~10 minPro
Pivot order statuses into columns (bakery)
Count orders per region with one column per status (Complete, Cancelled, Pending).
- advanced~10 minPro
Pivot order statuses into columns (bike rental)
Count orders per region with one column per status (Complete, Cancelled, Pending).
- intermediate~9 minPro
Pivot months into columns, summing repeated entries (bakeries)
Each store can have several sales lines per month. Pivot Month into columns with the total Sales per store and month.
- intermediate~9 minPro
Pivot months into columns, summing repeated entries (garden)
Each store can have several sales lines per month. Pivot Month into columns with the total Sales per store and month.
- intermediate~8 minPro
Turn attribute–value rows into one row per record (intake)
A form tool stores answers as (RecordID, Field, Value) rows. Pivot Field so each record becomes one row.
- intermediate~8 minPro
Turn attribute–value rows into one row per record (feedback)
A form tool stores answers as (RecordID, Field, Value) rows. Pivot Field so each record becomes one row.
- advanced~12 minPro
Pivot categories and add a total that includes future columns (consultancy)
Pivot expense categories into columns (summing amounts), then add a Total across every category column, including new ones.
- advanced~12 minPro
Pivot categories and add a total that includes future columns (school)
Pivot expense categories into columns (summing amounts), then add a Total across every category column, including new ones.