Table.UnpivotOtherColumns is a Power Query M function that translates all columns other than a specified set into attribute-value pairs, combined with the rest of the values in each row. The function returns a table with the non-specified columns unpivoted into attribute-value pairs.
Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365
Syntax
Table.UnpivotOtherColumns(
table as table,
pivotColumns as list,
attributeColumn as text,
valueColumn as text,
) as tableDescription
Translates all columns other than a specified set into attribute-value pairs, combined with the rest of the values in each row.
Examples
Translates all columns other than a specified set into attribute-value pairs, combined with the rest of the values in each row.
Table.UnpivotOtherColumns(
Table.FromRecords( {
[key = "key1", attribute1 = 1, attribute2 = 2, attribute3 = 3],
[key = "key2", attribute1 = 4, attribute2 = 5, attribute3 = 6]
} ),
{"key"},
"column1",
"column2"
)
/* Output:
Table.FromRecords( {
[key = "key1", column1 = "attribute1", column2 = 1],
[key = "key1", column1 = "attribute2", column2 = 2],
[key = "key1", column1 = "attribute3", column2 = 3],
[key = "key2", column1 = "attribute1", column2 = 4],
[key = "key2", column1 = "attribute2", column2 = 5],
[key = "key2", column1 = "attribute3", column2 = 6]
} )
*/
Related articles
Learn more about Table.UnpivotOtherColumns 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.UnpivotOtherColumns are:
