Unpivot, then turn header text into real dates
Turn unpivoted header text into typed dates that survive new periods.
~9 min read · intermediate · Module 14: Pivot and unpivot
By Tahir Nas · Examples run in our simulator · Updated
Before this: Unpivot columns so new months are picked up, Convert dates and numbers with a locale
After an unpivot, the Month column holds the old header text. Text like "2026-01" sorts correctly, but it can't be filtered by date range, joined to a calendar or used with date functions. Convert it as part of the reshape, and new periods are converted automatically on refresh.
ISO-like headers
let
Budget = #table({"Account", "2026-01", "2026-02", "2026-03"}, {{"Travel", 500, 450, 600}, {"Training", 200, null, 250}}),
Long = Table.UnpivotOtherColumns(Budget, {"Account"}, "Period", "Budget"),
Dated = Table.TransformColumns(Long, {{"Period", each Date.FromText(_ & "-01"), type date}})
in
DatedResult
| Account | Period | Budget |
|---|---|---|
| Travel | 2026-01-01 | 500 |
| Travel | 2026-02-01 | 450 |
| Travel | 2026-03-01 | 600 |
| Training | 2026-01-01 | 200 |
| Training | 2026-03-01 | 250 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Month-name headers
Headers like "Jan 26" or "January 2026" depend on a culture, because month names do. Pass it explicitly, and include the format if the text isn't a standard date:
let
Sheet = #table({"Store", "Jan 2026", "Feb 2026"}, {{"Leeds", 10, 12}}),
Long = Table.UnpivotOtherColumns(Sheet, {"Store"}, "Month", "Sales"),
Dated = Table.TransformColumns(Long, {{"Month", each Date.FromText("1 " & _, [Culture = "en-GB"]), type date}})
in
DatedResult
| Store | Month | Sales |
|---|---|---|
| Leeds | 2026-01-01 | 10 |
| Leeds | 2026-02-01 | 12 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Don't unpivot the totals
Wide sheets often have a Total or YTD column. Unpivot Other Columns treats it as just another month, and Date.FromText then fails on "Total". Remove such columns before unpivoting, by name, or filter them out of the long table by value before converting:
let
Budget = #table({"Account", "2026-01", "2026-02", "Total"}, {{"Travel", 500, 450, 950}}),
Long = Table.UnpivotOtherColumns(Budget, {"Account"}, "Period", "Budget"),
NoTotals = Table.SelectRows(Long, each [Period] <> "Total"),
Dated = Table.TransformColumns(NoTotals, {{"Period", each Date.FromText(_ & "-01"), type date}})
in
DatedResult
| Account | Period | Budget |
|---|---|---|
| Travel | 2026-01-01 | 500 |
| Travel | 2026-02-01 | 450 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Filtering by value after the unpivot is the more robust of the two: a 'Total' column that appears only in some files is handled without a MissingField option.
Where to find this in Excel
- Excel for Windows
- Right-click > Unpivot Other Columns, then right-click Attribute > Change Type > Using Locale
- What you see
- The month headers become rows of text, and Using Locale turns them into real dates in the right format.
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); Data types in Power Query (Microsoft Learn).
Common mistakes
- Leaving the period as text, so a calendar join or a date filter doesn't work.
- Unpivoting a Total column along with the months, doubling the figures.
- Converting month-name headers without a culture.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: after Unpivot Other Columns, use Change Type → Using Locale on the attribute column, or a custom transform like the ones here for non-standard text.
- In Power BI, a real date column lets you relate the table to a date dimension; a text period can't use time-intelligence functions.
- The simulator's Date.FromText supports the cultures listed on the compatibility page and common day/month-name forms.
Date.FromTextTable.TransformColumnTypesNext step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the Pivot and unpivot quiz