Unpivot Month Columns in Power Query: Keep IDs, Zeros, and a Clear Blank Rule

Define the row that the output should represent

The input contains three products and three month columns. One output row should represent one product, one region, and one month with a recorded amount. Keep product and region together as identifiers; neither belongs in the amount column.

Use year-month headings such as 2026-01 rather than January alone. They distinguish years and sort chronologically as text when consistently padded. Remove worksheet totals and commentary columns before importing the sample. Otherwise, an unpivot of all non-identifier columns could mistake them for months.

Build a refreshable unpivot in desktop Excel

This walkthrough targets desktop Excel on Windows with Power Query. Enter the source in A1:E4, with blank cells for the missing monthly values. Create an Excel Table with headers and name it MonthlySales. The sample is intentionally small enough to inspect every resulting row.

1. Click inside MonthlySales and choose Data > From Table/Range to open Power Query Editor. Keep product and region as Text.
2. Inspect the month columns. The blank worksheet cells should appear as null. If an upstream source provides empty text instead, normalize that to null before applying the numeric type.
3. Set the three amount columns to Decimal Number. Review any conversion errors; do not remove error rows to force a matching row count.
4. Select product and region together, right-click a selected header, and choose Unpivot Other Columns. Rename Attribute to month and Value to amount.
5. Sort product ascending, then month ascending. Confirm the four expected observations, then choose Home > Close & Load to a new worksheet.

Choose how future columns will be handled

Use Unpivot Other Columns when product and region are the complete set of fixed identifiers and every other column is a monthly amount. If you later add 2026-04, it becomes another month on refresh, provided that it is inside MonthlySales.

For a fixed report covering only January through March, choose Unpivot Only Selected Columns on those three month headers. A subsequently added April column stays outside that specific unpivot operation. Do not assume the plain Unpivot Columns command always means a permanently fixed list; inspect the generated step and choose the explicit option that matches your rule.

Use an explicit empty-value policy

A null monthly value represents no recorded observation in this example. Table.Unpivot omits null entries; a product with only null monthly values therefore disappears from the long result. That is why P300 is absent. An empty text value is a different value, so normalize it deliberately instead of depending on an import’s guessing.

Zero means a recorded amount of zero and must remain. Replacing missing values with zero would create five additional observations in this sample. The total would still be 23, but the observation count would rise from four to nine and averages would change. An unchanged total is not proof that the missing-value policy is correct.

If a downstream system needs every product-month pair, retain a product-and-month scaffold and join recorded amounts to it, leaving absent amounts null. That is a different output contract from this sparse observation list; do not silently substitute zeros.

Inspect or reuse the complete query

The query below reads the named Excel Table, changes empty monthly text to null, converts monthly amounts using an explicit culture, unpivots the non-key columns, and sorts the result. It assumes every non-key header is a month and amounts are compatible with the declared numeric convention.

To use it, create MonthlySales as described, then replace the query in Home > Advanced Editor. If your source also contains owner, total, or notes columns, remove or explicitly protect them first. Review the query after a schema change; accepting any new column is convenient only when that column means another month.

let
    Source = Excel.CurrentWorkbook(){[Name="MonthlySales"]}[Content],
    Keys = {"product", "region"},
    Months = List.Difference(Table.ColumnNames(Source), Keys),
    EmptyTextToNull = Table.TransformColumns(
        Source,
        List.Transform(Months, (columnName) =>
            {columnName, each if _ = "" then null else _, type any})
    ),
    Typed = Table.TransformColumnTypes(
        EmptyTextToNull,
        List.Transform(Months, each {_, type number}),
        "en-US"
    ),
    Long = Table.UnpivotOtherColumns(Typed, Keys, "month", "amount"),
    Sorted = Table.Sort(Long, {{"product", Order.Ascending}, {"month", Order.Ascending}})
in
    Sorted

Validate the first load and the next month

Compare the loaded rows with the expected file. January totals 15 and March totals 8, so the total is 23. Confirm that March zero for P100 remains and that there are no February rows under the chosen missing-value rule.

For a refresh check, add 2026-04 inside the source Table with amounts 2, 3, and 4 for P100, P200, and P300. The Other Columns workflow should then produce seven rows totaling 32; P300 appears once for April. If April stays as a separate wide column, inspect which unpivot operation was generated.

Keep the long output as a Table for later summaries. If a PivotTable reports stale totals after refresh, confirm its source includes the refreshed output and refresh that report too.

The supplied M query was executed through the native Power Query provider in desktop Excel 16.0 build 5569, using the named MonthlySales Table. It returned the exact four expected rows, including the real zero and no February observations. Adding April inside the Table and refreshing produced seven rows totaling 32, with P300 appearing once. Independent checks also confirmed that replacing missing values with zero changes the base count to nine while retaining total 23. This verifies the named-table query output; it does not record the graphical Editor clicks or test every Excel edition.