Table.UnpivotOtherColumns Function in Power Query

The Table.UnpivotOtherColumns function translates all columns other than a specified set into attribute-value pairs, combined with the rest of the values in each row.

Syntax

Table.UnpivotOtherColumns(
    table as table, 
    pivotColumns as list, 
    attributeColumn as text, 
    valueColumn as text
) as table

The function has the following parameters:

table: The input table to unpivot.
pivotColumns: A list of column names to keep as-is (not unpivoted).
attributeColumn: Specifies the name of the new column that will contain the names of the unpivoted columns.
valueColumn: Specifies the name of the new column that will contain the values from the unpivoted columns.

Example: Create a table from records.

Power Query M

let
    MyTable = Table.FromRecords(
        {
            [EmployeeID = 1, Name = "Ashish", Science_Marks = 56, Math_Marks=65],
            [EmployeeID = 2, Name = "Katrina", Science_Marks = 80, Math_Marks=65],
            [EmployeeID = 3, Name = "Alia", Science_Marks = 38, Math_Marks=65],
            [EmployeeID = 4, Name = "Vicky", Science_Marks = 58, Math_Marks=60],
            [EmployeeID = 5, Name = "Mohini", Science_Marks = 28, Math_Marks=65],
            [EmployeeID = 6, Name = "Meenakshi", Science_Marks = 29, Math_Marks=65],
            [EmployeeID = 7, Name = "Esha", Science_Marks = 38, Math_Marks=65],
            [EmployeeID = 8, Name = "Anjali", Science_Marks = 38, Math_Marks=38]
        }
    )
in
    MyTable

The output of the above code is shown below:

Table.UnpivotOtherColumns in Power Query

Example: Unpivot all columns except specified ones into attribute-value pairs, combined with the rest of the values in each row.

Power Query M

let
    MyTable = Table.FromRecords(
        {
            [EmployeeID = 1, Name = "Ashish", Science_Marks = 56, Math_Marks=65],
            [EmployeeID = 2, Name = "Katrina", Science_Marks = 80, Math_Marks=65],
            [EmployeeID = 3, Name = "Alia", Science_Marks = 38, Math_Marks=65],
            [EmployeeID = 4, Name = "Vicky", Science_Marks = 58, Math_Marks=60],
            [EmployeeID = 5, Name = "Mohini", Science_Marks = 28, Math_Marks=65],
            [EmployeeID = 6, Name = "Meenakshi", Science_Marks = 29, Math_Marks=65],
            [EmployeeID = 7, Name = "Esha", Science_Marks = 38, Math_Marks=65],
            [EmployeeID = 8, Name = "Anjali", Science_Marks = 38, Math_Marks=38]
        }
    ),
    result = Table.UnpivotOtherColumns(MyTable, {"EmployeeID", "Name"}, "Attribute", "Value")
in
    result

The output of the above code is shown below:

Previous Next