Table.Pivot
Table function · By Tahir Nas · Examples run in our simulator · Published · Updated
Turns values of one column into new columns.
When to use it
Use it to spread one column's values into columns, such as months across the top. Pick an aggregation whenever a row key and attribute can repeat. For analysis, the long layout is usually better; pivot at the end for presentation.
Syntax
Table.Pivot(table as table, pivotValues as list, attributeColumn as text, valueColumn as text, optional aggregationFunction as nullable function) as tableParameters
- pivotValues
- Values that become columns, often List.Distinct(table[col]).
- aggregationFunction
- How to combine several values, such as List.Sum.
Returns
A wide table.
Example
Table.Pivot(#table({"Region", "Month", "Units"}, {{"North", "Jan", 3}, {"North", "Feb", 5}, {"South", "Jan", 2}, {"South", "Feb", 1}}), {"Jan", "Feb"}, "Month", "Units", List.Sum)Result
| Region | Jan | Feb |
|---|---|---|
| North | 3 | 5 |
| South | 2 | 1 |
Example: count instead of sum
List.Count as the aggregation counts the rows per cell instead of adding values.
Table.Pivot(#table({"Rep", "Status", "Id"}, {{"Ana", "Won", 1}, {"Ana", "Won", 2}, {"Ana", "Lost", 3}}), {"Won", "Lost"}, "Status", "Id", List.Count)Result
| Rep | Won | Lost |
|---|---|---|
| Ana | 2 | 1 |
Both results are produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M, and checked on every build.
Good to know
- Other columns become the row key; remove the ones you don't want first.
Common mistakes
- No aggregation with duplicate keys, giving 'too many elements in the enumeration' errors.
- Leaving extra columns in the input, which become part of the row key.
The matching command in Excel
- Excel for Windows
- Transform > Pivot Column
- What you see
- You pick the values column and, under Advanced options, the aggregate function.
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).
Exercises that use Table.Pivot
- advanced~10 minPro
Pivot order statuses into columns (kite shop)
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.
- advanced~15 minPro
Reshape month-and-measure columns into a tidy table (kiosks)
Columns look like "2026-01 Sales" and "2026-01 Units". Produce one row per item and month with Sales and Units columns.
- 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.
- 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~13 minPro
Turn a list of name/value attributes into columns (apparel)
Each product has an attributes list like [{name: colour, value: red}, …]. Make one column per attribute name.
Learn more
Related functions
Official documentation: Table.Pivot on Microsoft Learn.