How to Unpivot Columns in Power Query for Power BI

In Power BI's Power Query, select the columns you want to unpivot, then right-click and choose Unpivot Columns. This converts selected columns into attribute-value pairs, making data easier to analyze and visualize.

Syntax

The basic syntax to unpivot columns in Power Query uses the Table.UnpivotColumns function. It requires the table, a list of columns to unpivot, and names for the new attribute and value columns.

• table: The source table to transform.
• columns: List of column names to unpivot.
• attributeColumnName: Name for the new column holding original column headers.
• valueColumnName: Name for the new column holding the values.

powerquery

Table.UnpivotColumns(table as table, columns as list, attributeColumnName as text, valueColumnName as text) as table

Example

This example shows how to unpivot the columns Q1 and Q2 in a sales table to get a normalized list of sales by quarter.

powerquery

let
    Source = Table.FromRecords({
        [Product="A", Q1=100, Q2=150],
        [Product="B", Q1=200, Q2=250]
    }),
    Unpivoted = Table.UnpivotColumns(Source, {"Q1", "Q2"}, "Quarter", "Sales")
in
    Unpivoted

Output

Product | Quarter | Sales
--------|---------|------
A       | Q1      | 100
A       | Q2      | 150
B       | Q1      | 200
B       | Q2      | 250

Common Pitfalls

Common mistakes when unpivoting columns include:

• Not selecting the correct columns to unpivot, which can lead to losing important data.
• Unpivoting all columns including identifiers, which breaks the data structure.
• Forgetting to rename the new attribute and value columns, causing confusion in reports.

Always select only the columns that represent values to unpivot, and keep identifiers like IDs or names untouched.

powerquery

/* Wrong: Unpivoting all columns including Product (identifier) */
let
    Source = Table.FromRecords({
        [Product="A", Q1=100, Q2=150],
        [Product="B", Q1=200, Q2=250]
    }),
    WrongUnpivot = Table.UnpivotColumns(Source, {"Product", "Q1", "Q2"}, "Attribute", "Value")
in
    WrongUnpivot

/* Right: Unpivot only Q1 and Q2 columns */
let
    Source = Table.FromRecords({
        [Product="A", Q1=100, Q2=150],
        [Product="B", Q1=200, Q2=250]
    }),
    CorrectUnpivot = Table.UnpivotColumns(Source, {"Q1", "Q2"}, "Quarter", "Sales")
in
    CorrectUnpivot

Quick Reference

Tips for unpivoting columns in Power Query:

• Select only value columns to unpivot, keep identifiers intact.
• Use Unpivot Columns from the right-click menu or the Transform tab.
• Rename the new columns to meaningful names like Attribute and Value.
• Preview the data after unpivoting to ensure it matches your expected format.

Key Takeaways

Select only the columns with values to unpivot, not identifiers.

Use the right-click menu or Table.UnpivotColumns function in Power Query.

Rename the new attribute and value columns for clarity.

Unpivoting converts wide data into a normalized, long format for easier analysis.

Always preview your data after unpivoting to confirm correctness.