Table.Unpivot is a Power Query M function that translates a set of columns in a table into attribute-value pairs, combined with the rest of the values in each row. The function returns a table with the specified columns unpivoted into attribute-value pairs.
Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365
Syntax
Table.Unpivot(
table as table,
pivotColumns as list,
attributeColumn as text,
valueColumn as text,
) as tableDescription
Translates a set of columns in a table into attribute-value pairs, combined with the rest of the values in each row.
Examples
Take the columns “a”, “b”, and “c” in the table ( {[ key = "x", a = 1, b = null, c = 3 ], [ key = "y", a = 2, b = 4, c = null ]} ) and unpivot them into attribute-value pairs.
Table.Unpivot(
Table.FromRecords( {
[key = "x", a = 1, b = null, c = 3],
[key = "y", a = 2, b = 4, c = null]
} ),
{"a", "b", "c"},
"attribute",
"value"
)
/* Output:
Table.FromRecords( {
[key = "x", attribute = "a", value = 1],
[key = "x", attribute = "c", value = 3],
[key = "y", attribute = "a", value = 2],
[key = "y", attribute = "b", value = 4]
} )
*/
Related articles
Learn more about Table.Unpivot in the following articles:
- Unpivot Columns And Keep Null Values in Power Query
Unpivoting columns in Power Query typically removes null values. Learn how to keep null values while unpivoting with this effective method! » Read more
Related functions
Other functions related to Table.Unpivot are:
