Power Query Problems

How to Unpivot Excel Data by Year, Measure, and Quintile Using Power Query

Aamir Naveed Akram Sep 28, 2026 870 views

Question details

The user needs to convert a wide Excel table with measures, quintiles, and multiple yearly columns into a normalized, machine-readable format with one row per combination.

How to Unpivot Excel Data by Year, Measure, and Quintile Using Power Query

Product: Microsoft Excel
Device & OS: not provided
Scenario: Transforming wide datasets for data analysis, databases, or reporting by converting column headers into row values.
Observed behavior: The wide data needs to be reshaped into a normalized long format structure, resulting in a large combination of rows (e.g., 16,864 rows from 272 records and 62 years).

Before you start

Ensure your Excel data is formatted as a Table and does not contain merged cells or blank header rows before opening it in Power Query.

Solution 1Recommended

Use Unpivot Other Columns in Power Query

Select the fixed identifier columns (Measure and Quintile) and apply the 'Unpivot Other Columns' command to dynamically reshape the yearly data.

Using 'Unpivot Other Columns' is the most robust method for this transformation. It ensures that if new year columns are added to your dataset in the future, Power Query will automatically include them in the unpivot process without requiring you to update the query.

Load data into Power Query

Select any cell inside your data range in Excel. Navigate to the 'Data' tab on the ribbon and click 'From Table/Range' to open the Power Query Editor.

Select the identifier columns

In the Power Query Editor, locate the 'Measure' and 'Quintile' columns. Hold the Ctrl key on your keyboard and click both column headers to highlight them.

Unpivot the year columns

Right-click on either the 'Measure' or 'Quintile' column header and select 'Unpivot Other Columns' from the context menu. This action will collapse all the year columns into attribute-value pairs.

Rename the generated columns

Double-click the newly created 'Attribute' column header and rename it to 'Year'. Then, double-click the 'Value' column header and rename it to represent your specific metric.

Close and load the dataset

Go to the 'Home' tab and click 'Close & Load'. This will output your newly transformed, machine-readable data into a new Excel worksheet.

Use Unpivot Other Columns in Power Query

Dynamic Updates: When your original wide table is updated with new years, you can simply click 'Refresh All' on the Excel Data tab, and the unpivot rules will automatically apply.

Solution 2

Clean Headers and Map Generic Year Names

Remove unwanted human-readable header rows and fix generic column names (like Column1) before or after unpivoting.

Free Microsoft Office alternative

Enhance Your Data Analysis with WPS Office

While Power Query is a specific feature within Microsoft Excel, WPS Office provides a highly compatible, free, and lightweight alternative for your comprehensive spreadsheet and data modeling tasks.

Full compatibility with Microsoft Excel formats (.xlsx, .xls, .csv).Built-in Pivot Tables and advanced data analysis features for transforming datasets.Lightweight installation with fast processing, even on older devices.Familiar user interface requiring zero learning curve for seamless migration.

microsoft office alternative - wps office

Frequently Asked Questions

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

'Unpivot Columns' explicitly applies the transformation to only the columns you selected. 'Unpivot Other Columns' applies the transformation to all columns except the ones you selected, which makes it ideal for datasets where new columns (like future years) will be added over time.

Why do I get generic column names like 'Column1' when unpivoting?

This happens when Power Query does not recognize your first row as the header row. To fix this, use the 'Use First Row as Headers' command on the Home tab before you select your columns and unpivot.

How many rows should I expect after unpivoting my data?

The total number of resulting rows is calculated by multiplying the number of records by the number of unpivoted columns. For example, 272 records multiplied by 62 year columns will yield 16,864 rows.

Excel & Data Problems