Unpivot columns in Power Query

In short: select the columns that should stay as they are (for example Product), then Transform > Unpivot Other Columns. The remaining headers become a column called "Attribute", their values a column called "Value". In M: Table.UnpivotOtherColumns(Source, {"Product"}, "Attribute", "Value").

Try it now

These unit sales have one column per month. Turn them into a list with one row per product and month.

Task The unit sales are in a crosstab with one column per month. Turn it into a list: one row per product and month.

	A	B	C	D
1	Product	Jan	Feb	Mar
2	Paper	120	135	110
3	Toner	80		95
4	Binders	60	70	65
5	Pens	40	45

Notice two things in the result: the empty cells are gone, and the month names now sit as values in the Attribute column. Both are intended. Empty values never produce a row when you unpivot.

Why a list beats a crosstab

A crosstab has a separate column for every month. When a month is added, the layout changes, and every formula that stops at column D needs fixing. In a list, a new month is just more rows. A PivotTable can then show months across, products down, or the other way round, and SUMIFS can total by product and month at the same time.

Before (crosstab)	After (list)
Product | Jan | Feb | Mar	Product | Attribute | Value
Paper | 120 | 135 | 110	Paper | Jan | 120 Paper | Feb | 135 Paper | Mar | 110

Three ways to unpivot

• Unpivot Other Columns: you select the columns that stay. Everything else is turned into rows, including month columns that show up in next month's export. Usually the right choice.
• Unpivot Columns: you select the columns to turn. Microsoft writes the step so that it picks up new columns as well.
• Unpivot Only Selected Columns: exactly the selected columns are turned, new columns are left alone.

Afterwards, double-click Attribute and Value to give them real names, such as Month and Units.

One more exercise: drop the total first

Reports often carry a total column on the right. If you unpivot it along with the rest, "Total" turns into a fake quarter and every sum built on the list doubles.

Task Turn the quarterly figures into a list (one row per region and quarter). The Total column does not belong in the list, otherwise it would be counted twice later.

	A	B	C	D	E	F
1	Region	Q1	Q2	Q3	Q4	Total
2	North	310	280	330	360	1280
3	South	190	210	205	240	845
4	West	150	160	170	155	635

This exercise is part of Premium.

Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.

See Premium Already Premium? Log in

The first exercise in this lesson is free.

The M code of the second exercise

= Table.RemoveColumns(Source,{"Total"})
= Table.UnpivotOtherColumns(#"Removed Columns", {"Region"}, "Attribute", "Value")

Table.UnpivotOtherColumns only names the columns that stay. The quarters are not mentioned anywhere. That is exactly why the step picks up a new quarter on the next refresh without any changes on your side.

Common mistakes

Totals come out twice as high

A total column was unpivoted too. Remove it first with Remove Columns. The same applies to a total row at the bottom of the report: filter it out with Filter Rows before you unpivot.

The wrong columns get turned

With Unpivot Other Columns you select what stays, not the months. If several columns stay, such as Region and Product, select both.

The values are text

Numbers that arrived as text stay text after unpivoting. Set the Value column's data type to Whole Number or Decimal Number.

In German Excel

The German commands are Andere Spalten entpivotieren, Spalten entpivotieren and Nur ausgewählte Spalten entpivotieren on the Transformieren tab. The new columns are called Attribut and Wert.

Which Excel versions support this?

Unpivot is part of the Power Query Editor in Excel 2016 and later for Windows and in Microsoft 365. On a Mac it needs Microsoft 365. Without Power Query, Excel has no single command for it. See the Power Query overview for details.

FAQ