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

Introduction

Unpivoting columns changes data from a wide format to a long format. It helps when you have many columns that represent similar data types and want to analyze them as rows instead. This makes your data easier to work with in reports and visuals.

When your sales data has months as separate columns and you want one column for month and one for sales amount.

When survey responses are in multiple columns and you want to list all answers in one column for easier analysis.

When product categories are columns but you want to compare them side by side in a chart.

When you receive data exports with repeated measures across columns and need to normalize it for filtering.

When you want to create a timeline from columns representing different dates.

Steps

Step 1: Open

- Power Query Editor

The data preview window shows your table with all columns visible

Step 2: Select

- the columns you want to unpivot

Selected columns are highlighted in the table

Step 3: Right-click

- on one of the selected column headers

A context menu appears

Step 4: Click

- Unpivot Columns in the context menu

The selected columns transform into two columns: Attribute and Value

Step 5: Rename

- the new Attribute and Value columns

Columns have meaningful names for your data context

Step 6: Click

- Close & Apply on the Home tab

The transformed data loads back into Power BI for use in reports

Before vs After

Before

Table has columns: Product, Jan, Feb, Mar with sales numbers in each month column

After

Table has columns: Product, Month, Sales with one row per product per month

Settings Reference

Unpivot Columns

📍 Right-click menu on selected columns in Power Query Editor

Transforms selected columns from wide to long format

Default: Unpivot Columns

Rename Column

📍 Double-click column header in Power Query Editor

Gives meaningful names to unpivoted columns

Default: Attribute, Value

Common Mistakes

Unpivoting all columns including the identifier columns

This causes loss of key data like product names because they get unpivoted too

Select only the columns with values to unpivot, leaving identifier columns untouched

Not renaming the Attribute and Value columns after unpivot

Generic names make reports confusing and hard to understand

Rename columns to meaningful names like Month and Sales

Summary

Unpivoting changes columns into rows to simplify data analysis.

Use it when you have repeated measures across columns that should be in one column.

Always select only the columns to unpivot and rename the new columns for clarity.