Table.Unpivot
Turns the columns you name into rows of two columns: the old column's name and its value.
Group: Table. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Table.Unpivot(table as table, pivotColumns as list, attributeColumn as text, valueColumn as text) as table| Parameter | Type | Description |
|---|---|---|
table | table | The table in wide form. |
pivotColumns | list | The columns to turn into rows. |
attributeColumn | text | The name of the new column for the old column names. |
valueColumn | text | The name of the new column for their values. |
Returns
table. The columns you didn't name, then the two new ones: a row per old row and named column.
Remarks
- A null cell gives no row.
- When columns can be added in the source (a new month), name the columns to keep with
Table.UnpivotOtherColumnsinstead: Unpivot Other Columns in Transform data writes that.
Examples
Months into rows
Table.Unpivot(#table({"Shop", "Jan", "Feb"}, {{"Oslo", 10, 12}, {"Bergen", 7, null}}), {"Jan", "Feb"}, "Month", "Sales")| Shop | Month | Sales |
|---|---|---|
| Oslo | Jan | 10 |
| Oslo | Feb | 12 |
| Bergen | Jan | 7 |
Bergen has no February value, so it has no February row.