Unpivot columns so new months are picked up
Turn wide data long in a way that survives new columns.
~10 min read · intermediate · Module 14: Pivot and unpivot
By Tahir Nas · Examples run in our simulator · Published · Updated
Before this: Choose Columns vs Remove Columns
Spreadsheets often put one month per column. Analysis tools prefer one row per item per month: it filters, groups and charts without formulas per column. Unpivoting turns column headers into values.
let
Sheet = #table({"Item", "Jan", "Feb", "Mar"}, {{"Kite", 3, 5, null}, {"Reel", 2, 0, 4}}),
Long = Table.UnpivotOtherColumns(Sheet, {"Item"}, "Month", "Units")
in
LongResult
| Item | Month | Units |
|---|---|---|
| Kite | Jan | 3 |
| Kite | Feb | 5 |
| Reel | Jan | 2 |
| Reel | Feb | 0 |
| Reel | Mar | 4 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Other columns vs selected columns
This is the Choose-versus-Remove decision again. Unpivot Other Columns names the columns to keep (Item) and unpivots everything else, including April when it appears. Unpivot Columns names the month columns, so a new month stays wide on refresh. Run both against next quarter's file with an extra April column:
let
NextFile = #table({"Item", "Jan", "Feb", "Mar", "Apr"}, {{"Kite", 3, 5, 1, 6}}),
Robust = Table.UnpivotOtherColumns(NextFile, {"Item"}, "Month", "Units"),
Brittle = Table.Unpivot(NextFile, {"Jan", "Feb", "Mar"}, "Month", "Units")
in
#table({"Step", "Columns"}, {
{"Unpivot Other Columns", Text.Combine(Table.ColumnNames(Robust), ", ")},
{"Unpivot Columns", Text.Combine(Table.ColumnNames(Brittle), ", ")}
})Result
| Step | Columns |
|---|---|
| Unpivot Other Columns | Item, Month, Units |
| Unpivot Columns | Item, Apr, Month, Units |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Name the columns in the same step
The editor names the new columns Attribute and Value. Rename them in the same step by passing names, as above, so later steps refer to Month and Units.
After unpivoting, the Month column holds text such as "Jan". Convert it to a real date as part of the same query if you'll filter or sort by period (convert headers to dates).
Where to find this in Excel
- Excel for Windows
- Select the columns to keep > right-click > Unpivot Other Columns (Transform > Unpivot Columns has the same options)
- What you see
- The other columns become Attribute and Value pairs, and month columns added later are unpivoted on refresh.
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).
Common mistakes
- Selecting the month columns and choosing Unpivot Columns, so new months are ignored on refresh.
- Expecting a row for empty (null) cells after unpivoting.
- Leaving the default Attribute/Value names and referring to them in later steps.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: select the identifier columns → Transform → Unpivot Columns → Unpivot Other Columns. Unpivot Only Selected Columns writes Table.Unpivot with a fixed list.
- Selecting the month columns and choosing plain Unpivot Columns in the menu actually writes Table.UnpivotOtherColumns with the remaining columns, which is the robust form; check the formula bar to be sure which you got.
- The simulator skips null cells when unpivoting, like the native functions.
Next step
Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's part of Pro.
Practise: Unpivot month columns into rows (hardware)Practice this
Each exercise runs in the browser and is checked against fresh data, not just the example.
- 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.
- intermediate~8 minPro
Unpivot month columns into rows (pharmacy)
Turn one column per month into Month and Units rows, in a way that picks up new months automatically.
- intermediate~8 minPro
Unpivot month columns into rows (cafe)
Turn one column per month into Month and Units rows, in a way that picks up new months automatically.
- advanced~10 minPro
Unpivot only the quarter columns, whatever else the sheet has (budget)
The sheet has quarter columns (Q1 2026 …) and descriptive columns that change over time. Unpivot just the quarters.
- advanced~10 minPro
Unpivot only the quarter columns, whatever else the sheet has (targets)
The sheet has quarter columns (Q1 2026 …) and descriptive columns that change over time. Unpivot just the quarters.
- 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~15 minPro
Reshape month-and-measure columns into a tidy table (stalls)
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~10 minPro
Predict which cells an unpivot will drop (timesheet)
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~18 minPro
Project: budget versus actual by department and month (charity)
Combine a wide budget sheet with a long list of actual spend to report Budget, Actual and Variance per department and month.