How to Pivot Columns in Power Query in Power BI

In Power BI's Power Query, use the Pivot Column feature to turn unique values from one column into new columns. Select the column to pivot, then choose the values column to aggregate, and pick an aggregation method like Sum or Don't Aggregate.

Syntax

The Pivot Column operation in Power Query transforms rows into columns based on unique values in a selected column.

Key parts:

• Table.Pivot(table, pivotValues, pivotColumnName, valueColumnName, aggregationFunction)
• table: The source table to transform.
• pivotValues: List of unique values from the column to become new columns.
• pivotColumnName: The name of the column to pivot.
• valueColumnName: The column whose values fill the new pivoted columns.
• aggregationFunction: Function to aggregate multiple values (e.g., List.Sum).

powerquery

Table.Pivot(Source, List.Distinct(Source[Category]), "Category", "Value", List.Sum)

Example

This example pivots a table with sales data, turning product categories into columns showing total sales.

powerquery

let
    Source = Table.FromRecords({
        [Date = #date(2024,1,1), Category = "Fruit", Value = 10],
        [Date = #date(2024,1,1), Category = "Vegetable", Value = 5],
        [Date = #date(2024,1,2), Category = "Fruit", Value = 7],
        [Date = #date(2024,1,2), Category = "Vegetable", Value = 3]
    }),
    Pivoted = Table.Pivot(
        Source,
        List.Distinct(Source[Category]),
        "Category",
        "Value",
        List.Sum
    )
in
    Pivoted

Output

Date       | Fruit | Vegetable
2024-01-01 | 10    | 5
2024-01-02 | 7     | 3

Date	Fruit	Vegetable
2024-01-01	10	5
2024-01-02	7	3

Common Pitfalls

Common mistakes when pivoting columns in Power Query include:

• Not selecting the correct column to pivot, leading to unexpected results.
• Choosing an aggregation function when data does not need aggregation, causing data loss.
• Pivoting columns with many unique values, which can create too many columns and slow performance.

Always check if your data needs aggregation or if you can use Don't Aggregate option.

powerquery

/* Wrong: Pivoting without aggregation on multiple values causes error */
let
    Source = Table.FromRecords({
        [ID=1, Category="A", Value=10],
        [ID=1, Category="A", Value=20]
    }),
    Pivoted = Table.Pivot(Source, List.Distinct(Source[Category]), "Category", "Value")
in
    Pivoted

/* Right: Use aggregation to sum values */
let
    Source = Table.FromRecords({
        [ID=1, Category="A", Value=10],
        [ID=1, Category="A", Value=20]
    }),
    Pivoted = Table.Pivot(Source, List.Distinct(Source[Category]), "Category", "Value", List.Sum)
in
    Pivoted

Quick Reference

Tips for pivoting columns in Power Query:

• Use Pivot Column from the Transform tab for GUI-based pivoting.
• Choose the correct value column to fill new columns.
• Pick an aggregation function if multiple rows share the same pivot key.
• Use Table.Pivot in M code for advanced scenarios.

Key Takeaways

Use the Pivot Column feature in Power Query to turn unique values into new columns.

Always select the correct value column and aggregation method to avoid errors.

Pivoting large sets of unique values can slow down your query.

You can pivot columns via the UI or by writing M code with Table.Pivot.

Check your data for duplicates before pivoting to choose the right aggregation.