Power QueryExercises

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
    Dated

Result

AccountPeriodBudget
Travel2026-01-01500
Travel2026-02-01450
Travel2026-03-01600
Training2026-01-01200
Training2026-03-01250

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

Appending "-01" makes each header a full ISO date, which Date.FromText reads in any culture.

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
    Dated

Result

StoreMonthSales
Leeds2026-01-0110
Leeds2026-02-0112

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
    Dated

Result

AccountPeriodBudget
Travel2026-01-01500
Travel2026-02-01450

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.

Next step

Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.

Take the Pivot and unpivot quiz