How to Convert Horizontal Excel Data to Vertical Format Using Power Query
Question details
The user needs to transform horizontally arranged data (scale and amount) into a vertical Excel table without manually copy-pasting each value.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Restructuring a wide, horizontal dataset with paired attributes into a vertical, database-friendly layout for better analysis.
- Observed behavior
- The current dataset is arranged horizontally, requiring an automated transformation process using Power Query to unpivot the data columns.
Ensure your source data does not contain entirely blank rows or columns, and properly label all column headers. Formatting your data range as an Excel Table (Ctrl+T) before importing is highly recommended.
Use Power Query to Unpivot and Merge Columns
This method uses Excel's Power Query (Get & Transform) to automatically unpivot paired data points, making the transformation seamless and repeatable.
Power Query allows you to flatten horizontal datasets. By unpivoting 'other' columns and using index columns to merge separated attributes (like Scale Quantity and Amount), you can accurately build a fully functional vertical dataset.
Select any cell in your source data range and press Ctrl+T to format it as a table. Once converted, go to the Data tab on the Excel ribbon and click 'From Table/Range' to open the Power Query Editor.
In the Power Query Editor, select the fixed columns you want to keep intact (e.g., ID, Material). Right-click the selected headers and choose 'Unpivot Other Columns' (or navigate to Transform > Unpivot Columns > Unpivot Other Columns).
Select the newly created 'Attribute' column. Go to Home > Split Column > By Delimiter, and choose to split by the rightmost space. Next, duplicate this entire query in the left pane so you have two identical queries. Rename one query to 'Scale' and the other to 'Amount'.
Filter the 'Scale' query so it only shows scale quantities, and filter the 'Amount' query to show only amounts. For both queries, navigate to Add Column > Index Column and select 'From 0'.
In the 'Scale' query, click Home > Merge Queries. Select the 'Amount' query from the dropdown and match them by clicking the Index columns in both previews. Expand the required value columns from the merged data, remove the Index column, set the data types (Whole Number for Scale, Currency for Amount), and click 'Close & Load' to export the vertical table to a new worksheet.
Try WPS Office for Your Spreadsheet and Data Processing Needs
While Power Query is a built-in feature of Microsoft Excel tailored for complex data modeling, WPS Office offers a highly compatible, lightweight, and completely free alternative for your everyday data processing and spreadsheet tasks. Enjoy a familiar interface and reliable analysis tools without expensive subscription fees.
Frequently Asked Questions
Can I convert horizontal data to vertical format without using Power Query?
Yes, you can use the built-in Transpose feature for simpler datasets. Select and copy your horizontal data, right-click the destination cell, select Paste Special, and check the 'Transpose' box. However, unlike Power Query, this method is manual and will not automatically update if the original data changes.
Why is an Index column required when merging queries?
Adding an Index column to both the split Scale and Amount queries provides a unique identifier for each row. This guarantees that when you merge the queries back together, the corresponding scales and amounts align perfectly on the same row without mixing up the data.
How do I update my vertical table if the original horizontal data changes?
Because Power Query maintains a live connection to your source table, you do not need to repeat the unpivot process. Simply navigate to the Data tab on your Excel ribbon and click 'Refresh All'. Your vertical table will automatically process and append the new horizontal data.
