Pivoting columns in Power BI - Step-by-Step Guide

Introduction

Pivoting columns in Power BI helps you turn row data into columns. This is useful when you want to reshape your data to see categories as separate columns instead of rows.

When you have sales data by product and date, and you want each date to be a separate column.

When survey responses are listed in rows but you want each question as a column.

When you want to compare monthly expenses side by side as columns instead of rows.

When you need to create a summary table that shows categories horizontally.

When your data has repeated values in one column that you want to spread across multiple columns.

Steps

Step 1: Open your dataset in Power Query Editor

- Home tab > Transform data

The Power Query Editor window opens showing your data table

Step 2: Select the column you want to pivot

- Data preview pane

The selected column is highlighted

Step 3: Click the Pivot Column button

- Transform tab > Pivot Column

A Pivot Column dialog box appears

Step 4: Choose the values column for the new columns

- Pivot Column dialog > Values Column dropdown

The values column is selected for aggregation

Step 5: Select the aggregation function (e.g., Don't Aggregate or Sum)

- Pivot Column dialog > Advanced options > Aggregate Value Function

The aggregation method is set for pivoting

Step 6: Click OK

- Pivot Column dialog

The selected column is pivoted into multiple columns based on unique values

Step 7: Click Close & Apply

- Home tab in Power Query Editor

The transformed data loads back into Power BI with pivoted columns

Before vs After

Before

Data has a 'Month' column with values Jan, Feb, Mar repeated in rows and a 'Sales' column with numbers

After

Data shows separate columns for Jan, Feb, Mar with sales numbers filled under each month column

Settings Reference

Values Column

📍 Pivot Column dialog box

Specifies which column's values fill the new pivoted columns

Default: None selected

Aggregate Value Function

📍 Pivot Column dialog > Advanced options

Determines how to combine multiple values when pivoting

Default: Don't Aggregate

Common Mistakes

Not selecting the correct values column before pivoting

Without selecting the values column, Power BI cannot fill the new columns with data

Always choose the column that contains the data you want to spread across new columns

Using aggregation when you want exact values

Aggregation can combine multiple rows and change the original data meaning

Choose 'Don't Aggregate' if you want to keep original values without summarizing

Summary

Pivoting columns reshapes data by turning unique row values into columns.

You must select the column to pivot and the values column to fill new columns.

Choose aggregation carefully to avoid unwanted data changes.