Table.Pivot Function in Power Query M (5 Easy Examples)

If you want to turn rows into columns, or reshape a long, tall table into a wide one, the Table.Pivot function is what you reach for. It takes the values from one column and spreads them out into separate columns of their own.

It’s available in Excel (Power Query), Power BI Desktop, and Power BI Service.

Table of Contents

Syntax of Table.Pivot Function

Table.Pivot(table as table, pivotValues as list, attributeColumn as text, valueColumn as text, optional aggregationFunction as nullable function) as table

where

table (required, table). The source table, stored in long form as attribute-value pairs.
pivotValues (required, list). The distinct attribute values that become the new column headers. Often written as List.Distinct(Source[Col]) so the columns are discovered for you.
attributeColumn (required, text). The column whose values get rotated up into the new column headers.
valueColumn (required, text). The column whose values fill the new pivoted columns.
aggregationFunction (optional, nullable function). How to combine multiple values that fall under the same key and attribute, for example List.Sum. Required when duplicate combinations exist.

Returns: a table reshaped from long to wide. Missing key and attribute combinations come back as null, and duplicate combinations need an aggregationFunction or the refresh errors out.

Think of it as the opposite of unpivot. Unpivot folds columns down into rows, and Table.Pivot spreads those rows back up into columns.

Example 1: Basic pivot with a literal pivotValues list

Say you have call and email counts for each sales rep, stored one metric per row.

You want to spread the two metrics into their own columns.

Here is the starting data:

Rep	Metric	Amount
Alice	Calls	40
Alice	Emails	12
Bob	Calls	33
Bob	Emails	19

Pass the column names explicitly as a literal list:

let
    Source = Excel.CurrentWorkbook(){[Name="RepMetrics"]}[Content],
    Pivoted = Table.Pivot(Source, {"Calls", "Emails"}, "Metric", "Amount")
in
    Pivoted

The result produces:

Rep	Calls	Emails
Alice	40	12
Bob	33	19

The values in Metric become column headers, and the matching Amount for each rep fills the new cells.

Example 2: Discover the columns with List.Distinct

When you don’t want to type the column names by hand, build pivotValues from the data itself with List.Distinct.

Here is the starting data:

Store	Month	Sales
North	Jan	500
North	Feb	650
South	Jan	420
South	Feb	480

Feed the distinct Month values straight into the function:

let
    Source = Excel.CurrentWorkbook(){[Name="StoreSales"]}[Content],
    Pivoted = Table.Pivot(Source, List.Distinct(Source[Month]), "Month", "Sales")
in
    Pivoted

The result produces:

Store	Jan	Feb
North	500	650
South	420	480

The new columns line up in the order List.Distinct hands them back, which is the order the months first show up in the source.

Example 3: Combine duplicates with List.Sum

Sometimes a single key and attribute pair has more than one row. Here Dana has two Widgets rows.

Without an aggregationFunction, that clash would throw an error (see Example 5). Pass List.Sum to add the duplicate values together.

Here is the starting data:

Salesperson	Product	Revenue
Dana	Widgets	100
Dana	Widgets	150
Dana	Gadgets	80
Eli	Widgets	60
Eli	Gadgets	90

Add List.Sum as the fifth argument:

let
    Source = Excel.CurrentWorkbook(){[Name="ProductRevenue"]}[Content],
    Pivoted = Table.Pivot(Source, List.Distinct(Source[Product]), "Product", "Revenue", List.Sum)
in
    Pivoted

The result produces:

Salesperson	Widgets	Gadgets
Dana	250	80
Eli	60	90

Dana’s two Widgets rows (100 and 150) collapse into a single 250 because List.Sum combines them.

Example 4: Count values with List.Count

The aggregation function doesn’t have to add numbers. Here you count how many tickets each agent has at each priority, even though the value column holds text.

Here is the starting data:

Agent	Priority	Ticket
Sam	High	T1
Sam	High	T2
Sam	Low	T3
Riley	High	T4
Riley	Low	T5

Pass List.Count to count the tickets in each group:

let
    Source = Excel.CurrentWorkbook(){[Name="AgentTickets"]}[Content],
    Pivoted = Table.Pivot(Source, List.Distinct(Source[Priority]), "Priority", "Ticket", List.Count)
in
    Pivoted

The result produces:

Agent	High	Low
Riley	1	1
Sam	2	1

Sam has two High tickets, so that cell shows 2. The value column was text, but List.Count only cares how many values landed in each group.

Example 5: Pivoting duplicates without an aggregation function (error demo)

This example is here to show what happens when you forget the aggregation function. The same ProductRevenue data from Example 3 has two Widgets rows for Dana.

Here is the starting data:

Salesperson	Product	Revenue
Dana	Widgets	100
Dana	Widgets	150
Dana	Gadgets	80
Eli	Widgets	60
Eli	Gadgets	90

This time, leave the fifth argument off:

let
    Source = Excel.CurrentWorkbook(){[Name="ProductRevenue"]}[Content],
    Pivoted = Table.Pivot(Source, List.Distinct(Source[Product]), "Product", "Revenue")
in
    Pivoted

There is no result table here. On refresh, the query fails because Dana’s Widgets cell would have to hold two values (100 and 150) and Table.Pivot has no way to pick one. It throws:

Expression.Error: There were too many elements in the enumeration to complete the operation.

The fix is to add an aggregationFunction as the fifth argument, exactly as in Example 3, so the duplicate values collapse into one.

Things to keep in mind with Table.Pivot

Duplicate key and attribute combos need an aggregationFunction. Without one you get Expression.Error: There were too many elements in the enumeration to complete the operation. (Example 5). Add List.Sum, List.Count, List.Max, Text.Combine, or similar to collapse them.
Missing combinations come back as null. If a key has no row for one of the pivot values, that cell is null, not zero or blank.
pivotValues controls which columns appear and their order. Values left out of the list are dropped from the result, and the list order is the left-to-right column order. To reorder the rows afterward, follow up with <a href=”https://spreadsheetplanet.com/power-query/functions/table-sort/”>Table.Sort</a>.
It’s the inverse of Table.UnpivotOtherColumns. Pivot goes long to wide, unpivot goes wide to long, so the two undo each other.

Performance and query folding

Table.Pivot generally runs locally rather than folding back to the source, so it can be expensive on large tables. Using List.Distinct for pivotValues adds an extra scan of the column before the pivot runs. For typical Excel and CSV data it’s fine, but on big foldable sources expect the work to happen in the engine, not the database.

Common questions about Table.Pivot

What is the difference between Table.Pivot and Table.UnpivotOtherColumns?

They are inverse operations. Table.Pivot turns the values in a column into new columns (long to wide), while Table.UnpivotOtherColumns folds columns down into attribute-value rows (wide to long).

Why do I get the “too many elements” error?

A key and attribute combination has more than one value in the source, and you didn’t pass an aggregationFunction. Add one (like List.Sum) so those duplicate values collapse into a single cell. Example 5 shows the error and Example 3 shows the fix. If you instead need a fresh calculated column rather than reshaped data, reach for <a href=”https://spreadsheetplanet.com/power-query/functions/table-addcolumn/”>Table.AddColumn</a>.

List of All Power Query Functions

Related Power Query Functions / Articles: