The unpivot choice that decides if next month works

Budget and forecast files usually arrive with a column for each month. That’s easy to read and awkward to analyse, so Unpivot is often the first step in Power Query.

The Unpivot menu has three options, and the difference shows up the next time your source gets a new column.

Three options, one trap

Command	When a new month column appears
Unpivot Columns	It’s unpivoted too
Unpivot Other Columns	It’s unpivoted too
Unpivot Only Selected Columns	It’s left out and stays as its own column

So avoid Unpivot Only Selected Columns for anything that grows, unless you really do want a fixed list.

My preference is Unpivot Other Columns. You select the columns that stay fixed, such as Department and Account, so the query names what doesn’t change:

let
    Source = Excel.CurrentWorkbook(){[Name="Budget"]}[Content],
    Unpivoted = Table.UnpivotOtherColumns(Source, {"Department", "Account"}, "Month", "Amount"),
    Typed = Table.TransformColumnTypes(Unpivoted, {{"Month", type text}, {"Amount", type number}})
in
    Typed

The Changed Type trap

Power Query often adds a Changed Type step automatically, right after the source. It can list every column by name:

Table.TransformColumnTypes(Source, {{"Jan", type number}, {"Feb", type number}, {"Mar", type number}})

That’s a hard-coded list of months sitting before your unpivot. A new month won’t get the type, and if a named column later disappears, the refresh fails with an error saying the column wasn’t found.

The fix: delete that automatic step, then set types after unpivoting, as in the example above. By then there are only a few columns to type, and their names never change.

Quick check after any change

Add a test column to the source file and refresh. Then compare the total of Amount with the total in the source. If they agree, your query can handle next month.