Unpivot Columns
Power BI · Power Query (Data Transformation)
Theory
Unpivot turns columns into rows. Many spreadsheets are wide: one row per region and one column per month. Power BI works best with long (tidy) data: one row per region per month, with a Month column and a value column. Unpivot does this conversion. Select the columns that identify each row, here Region, and choose Unpivot Other Columns: every other column header becomes a value in a new Attribute column, and each cell becomes a value in a Value column, which you then rename.
For example, a Targets sheet with Region, Jan, Feb, Mar and two rows becomes six rows: North Jan 1000, North Feb 1500 and so on. Unpivot Other Columns is safer than unpivoting the selected month columns, because a new Apr column added next month is picked up automatically. Null cells are skipped by unpivot, so they do not create rows.
Why we use it
Wide tables cannot be filtered by month, joined to a Date table or plotted as a trend. Unpivoting gives one value column you can sum and a Month column you can slice by.
When to use it
Use it whenever headers contain data values such as months, years or product names. The reverse, Pivot Column, is for turning a long attribute back into columns, which is rarely needed for modeling.
How it works
- Wide table: one column per month
- Select the identifier column (Region)
- Transform → Unpivot Other Columns
- Each month header becomes a row value
- Rename Attribute and Value, set types
- Long table ready to sum and slice
Syntax
Select the ID column(s) → Transform → Unpivot Columns → Unpivot Other Columns
Rename Attribute → Month, Value → Target
M:
Table.UnpivotOtherColumns(table as table, pivotColumns as list, attributeColumn as text, valueColumn as text)Example
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Targets, {"Region"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",
{{"Attribute", "Month"}, {"Value", "Target"}})
// Or name them directly:
Table.UnpivotOtherColumns(Targets, {"Region"}, "Month", "Target")Key points
- Wide (columns per month) → long (one row per month)
- Unpivot Other Columns keeps working when new month columns appear
- Attribute and Value columns should be renamed
- Null cells are skipped, so they produce no rows
- Set the data type of the new value column after unpivoting