Downloads
Power Query MTable: headers and shapeIntermediate

Table.UnpivotOtherColumns

Turns every column except the ones you keep into rows.

Syntax
Table.UnpivotOtherColumns(table as table, pivotColumns as list, attributeColumn as text, valueColumn as text) as table

Returns: A long table, with the kept columns repeated for each column that was turned into rows.

What it does

Table.UnpivotOtherColumns turns every column into rows, except the columns you keep. This is the safer form of Table.Unpivot for a monthly grid, because a new month column is picked up automatically. You name the identifying columns, such as the account and the department, and the rest become rows.

Syntax and arguments

Example

A budget grid has Account and Department as its identifying columns, and a month column for each month. Every month column becomes a row without naming the months.

let
    Source = Excel.CurrentWorkbook(){[Name = "BudgetByDept"]}[Content],
    Long = Table.UnpivotOtherColumns(Source, {"Account", "Department"}, "Month", "Budget")
in
    Long

Each row is one account, one department and one month. When a month column is added to the sheet next year, it is turned into rows as well, with no change to the query.

Things to watch

In business terms

Planning files tend to gain a column each year. Keeping the identifying fields and unpivoting the rest means the same query works for the next budget cycle without edits.

Related functions

All Power Query M functions · View as Markdown · M basics