Transform Vertical Rows into Horizontal Columns with Power Query in Excel
Question details
The user needs to reshape a two-column source table so that each unique Number appears once, and its related Value entries are distributed across multiple horizontal columns.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- The user has repeated numbers and associated values stored vertically and wants a one-row-per-number layout for easier review or reporting.
- Observed behavior
- The source data currently returns each number-value pair on its own vertical row; the expected output is one row per Number with values spread into side-by-side columns.
Make sure the source range has clear headers such as Number and Value and is formatted as an Excel table before you open Power Query. The Power Query steps are completed in Microsoft Excel because WPS Office does not include the Power Query Editor.
Group by Number and expand the Value lists into columns with Power Query
Use this refreshable Power Query method when you have repeated numbers in rows and need one row per number with the related values spread across multiple columns.
Power Query groups the source rows by the Number column, keeps the related values in a list, and then converts that list into separate columns. This method stays linked to the source table, so new rows can be refreshed without rebuilding the transformation.
Click any cell inside the source data in Excel, go to Data > From Table/Range, confirm that the range has headers such as Number and Value, and click OK.
In the Power Query Editor, select the Number column, go to Home > Group By, set Group by to Number, enter New column name MyTable, choose Operation All Rows, and click OK.
Go to Add Column > Custom Column, enter New column name Values, type =Table.Column([MyTable],"Value") in the formula box, and click OK.
Click the expand icon on the Values column, choose Extract Values, select a delimiter such as semicolon, and click OK. With the Values column still selected, go to Split Column > By Delimiter, choose Semicolon, and set Split into to Columns.
Select the MyTable column, press Delete or use Home > Remove Columns, rename the split columns as needed, and click Close & Load to load the transformed table back into Excel.
Open and share the transformed workbook in WPS Office
Power Query is a Microsoft Excel feature; after you create the horizontal layout in Excel, WPS Office is a free, lightweight alternative for opening, editing, and sharing the .xlsx result. It offers Excel format compatibility, a familiar ribbon, and seamless migration from Microsoft Office.
- 1. Download WPS Office: Go to the WPS Office download page and install WPS Spreadsheets.
- 2. Open the transformed workbook: Double-click the .xlsx file, or use Menu > Open in WPS Spreadsheets.
- 3. Edit and save compatibly: Make edits in the worksheet and save as .xlsx to retain Excel compatibility.
Frequently Asked Questions
Can I perform Power Query transformation directly in WPS Office Spreadsheets?
Power Query Editor is a Microsoft Excel feature and is not available in WPS Office. Complete the query in Excel, then open the resulting .xlsx workbook in WPS Spreadsheets to view, edit, and save it.
Why does expanding the Value column create multiple rows instead of horizontal columns?
If you choose Expand to New Rows, Power Query creates a new row for each list item. For horizontal columns, use Extract Values with a delimiter first, then use Split Column > By Delimiter and set Split into to Columns.
What should I do if my Value lists have different numbers of entries?
Power Query creates as many columns as the longest list. Rows with fewer values show null in the extra columns, which you can filter or replace in the Power Query Editor before loading.
Can the horizontal layout refresh when source rows are added?
Yes. In Excel, keep the source data in an Excel table, refresh the Power Query with Data > Refresh All, then reopen or refresh the workbook in WPS Spreadsheets to see the updated result.
