Table.UnpivotOtherColumns
Table function · By Tahir Nas · Examples run in our simulator · Published · Updated
Turns all columns except the listed ones into attribute/value rows.
When to use it
Use it to turn a wide layout (one column per month or product) into a long one with one row per item and attribute. It's the right choice when new period columns appear over time, because every column except the identifiers is unpivoted.
Syntax
Table.UnpivotOtherColumns(table as table, pivotColumns as list, attributeColumn as text, valueColumn as text) as tableParameters
- pivotColumns
- Columns to keep as identifiers.
Returns
A long table.
Example
Table.UnpivotOtherColumns(#table({"Item", "Jan", "Feb"}, {{"Kite", 3, 5}}), {"Item"}, "Month", "Units")Result
| Item | Month | Units |
|---|---|---|
| Kite | Jan | 3 |
| Kite | Feb | 5 |
Example: months to rows, then typed
After unpivoting, the attribute column is text, so convert it if it holds dates or numbers.
Table.TransformColumnTypes(Table.UnpivotOtherColumns(#table({"Item", "2026-01", "2026-02"}, {{"Kite", 3, 5}}), {"Item"}, "Month", "Units"), {{"Units", Int64.Type}})Result
| Item | Month | Units |
|---|---|---|
| Kite | 2026-01 | 3 |
| Kite | 2026-02 | 5 |
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
- Null cells produce no rows.
- New columns are unpivoted automatically on refresh.
Common mistakes
- Using Unpivot Columns (a fixed list) on data where columns are added every month.
- Leaving total columns in the input, so totals become rows.
The matching command in Excel
- Excel for Windows
- Select the columns to keep > right-click > Unpivot Other Columns
- What you see
- Every other column becomes Attribute and Value pairs, including columns added later.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Unpivot columns (Microsoft Learn).
Exercises that use Table.UnpivotOtherColumns
- intermediate~8 minPro
Unpivot month columns into rows (hardware)
Turn one column per month into Month and Units rows, in a way that picks up new months automatically.
- advanced~15 minPro
Reshape month-and-measure columns into a tidy table (kiosks)
Columns look like "2026-01 Sales" and "2026-01 Units". Produce one row per item and month with Sales and Units columns.
- advanced~10 minPro
Predict which cells an unpivot will drop (stock sheet)
Before unpivoting a sheet with blank (null) cells, list exactly the item–month cells that will disappear.
- advanced~18 minPro
Project: budget versus actual by department and month (school)
Combine a wide budget sheet with a long list of actual spend to report Budget, Actual and Variance per department and month.
- advanced~14 minPro
Debug a query that works today and breaks on refresh (retail)
The starter passes on this month's sheet but fails next month: it removes a column that may vanish and unpivots a fixed month list.
Learn more
- Unpivot columns so new months are picked up lesson
- Unpivot columns in Power Query: a refresh-proof guide guide
Related functions
Official documentation: Table.UnpivotOtherColumns on Microsoft Learn.