Power QueryExercises

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
    Long

Result

ItemMonthUnits
KiteJan3
KiteFeb5
ReelJan2
ReelFeb0
ReelMar4

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Kite's empty March produced no row. Reel's 0 in February did: zero is a value, null isn't.

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

StepColumns
Unpivot Other ColumnsItem, Month, Units
Unpivot ColumnsItem, Apr, Month, Units

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The brittle version leaves Apr as a column, so April's units never reach the Units column.

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.
Functions in this lesson:Table.UnpivotOtherColumnsTable.Unpivot

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.