Split Address Rows into a Column in Excel Power Query
Question details
Move address rows into a separate column beside their corresponding company, facility, and reference fields.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- Imported data contains a company record followed by a separate row containing its address.
- Observed behavior
- The desired result is one row per record, with the address in its own column.
In Power Query Editor, check that each record consists of exactly two consecutive rows: a company row followed by its address row. Identify the source column containing the address and remove any extra rows that would disrupt the pattern before adding an index.
Merge Paired Rows Using an Index in Power Query
Use this method when every company row is immediately followed by one address row.
The index assigns a shared record number to each pair of rows. Two copies of the query can then be filtered and merged to place the address beside the company details.
The example assumes the address appears in the Company column on the second row. Substitute the actual source column name if your data differs.
In Excel, select Data > Queries & Connections, then double-click the imported query to open Power Query Editor.
Select Add Column > Index Column > From 0. Confirm that the first company row has index 0 and its address row has index 1.
Select Add Column > Custom Column and create RecordID with the formula Number.IntegerDivide([Index], 2). Add another custom column named RowType with the formula Number.Mod([Index], 2).
Right-click the query in the Queries pane and select Duplicate. Rename the copy AddressRows, filter RowType to 1, and keep only RecordID and the column containing the address. Rename that source column Address.
Return to the original query and filter RowType to 0. Keep RecordID and the company, facility, and reference columns needed in the result.
Select Home > Merge Queries. Choose AddressRows as the second table, select RecordID in both tables, and use a Left Outer join. Expand the merged column and select Address, then select Home > Close & Load.

Review Address Tables with WPS Spreadsheets
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Excel format compatibility for an easy move between office suites. It can help you review and edit the finished address table, but it does not fix an Excel Power Query issue or replace the query steps above.

Frequently Asked Questions
Why do I need an index before merging the rows?
An index provides a predictable position for each row. Starting at 0 lets Number.IntegerDivide([Index], 2) assign the same RecordID to each company row and its following address row.
What if the address is not in the Company column?
In the AddressRows query, keep RecordID and the actual column containing the address, then rename that column Address before merging.
Will this method work if some records have no address row?
Not reliably. The two-row grouping depends on every record having a company row followed by an address row. Missing or extra rows can shift the pairings, so check the source pattern first.
How do I show the address after merging the queries?
In Power Query Editor, select the expand icon on the merged column, choose Address, and confirm. Then use Home > Close & Load to return the result to Excel.




