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

Example:

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

Now let’s all columns other than a specified set 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