Table.UnpivotOtherColumns
TableUnpivots all columns except the specified ones into attribute-value pairs.
Syntax
Table.UnpivotOtherColumns(table as table, pivotColumns as list, attributeColumn as text, valueColumn as text) as tableParameters
| Name | Type | Required | Description |
|---|---|---|---|
table | table | Yes | The input table containing columns to unpivot. |
pivotColumns | list | Yes | A list of column names to keep as-is (not unpivoted). |
attributeColumn | text | Yes | The name of the new column that holds the original column names. |
valueColumn | text | Yes | The name of the new column that holds the values from the unpivoted columns. |
Return Value
table — A table with all non-specified columns unpivoted into rows of attribute-value pairs.
Remarks
Table.UnpivotOtherColumns is the complement of Table.Unpivot. Instead of specifying which columns to unpivot, you specify which columns to keep — everything else gets unpivoted into attribute-value pairs.
This is generally preferred over Table.Unpivot when the source table may gain new columns over time (e.g., new months, new product lines). With Table.UnpivotOtherColumns, new columns are automatically unpivoted without changing your query. With Table.Unpivot, you would need to update the column list manually.
Important: Like Table.Unpivot, null values are silently removed during the unpivot operation. Replace nulls with a placeholder using Table.ReplaceValue before unpivoting if you need to preserve missing values.
Examples
Example 1: Unpivot all columns except Product
let
Source = Table.SelectColumns(Table.FirstN(Sales, 3), {"Product", "UnitPrice", "Quantity"})
in
Table.UnpivotOtherColumns(Source, {"Product"}, "Measure", "Value")Product | Measure | Value | |
|---|---|---|---|
| 1 | Widget A | UnitPrice | 25 |
| 2 | Widget A | Quantity | 4 |
| 3 | Gadget B | UnitPrice | 50 |
| 4 | Gadget B | Quantity | 2 |
| 5 | Widget C | UnitPrice | 15 |
| 6 | Widget C | Quantity | 10 |