Power QueryExercises

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
    Pivoted

Result

RegionOpenClosed
North21
South11

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.

With Agent as the value column and Ticket removed, the grain is Region again: two rows. Leaving an extra column in would split the rows.

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.
Functions in this lesson:Table.PivotList.CountList.Distinct

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.