Table.UnpivotOtherColumns in Power Query M (5 Examples)

If you want to reshape a wide table into a long one, Table.UnpivotOtherColumns is the function you reach for. It turns columns into rows, keeping only the columns you name and unpivoting everything else.

Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

Table of Contents

Syntax of Table.UnpivotOtherColumns Function

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

where

table (required, table). The source table you want to reshape from wide to long.
pivotColumns (required, list). The list of column names to KEEP. Every column NOT in this list gets unpivoted into rows.
attributeColumn (required, text). The name for the new column that holds the old column headers.
valueColumn (required, text). The name for the new column that holds the old cell values.

Returns: a table reshaped from wide to long. Any cell with a null value is dropped, so it produces no output row.

The one thing to get straight: you name the columns to KEEP, not the ones to unpivot. Power Query handles the rest.

Example 1: Unpivot all the other columns

Start with a small attendance table where each weekday is its own column.

Here is the starting data:

Class	Mon	Tue	Wed
Yoga	12	15	9
Spin	20	18	22

Keep Class and unpivot the three day columns into rows.

let
    Source = Excel.CurrentWorkbook(){[Name="ClassAttendance"]}[Content],
    Unpivoted = Table.UnpivotOtherColumns(Source, {"Class"}, "Day", "Attendees")
in
    Unpivoted

The result produces:

Class	Day	Attendees
Yoga	Mon	12
Yoga	Tue	15
Yoga	Wed	9
Spin	Mon	20
Spin	Tue	18
Spin	Wed	22

Each source row expands into one row per day column, and Class repeats down those rows.

Example 2: Name the attribute and value columns

The third and fourth arguments are free text, so make them meaningful instead of the default Attribute and Value.

Here is the starting data:

Origin	Light	Medium	Dark
Ethiopia	40	25	10
Colombia	30	35	20

Keep Origin and label the new columns RoastLevel and Kilograms.

let
    Source = Excel.CurrentWorkbook(){[Name="RoastBatches"]}[Content],
    Unpivoted = Table.UnpivotOtherColumns(Source, {"Origin"}, "RoastLevel", "Kilograms")
in
    Unpivoted

The result produces:

Origin	RoastLevel	Kilograms
Ethiopia	Light	40
Ethiopia	Medium	25
Ethiopia	Dark	10
Colombia	Light	30
Colombia	Medium	35
Colombia	Dark	20

The old headers land in RoastLevel and their values in Kilograms, which reads far better than generic names.

Example 3: Keep two key columns

pivotColumns is a list of any length, so you can hold more than one column in place.

Here is the starting data:

Site	Year	Q1	Q2	Q3	Q4
North	2024	110	180	200	130
South	2024	95	160	210	120

Keep both Site and Year, then unpivot the four quarter columns.

let
    Source = Excel.CurrentWorkbook(){[Name="SolarOutput"]}[Content],
    Unpivoted = Table.UnpivotOtherColumns(Source, {"Site", "Year"}, "Quarter", "MWh")
in
    Unpivoted

The result produces:

Site	Year	Quarter	MWh
North	2024	Q1	110
North	2024	Q2	180
North	2024	Q3	200
North	2024	Q4	130
South	2024	Q1	95
South	2024	Q2	160
South	2024	Q3	210
South	2024	Q4	120

Both Site and Year repeat across the four unpivoted rows for each source row.

Example 4: Null value cells are dropped

When a value cell is blank, no row is created for it.

Here is the starting data:

Genre	Downtown	Uptown
Fiction	320	280
History	150
SciFi		210

Keep Genre and unpivot the two branch columns.

let
    Source = Excel.CurrentWorkbook(){[Name="BranchCheckouts"]}[Content],
    Unpivoted = Table.UnpivotOtherColumns(Source, {"Genre"}, "Branch", "Checkouts")
in
    Unpivoted

The result produces:

Genre	Branch	Checkouts
Fiction	Downtown	320
Fiction	Uptown	280
History	Downtown	150
SciFi	Uptown	210

There are six value cells but only four output rows, because the two blank cells are skipped.

Example 5: Future-proof against new columns

Because you only name the key column, any value column added later is unpivoted automatically.

Here is the starting data:

Park	Spring	Summer	Autumn
Riverside	1200	3400	900
Hilltop	800	2600	700

Keep Park and unpivot whatever season columns exist.

let
    Source = Excel.CurrentWorkbook(){[Name="ParkVisitors"]}[Content],
    Unpivoted = Table.UnpivotOtherColumns(Source, {"Park"}, "Season", "Visitors")
in
    Unpivoted

The result produces:

Park	Season	Visitors
Riverside	Spring	1200
Riverside	Summer	3400
Riverside	Autumn	900
Hilltop	Spring	800
Hilltop	Summer	2600
Hilltop	Autumn	700

If a Winter column showed up next month, this same step would unpivot it with no edit needed.

Things to keep in mind with Table.UnpivotOtherColumns

You name the columns to KEEP, not the ones to unpivot. This is the whole point. It makes the step future-proof: any new column added to the source later gets unpivoted automatically, with no edit to the M. The same is not true of a step that targets columns by name, like one built with Table.AddColumn.
Null value cells produce no output row. A blank cell is skipped entirely (Example 4), so the result can have fewer rows than the source rows multiplied by the unpivoted columns.
Output order is predictable. For each source row, in row order, you get one row per unpivoted column in left-to-right column order. The kept columns repeat down those rows. If you need a different order afterward, follow this step with Table.Sort.
Pick meaningful names for attributeColumn and valueColumn. They are plain text. RoastLevel and Kilograms read better than the default Attribute and Value.
Passing an empty list {} unpivots the entire table. With no columns to keep, every header goes into the attribute column and every cell into the value column.

Performance and query folding

Against a foldable source, an unpivot step can sometimes fold to the database, but in practice it often runs locally. On Excel, CSV, or folder sources it always runs locally, which is fine for the typical wide tables you reshape this way.

Common questions about Table.UnpivotOtherColumns

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

With Table.Unpivot you list the columns you want to unpivot, so anything not listed stays put. With Table.UnpivotOtherColumns you list the columns to KEEP, and everything else is unpivoted. The second one is the robust choice, because new value columns added later are picked up automatically instead of being ignored.

How do I expand the long table back out again?

Unpivoting collapses your data into rows. If you later need to widen a column that holds nested tables, reach for Table.ExpandTableColumn instead.

List of All Power Query Functions

Related Power Query Functions / Articles: