Power Query: Unpivot Month Columns With Schema Guards

When an Excel report adds a new month column, reshape the month headers into a single Month field and their values into an Amount field. Discover valid month columns at refresh time, retain the intended identifiers, and reject unexpected nonmonth fields. This gives you a stable output shape while allowing the month range to grow.

This tutorial uses Power Query M in Excel or Power BI. Its fictional source has Account, Region, monthly values, and a precomputed Total. The full query checks the input schema before unpivoting so a new comment column cannot silently become a financial value. The M functions were checked against Microsoft documentation; the query was not executed in a Power Query runtime. The fixture arithmetic was independently checked in Python.

Define the table you need to load

Suppose a finance workbook arrives in this wide shape:

The desired grain is one account-region-month observation. A and North in January is one observation; A and North in February is another. The Month field must be a date, not a growing collection of column names. Total is a source summary for checking the result, not an additional observation.

Microsoft's unpivot overview explains the attribute-value transformation. The important production decision is which fields are permitted to become values. A broad “unpivot other columns” rule includes every field outside its retained identifier list, including an unexpected Notes or Total column.

Choose how the query should handle schema changes

• Account and Region: required dimensions. Their disappearance stops refresh.
• Exact yyyy-MM headers: monthly value fields. A valid new month is included automatically.
• Total: explicitly ignored in the output, retained in the raw source for reconciliation.
• Any other field: stop and review. Decide whether it is another dimension, an intentionally ignored field, or a source error.

This contract intentionally rejects headers such as Jan, 2026-1, 2026-13, and 2026-01 followed by a space. Convert inconsistent headers in a documented upstream step; do not let an ambiguous date parser decide what they mean. A new Owner field should prompt a grain decision before it is repeated across all monthly observations.

Copy the complete M query

Create a blank query, open Advanced Editor, and paste this self-contained example. For your workbook, replace only the Source step with your existing cleaned input step. Keep its raw-source query available for investigating errors.

let
    ToMonthly = (Input as table) as table =>
        let
            Dimensions = {"Account", "Region"},
            Ignored = {"Total"},
            Names = Table.ColumnNames(Input),
            IsMonth = (Name as text) as logical =>
                let
                    Parsed = try Date.FromText(
                        Name & "-01", [Format = "yyyy-MM-dd", Culture = "en-US"]
                    ) otherwise null
                in
                    if Parsed = null then false
                    else Date.ToText(Parsed, [Format = "yyyy-MM", Culture = "en-US"]) = Name,
            Months = List.Sort(List.Select(Names, each IsMonth(_))),
            Missing = List.Difference(Dimensions, Names),
            Unexpected = List.Difference(Names, List.Combine({Dimensions, Ignored, Months})),
            Checked =
                if not List.IsEmpty(Missing) then
                    error Error.Record("MissingDimensions", "Required columns disappeared", Missing)
                else if not List.IsEmpty(Unexpected) then
                    error Error.Record("UnexpectedColumns", "Review the source schema", Unexpected)
                else if List.IsEmpty(Months) then
                    error Error.Record("MissingMonths", "No yyyy-MM columns found", null)
                else Input,
            Keys = Table.SelectColumns(Checked, Dimensions),
            ValidKeys =
                if Table.RowCount(Table.SelectRows(Keys, each [Account] = null or [Region] = null)) > 0 then
                    error Error.Record("MissingKeys", "Account and Region cannot be null", null)
                else if Table.RowCount(Table.Distinct(Keys)) <> Table.RowCount(Keys) then
                    error Error.Record("DuplicateKeys", "Expected one row per Account and Region", null)
                else Checked,
            Selected = Table.SelectColumns(ValidKeys, List.Combine({Dimensions, Months})),
            Typed = Table.TransformColumnTypes(
                Selected, List.Transform(Months, each {_, type nullable number}), "en-US"
            ),
            Long = Table.Unpivot(Typed, Months, "Month", "Amount"),
            Dated = Table.TransformColumns(Long, {{"Month", each Date.FromText(
                _ & "-01", [Format = "yyyy-MM-dd", Culture = "en-US"]
            ), type date}})
        in
            Table.Sort(Dated, {{"Account", Order.Ascending}, {"Region", Order.Ascending}, {"Month", Order.Ascending}}),
    Source = #table(
        {"Account", "Region", "2026-01", "2026-02", "2026-03", "Total"},
        {
            {"A", "North", 100, 0, 150, 250},
            {"B", "South", 80, null, 120, 200}
        }
    )
in
    ToMonthly(Source)

The month detector uses an explicit Date.FromText format and culture, followed by a round-trip comparison. It discovers columns dynamically but accepts only the exact declared naming convention. The use of try is limited to testing a candidate header; a bad numeric value is not silently converted to zero.

Table.SelectColumns retains the reviewed dimensions and detected months. The Total field is excluded before Table.Unpivot. The explicit month list cannot accidentally include an identifier or summary field. The query then turns each monthly label into its month's first date.

Month values are converted using Table.TransformColumnTypes with en-US numeric conventions. Change that culture to match your actual file contract. A text value such as pending should remain a conversion error for investigation, not become an invented measurement.

Reconcile five output rows to six source cells

The source contains two rows times three months, or six monthly cells. One cell is null, so the ordinary unpivot result has five rows. The explicit zero for A in February survives. Microsoft demonstrates null omission in its Table.Unpivot example; do not assume row count always equals source rows multiplied by month count.

The nonnull monthly cells sum to 450. Account A contributes 250 and B contributes 200. The source Total column also sums to 450. If you unpivoted Total alongside the months and summed everything, you would get 900: the same amounts counted once as observations and again as summaries. Removing the summary field fixes the duplication; dividing a report measure by two would conceal it.

These are expected results for the fictional fixture, not observed Power BI refresh results. In your editor, confirm five rows, a 450 total, a retained zero, date-typed Month, and unique Account-Region-Month combinations before loading.

Keep unknown values different from known zero

A null might mean not submitted, not applicable, or unavailable. Decide its meaning with the source owner. Replacing every null with zero asserts that no activity occurred and changes the number of observed measurements.

If every account-month combination must appear, build an account-region by reporting-month scaffold, then left-join the unpivoted values onto it. Keep the unmatched Amount null unless the business rule explicitly defines absence as zero. For this fixture the scaffold has six rows, one unknown amount, and one known zero; the total still uses only known values.

An all-null source month can disappear entirely from the unpivoted result. Audit the detected header list as well as the output month list, and compare both to the expected reporting calendar. The schema guard allows new valid month headers; it does not prove that a previously required month has not disappeared.

Test the next refresh before trusting it

Run these edits in a disposable copy of the fixture, one at a time. After adding April, update source summary totals if they are part of your reconciliation: A becomes 270 and B becomes 230. For actual refresh acceptance, compare source key counts, detected months, null counts, output key uniqueness, and per-account and per-month totals. A matching grand total alone can hide offsetting errors.

Troubleshoot before changing the data model

• A new month is absent: inspect Table.ColumnNames at the step passed to ToMonthly. An earlier hard-coded “Removed Other Columns” step may already have discarded it.
• Totals doubled: inspect the raw attributes before aggregating. Expect only valid months, with Total excluded. Also inspect downstream relationships if the query total is correct.
• A valid amount becomes an error: inspect its raw string and source culture. Resolve decimal separators and placeholders explicitly rather than applying Replace Errors with zero.
• Desktop succeeds but scheduled refresh fails: confirm that both environments read the same file version and schema. Then use the separate Desktop versus Service refresh diagnosis for credentials and gateway checks.

FAQ

Can I just use Unpivot Other Columns?

Yes, when every future nonidentifier field is guaranteed to be a measurement. Its documented behavior includes all columns outside the retained set. Use explicit month detection when summaries or new descriptive fields can arrive.

Why is a blank month's row missing?

Ordinary unpivot omits null values. A source zero remains an observation. If the report must display missing combinations, generate a reporting calendar scaffold and preserve unknown amounts instead of automatically filling them with zero.

Should I fix a changing month header with DAX?

Start in Power Query when the problem is the physical shape of the source. Unpivot gives the model stable Month and Amount fields. A DAX measure can then calculate a result over that table; it does not replace source schema validation.