Power Query Problems

How to Convert Excel Job and Rate Pairs into Separate Rows

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to restructure spreadsheet data where a single row contains a site, project, and multiple horizontal job-rate pairs into a format where each job and rate pair has its own separate row, while keeping the associated project information intact.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Reorganizing complex horizontal data pairs into vertical rows to facilitate easier data analysis, reporting, and pivot table creation.
Observed behavior: Currently, the job and rate data pairs are spread horizontally across multiple columns in a single row, making it difficult to process or aggregate.

Before you start

Ensure your dataset is formatted as an Excel Table (Ctrl+T) before importing it into Power Query, and verify there are no completely blank rows interrupting your data range.

Solution 1Recommended

Use Power Query to Split and Expand Data

Power Query is the most robust method for this task, allowing you to convert the horizontal pairs into records and expand them into new rows dynamically.

This method uses Power Query's Advanced Editor to apply a custom M code script that splits the paired columns and expands them vertically.

Import Data to Power Query

Select your data range, go to the 'Data' tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.

Remove Unnecessary Columns

Right-click the 'Site' column header (and any other columns you do not need) and select 'Remove' to keep only the Project and the job-rate pairs.

Apply Custom M Code

Click 'Advanced Editor' on the Home tab. Replace the existing code with the custom script to group the pairs: let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], Removed = Table.RemoveColumns(Source,{"Site"}), Added = Table.AddColumn(Removed,"Jobs",each Table.FromRows(List.Split(List.RemoveNulls(List.Skip(Record.ToList(_),1)),2),{"Job","Rate"})) in Added.

Select and Expand Columns

Add a step to select the required columns: Table.SelectColumns(Added,{"Project","Jobs"}). Then, click the expand icon on the new 'Jobs' column to extract 'Job' and 'Rate' into separate columns.

Load the Transformed Data

Click 'Close & Load' on the Home tab to output the newly organized vertical data into a new Excel worksheet.

Automation Advantage: Once this Power Query is set up, you can simply click 'Refresh All' on the Data tab whenever you add new data to the original table.

Solution 2

Use Dynamic Array Formulas (Excel 365)

If you are using Excel 365 and prefer a formula-based approach without Power Query, you can use a combination of TOCOL, WRAPROWS, FILTER, and UNIQUE.

Advanced Data Formatting in WPS

Reshape Complex Data Easily with WPS Spreadsheet

WPS Spreadsheet provides powerful data manipulation tools, including support for dynamic array functions, allowing you to transform complex horizontal data into clean vertical rows effortlessly.

1. Open Your Dataset: Launch WPS Spreadsheet and open your existing workbook containing the horizontal data pairs.
2. Apply Array Formulas: Select the destination cell and utilize advanced functions to filter and wrap your data rows precisely as you would in standard spreadsheet environments.
3. Organize and Format: Use WPS Spreadsheet's built-in formatting tools to present your newly separated rows cleanly for reporting or pivot table generation.

Fully compatible with Microsoft Excel file formats (.xlsx)Supports advanced array formulas for complex data extractionFree and lightweight alternative to Microsoft OfficeFamiliar user interface requires no learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Can I use the Text-to-Columns feature to separate these pairs?

No, Text-to-Columns is designed to split delimited data within a single cell across multiple columns. It cannot take multiple columns and transpose them into new vertical rows.

Why am I getting a #SPILL! error when using the TOCOL formula?

A #SPILL! error occurs when there is existing data blocking the dynamic array formula from expanding. Ensure the cells below and to the right of your formula are completely empty.

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

If you use dynamic array formulas, the results will update automatically provided the new data falls within the referenced range. If you use Power Query, you must right-click the output table and select 'Refresh' to see the updates.

Excel & Data Problems