How to How to Unpivot Columns in Power Query in Excel in Excel - Practical Guide

Learn how to unpivot columns in Power Query to transform wide data formats into long formats for better analysis. This essential skill converts multiple column headers into row values, making data analysis and visualization significantly easier.

Why This Matters

Unpivoting is critical for preparing messy data for analysis, enabling proper aggregation, filtering, and visualization in dashboards and reports.

Prerequisites

•Excel 365 or Excel with Power Query installed
•Basic understanding of Power Query Editor interface
•A dataset with multiple column headers representing values

Step-by-Step Instructions

Load your data into Power Query

Open Excel, go to Data > From Table/Range or From File, select your data source, and click Load to open the Power Query Editor.

Select columns to unpivot

In Power Query Editor, select the columns you want to unpivot by clicking the column header while holding Ctrl for multiple selections.

Access the Unpivot function

Right-click on the selected columns and choose Unpivot Columns, or go to Transform > Unpivot Columns from the ribbon menu.

Review the unpivoted structure

Verify that your data has been transformed with a new Attribute column (original headers) and Value column (original values).

Load the transformed data to Excel

Click Home > Close & Load to import the unpivoted data back into your Excel worksheet.

Alternative Methods

Use Unpivot Other Columns

Select the columns you want to keep as identifiers, right-click and choose Unpivot Other Columns to automatically unpivot all remaining columns.

Manual column selection via Transform menu

Access Transform > Unpivot Columns directly from the ribbon without right-clicking for a streamlined workflow.

Tips & Tricks

✓Always keep identifier columns (like dates or product names) unselected when unpivoting to maintain data integrity.
✓Use Unpivot Other Columns when you have many columns to unpivot and only a few identifier columns.
✓Rename the Attribute and Value columns after unpivoting to reflect your actual data content.

Pro Tips

★Combine unpivot with grouping operations to aggregate unpivoted data by specific dimensions before loading.
★Use the Data Type feature to ensure columns are properly typed after unpivoting to prevent calculation errors.
★Save your Power Query as a reusable template for recurring unpivot operations on similar datasets.

Troubleshooting

Unpivot option is grayed out

Ensure you have selected at least one column before accessing the unpivot function. Right-click on column headers, not on cells.

Data appears as text instead of numbers

Go to Transform > Data Type and change the Value column to appropriate type (Decimal, Integer) after unpivoting.

Identifier columns were unpivoted by mistake

Use Undo (Ctrl+Z) immediately, reselect only the data columns, and retry the unpivot operation.

Related Excel Formulas

TRANSPOSE UNPIVOT (DAX)INDEX MATCH

Frequently Asked Questions

What's the difference between Unpivot Columns and Unpivot Other Columns?

Unpivot Columns unpivots only the selected columns, while Unpivot Other Columns unpivots all columns except the selected ones. Use Unpivot Other Columns when you want to keep identifier columns and unpivot the rest automatically.

Can I unpivot data without Power Query?

You can manually reorganize data using formulas or pivot tables in reverse, but Power Query is the most efficient and scalable method for unpivoting operations.

Will unpivoting increase my file size?

Unpivoting typically increases row count while reducing column count, which may slightly increase file size depending on your data volume and how Power Query compresses the model.

How do I unpivot multiple sets of columns?

Unpivot one set at a time or use Unpivot Other Columns strategically to handle multiple pivoted datasets in a single operation.