Power Query Problems

How to Transpose Excel Data into Rows Using Power Query

WPS Content Manager Oct 12, 2026 870 views

Question details

The user needs to transform a large Excel dataset by transposing it into rows, grouping by a unique identifier, and aligning related labels.

How to Transpose Excel Data into Rows Using Power Query

Product: Microsoft Excel
Device & OS: not provided
Scenario: Reshaping complex tabular data to group unique IDs (PERSONNUM) and pivot corresponding day values (DAYNUM) into separate columns.
Observed behavior: The data must be restructured so that DAYNUM values become column headers and the associated labels are aligned to the correct unique PERSONNUM rows.

Before you start

Ensure your raw data is formatted as an official Excel Table (press Ctrl+T) before importing it into Power Query, which will allow your query to update dynamically when new rows are added.

Solution 1Recommended

Use Power Query's Pivot Column Feature

This method uses the 'Pivot Column' function with the 'Don't Aggregate' advanced setting to transpose and restructure the data correctly.

Power Query handles massive datasets much better than traditional spreadsheet formulas. By pivoting a specific column and refusing to aggregate the results, you can transpose your category labels precisely where they belong.

Load data into Power Query

Select any cell inside your data table, navigate to the 'Data' tab on the Excel ribbon, and click 'From Table/Range' to launch the Power Query Editor.

Select the column to pivot

In the Power Query window, locate and click the header of the 'DAYNUM' column to select it.

Apply Pivot Column

Go to the 'Transform' tab at the top of the editor and click on 'Pivot Column'.

Configure advanced pivot settings

In the dialog box, choose your 'Label' column under the 'Values Column' dropdown. Then, click to expand 'Advanced options' and select 'Don't Aggregate' from the 'Aggregate Value Function' list. Click OK.

Close and Load

Navigate back to the 'Home' tab and click 'Close & Load'. This will output your newly transposed and aligned data into a fresh Excel worksheet.

Dynamic Updates: Once set up, anytime your original source data changes, simply right-click the green output table and select 'Refresh' to instantly update the transposed layout.

Solution 2

Alternative: Use Dynamic Array Formulas

If you prefer not to use Power Query, you can achieve a similar layout using Excel's dynamic array functions, provided you carefully manage your formula syntax.

Free Microsoft Office alternative

Simplify Your Data Processing with WPS Office

If complex Excel tools like Power Query feel overwhelming, try WPS Office. It provides a lightweight, highly compatible spreadsheet environment with robust Pivot Table features to organize and transpose your data without a steep learning curve.

1. Download and Install: Download WPS Office for free from the official website and install it on your computer.
2. Open your Excel File: Launch WPS Spreadsheet and open your existing .xlsx dataset seamlessly without losing formatting.
3. Utilize Built-in Data Tools: Navigate to the Data tab to access intuitive sorting, filtering, and Pivot Table options to manage your data effortlessly.

Seamless compatibility with Microsoft Excel (.xlsx, .xls) filesIntuitive Pivot Table features for easy data restructuringLightweight installation with lightning-fast performanceFree to download and use for all your daily data tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get errors when pivoting in Power Query?

Errors often occur if there are duplicate combinations of your grouping identifiers and pivot column values. Using 'Don't Aggregate' requires exactly one unique value per row/column intersection. Otherwise, you must choose an aggregation method like Sum, Max, or Count.

What does 'Don't Aggregate' mean in Power Query?

By default, when you pivot a column, Power Query attempts to calculate a mathematical summary (like a sum or count) for the values. Choosing 'Don't Aggregate' tells the tool to simply extract the exact text or raw value from your label column into the newly transposed layout.

Will my transposed data update automatically if I add new rows?

Yes, but it requires a manual trigger. Because Power Query maintains a secure connection to the source data, you can simply right-click anywhere inside your output table in Excel and select 'Refresh' to immediately update the layout with the latest data.

Excel & Data Problems