Table.UnpivotOtherColumns - Table Function

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 table

Description

The Table.UnpivotOtherColumns function transforms selected columns in a table into rows while leaving specified columns unchanged. It moves the unselected column headers into a new “Attribute” column and their corresponding values into a “Value” column.

Examples

Imagine you’re working with a table that looks like this:

Dataset for Table.Unpivot in Power Query M

In this table, you have columns for “Order Date” and “Country,” and the remaining columns represent different products. However, in most data models, it’s more useful to have all product names in a single column with their corresponding values in another column.

Here’s what that would look like:

Unpivoted dataset for Table.Unpivot in Power Query M

We want to transform the table so that the product names are combined into one column, and their values are placed in a separate column.

Unpivoting the Table

To do this, you’ll select the columns you want to keep as is—in this case “Order Date” and “Country”—and then unpivot the remaining columns. This means the function will move all unselected columns (the product columns in this case) into rows.

Here’s the code that does the job:

Table.UnpivotOtherColumns(
  Source, 
  {"Order Date", "Country"}, 
  "Attribute", 
  "Value"
)

What this says is:

Source: This is the original table.
{“Order Date”, “Country”}: These are the columns that will stay as they are.
Attribute: This is the name of the new column that will list the original column headers (e.g., “Product A”, “Product B”).
Value: This is the name of the new column that will contain the values from the original product columns. You can choose different names for these columns if you prefer.

Unpivoting All Columns

Notice that you don’t have to provide any columns to keep. In case you provide an empty list as second argument, the Table.UnpivotOtherColumns function will store all column names in the first column, and their corresponding value in the second. For the number of rows in your original table, the column names will be repeated.

For instance, the following code can be used to achieve that:

Table.UnpivotOtherColumns(
  Source, 
  {}, 
  "Attribute", 
  "Value"
)

The result of this operation is:

Table.UnpivotOtherColumns without specifying columns in Power Query M

Try it yourself

Here’s a sample code you can use to see how both examples work:

let
  Source = #table(
    type table[OrderDate = Date.Type, Country = Text.Type, Product A = Int64.Type, Product B = Int64.Type, Product C = Int64.Type],
      {
        {#date(2025, 1, 5), "Netherlands", 50, 50, 200},
        {#date(2025, 1, 6), "Denmark", 100, 75, 185}
      }
  ),
  UnpivotOtherColumns = Table.UnpivotOtherColumns( Source, { "OrderDate", "Country" }, "Attribute", "Value" ),
  UnpivotKeepNoColumns = Table.UnpivotOtherColumns( Source, {}, "Attribute", "Value")
in
  UnpivotKeepNoColumns

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

Other functions related to Table.UnpivotOtherColumns are:

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