Table.Unpivot - Table Function

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

The Table.Unpivot function in transforms selected columns in a table into rows, converting the column headers into a new (Attribute) column and the corresponding values into another (Value) column.

Examples

Let’s explore how the Table.Unpivot function works by examining a practical example.

The Scenario

Suppose you’re working with the following table:

Dataset for Table.Unpivot in Power Query M

This table has fields for the “Order Date” and “Country”, with different products listed in separate columns. However, for most data models, it’s preferable to have all products in a single column.

We want to restructure the data so that all product types (e.g., Product A, Product B, Product C) are combined into a single column, with their corresponding values in another column. Here’s what the desired result looks like:

Unpivoted dataset for Table.Unpivot in Power Query M

This is where the Table.Unpivot function comes into play. Unpivoting is the process of transforming columns into rows. Imagine the original table as a pivot table: the “Order Date” and “Country” fields are in the Rows section, while the products are in the Columns section. By “unpivoting,” we move the product columns to rows, which creates a more normalized table.

How to Unpivot the Table

To achieve this, you can apply these steps on the original table:

Select the Product Columns: Highlight the columns “Product A,” “Product B,” and “Product C.”
Unpivot the Columns: Right-click on one of the selected column headers and choose ‘Unpivot Selected Columns’ from the context menu.

This operation generates the following M code:

Table.Unpivot(
  Source , 
  { "Product A", "Product B", "Product C" }, 
  "Attribute", 
  "Value"
)

Here the:

First Argument (Source): Refers to the name of the table before the unpivot operation.
Second Second Argument: Lists the columns that will be transformed into rows.
Third Argument (Attribute): Specifies the name for the new column that will contain the original column headers (in this case, the product names).
Fourth Argument (Value): Names the column that will hold the values from the original product columns.

After applying the Table.Unpivot function, you will end up with the desired output table. As the output table shows, each product and its corresponding value now appears in separate rows, making the data structure more suitable for analysis.

Dynamic Unpivoting

Note that the above example hardcodes the column names to unpivot. For a more dynamic query, you may want to look into the Table.UnpivotOtherColumns function. This function would instead specify the columns to keep. All remaining columns (including any future additions) would then automatically unpivot.

You can try this example yourself by using:

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 }
        }
    ),
    Unpivot = Table.Unpivot( Source, {"Product A", "Product B", "Product C"}, "Attribute", "Value" )
in
    Unpivot

Learn more about Table.Unpivot 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.Unpivot are:

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