Table.Unpivot Function in Power Query M (3 Easy Examples)

Table.Unpivot turns wide, cross-tab columns into attribute-value rows. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you have a table where each category sits in its own column and you want those columns folded down into two columns, one for the labels and one for the values, this is the function you reach for.

Table of Contents

Syntax of Table.Unpivot Function

Table.Unpivot(table as table, pivotColumns as list, attributeColumn as text, valueColumn as text) as table

where

table (required, table). The table that holds the columns you want to unpivot.
pivotColumns (required, list). The list of column names to fold into attribute-value pairs.
attributeColumn (required, text). The name of the new column that will hold the old column headers.
valueColumn (required, text). The name of the new column that will hold the old cell values.

Returns: a table where the columns listed in pivotColumns are replaced by two columns, one named by attributeColumn and one named by valueColumn, with the remaining columns repeated on each new row.

So every chosen column becomes a row, the header goes into the attribute column, and the cell value goes into the value column.

Example 1: Unpivot quarterly headcount

Say you have a Headcount table with one row per department and a separate column for each quarter.

You want a long table with one row per department-and-quarter combination.

Here is the starting data:

Dept	Q1	Q2	Q3
Sales	12	15	14
Ops	8	9	11

Now list the three quarter columns and unpivot them:

let
    Source = Excel.CurrentWorkbook(){[Name="Headcount"]}[Content],
    Result = Table.Unpivot(Source,{"Q1","Q2","Q3"},"Quarter","Headcount")
in
    Result

Each quarter column becomes its own row, with the header in Quarter and the number in Headcount:

Dept	Quarter	Headcount
Sales	Q1	12
Sales	Q2	15
Sales	Q3	14
Ops	Q1	8
Ops	Q2	9
Ops	Q3	11

The Dept column stays put and repeats on each new row, while the three quarter columns collapse into the Quarter and Headcount pair. This is the same reshaping idea behind moving data that spans multiple rows into a single column.

Example 2: Unpivot the month columns

This works the same way for any cross-tab layout.

Here you have a Rainfall table with a City column and one column per month.

Here is the starting data:

City	Jan	Feb	Mar
Mumbai	2	1	3
Pune	4	2	5

List the three month columns and unpivot them into Month and Rainfall:

let
    Source = Excel.CurrentWorkbook(){[Name="Rainfall"]}[Content],
    Result = Table.Unpivot(Source,{"Jan","Feb","Mar"},"Month","Rainfall")
in
    Result

The month headers move into Month and their readings move into Rainfall:

City	Month	Rainfall
Mumbai	Jan	2
Mumbai	Feb	1
Mumbai	Mar	3
Pune	Jan	4
Pune	Feb	2
Pune	Mar	5

Here you are naming the columns to unpivot. When you would rather name the columns to KEEP and let everything else unpivot, Table.UnpivotOtherColumns is the sibling function. This example uses Table.Unpivot with an explicit list of the three month columns.

Example 3: Null cells are dropped

Table.Unpivot skips any cell whose value is null or blank, so the result can have fewer rows than you might expect.

Here is a Stores table where two cells are empty:

Store	Coffee	Tea	Juice
North	30		12
South	18	9

Unpivot the three drink columns into Drink and Units:

let
    Source = Excel.CurrentWorkbook(){[Name="Stores"]}[Content],
    Result = Table.Unpivot(Source,{"Coffee","Tea","Juice"},"Drink","Units")
in
    Result

Two rows are missing because their source cells were blank:

Store	Drink	Units
North	Coffee	30
North	Juice	12
South	Coffee	18
South	Tea	9

North has no Tea row and South has no Juice row, because Table.Unpivot omits any row whose value would be null or blank.

Things to keep in mind with Table.Unpivot

The value column comes out typed as any. Because it mixes the old columns together, you often need a Table.TransformColumnTypes step afterward to set it to Int64.Type, type number, or whatever fits. You can also add a new column of the right type instead of converting in place.
Column names in pivotColumns are case-sensitive and must exist. A typo or wrong case throws Expression.Error: The column 'Q1 ' of the table wasn't found. Match the headers exactly.
Keep the null-drop in mind. As Example 3 showed, blank source cells produce no rows, so a fully empty column simply disappears from the output.
Table.Unpivot lists the columns to unpivot; Table.UnpivotOtherColumns lists the columns to keep. Use the sibling when most columns should unpivot and only a key or two should stay.

Performance and query folding

Against a foldable source like SQL Server, an unpivot can sometimes fold into the source query, but it often runs locally instead. On Excel, CSV, and folder sources it always runs locally, which is fine for the small cross-tab tables this transform usually handles.

Common questions about Table.Unpivot

How do I pivot the data back into columns?

Use Table.Pivot, which is the inverse transform. Feed it the distinct attribute values and the value column to spread the attribute-value rows back out into one column per attribute.

List of All Power Query Functions

Related Power Query Functions / Articles: