Table.Unpivot

Updated on

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 table

Description

Table.Unpivot translates a set of columns in a table into attribute-value pairs. This transformation is 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]
} )
 */ 

Learn more about Table.Unpivot in the following articles:

Other functions related to Table.Unpivot are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-unpivot