Power QueryExercises

Unpivot columns in Power Query: a refresh-proof guide

Guide · By Tahir Nas · Examples run in our simulator · Published · Updated

Jump to the practice task

When you need it

Budgets, forecasts and survey exports put one period or question per column. That's easy to read and hard to analyse: you can't filter 'March to May' or chart a trend without a formula per column. Unpivoting turns the column headers into a column of values, so each row becomes one item in one period.

A wide table with Item, Jan, Feb and Mar columns becomes a long table with Item, Month and Units columns: one row per item and month, six rows from two.
Unpivot Other Columns keeps the identifier and turns every other column into rows.

The five-step recipe

  1. Remove any total columns (Total, YTD, Average) or plan to filter them out after the unpivot.
  2. Select the identifier columns (Account, Item, Store…).
  3. Choose Transform → Unpivot Columns → Unpivot Other Columns.
  4. Rename Attribute and Value to meaningful names in the same step.
  5. Convert the attribute to a real type (often a date) and set the value type.
let
    Forecast = #table({"Store", "Product", "2026-07", "2026-08", "2026-09", "Total"}, {
        {"Leeds", "Kite", 40, 55, null, 95},
        {"York", "Kite", 22, 18, 30, 70}
    }),
    Long = Table.UnpivotOtherColumns(Forecast, {"Store", "Product"}, "Period", "Units"),
    NoTotals = Table.SelectRows(Long, each [Period] <> "Total"),
    Typed = Table.TransformColumns(NoTotals, {{"Period", each Date.FromText(_ & "-01"), type date}})
in
    Typed

Result

StoreProductPeriodUnits
LeedsKite2026-07-0140
LeedsKite2026-08-0155
YorkKite2026-07-0122
YorkKite2026-08-0118
YorkKite2026-09-0130

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

Leeds has no September row because its cell was empty (null). The Total column was unpivoted and then filtered out by value.

Prove it survives a refresh

The point of Unpivot Other Columns is what happens next month. Test it: add a column to the source and check the output has the new period without any edit to the query. If you used Unpivot Only Selected Columns, the new column would stay wide.

let
    NextRefresh = #table({"Store", "Product", "2026-07", "2026-08", "2026-09", "2026-10"}, {{"Leeds", "Kite", 40, 55, 12, 61}}),
    Long = Table.UnpivotOtherColumns(NextRefresh, {"Store", "Product"}, "Period", "Units")
in
    Table.RowCount(Long)

Result

4

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

Four periods in, four rows out: October was picked up with no change to the query.

Blanks, zeros and duplicates

  • Unpivot drops null cells. If the report must show a row for every period, merge the long table onto a full list of periods afterwards.
  • Zero is a value, so a 0 cell does produce a row.
  • If two identifier columns are missing from the selection, rows that look different may collapse into the same key later; keep every column that identifies a row in the selection.

Practise it in the simulator

Each task opens the exact exercise in the browser workspace and is checked against fresh data, not just the example. These tasks are part of Pro.

Lessons

More resources

Function reference

Sources

Microsoft's official documentation for the features and functions used on this page.

Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.