Power QueryExercises

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 table

Parameters

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

RegionJanFeb
North35
South21

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

RepWonLost
Ana21

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

Common mistakes

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

Learn more

Related functions

Official documentation: Table.Pivot on Microsoft Learn.