Table.Unpivot
Turns several columns into rows, with one row for each combination of a column and its value.
Table.Unpivot(table as table, pivotColumns as list, attributeColumn as text, valueColumn as text) as tableReturns: A long table with an attribute column and a value column.
What it does
Table.Unpivot converts a wide report, with a column for each month, into a long table with one row for each month. The long form is what a summary, a pivot table or a data model needs. The columns you list are the ones that become rows. Every other column is left as it is.
Syntax and arguments
- table: the wide table to change. Required.
- pivotColumns: a list of the column names to turn into rows, in braces. Required.
- attributeColumn: the name of the new column that holds the old column headings, in quotes. Required.
- valueColumn: the name of the new column that holds the values, in quotes. Required.
Example
A budget for 2026 has one row for each account and one column for each month. The twelve month columns are turned into rows, so each account has twelve rows.
let
Source = Excel.CurrentWorkbook(){[Name = "Budget2026"]}[Content],
Long = Table.Unpivot(
Source,
{"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"},
"Month",
"Budget"
)
in
Long
Each month heading becomes a value in the Month column, and each budget figure lands in the Budget column. The Account column is repeated on every row.
Things to watch
- A new month added to the grid is not picked up. Use Table.UnpivotOtherColumns when the set of columns changes.
- The Budget column holds whatever type the source had, often text. Type it after the step.
- Blank cells come through as blank rows. Remove them with Table.SelectRows if they are not needed.
- Query folding: this step usually breaks folding, so do it after filters that reduce the data.
In business terms
Budget and forecast grids come out of planning tools with months across the top. Unpivoting them is the step that makes budget versus actual reporting possible in a pivot table or a model.