Power Query Problems

Split Address Rows into a Column in Excel Power Query

Jason MillerJason Miller Nov 18, 2026 868 views

Question details

Move address rows into a separate column beside their corresponding company, facility, and reference fields.

Split Address Rows into a Column in Excel Power Query
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the query

In Excel, select Data > Queries & Connections, then double-click the imported query to open Power Query Editor.

2
Add an index

Select Add Column > Index Column > From 0. Confirm that the first company row has index 0 and its address row has index 1.

3
Identify pairs and row types

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).

4
Create the address query

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.

5
Filter the company rows

Return to the original query and filter RowType to 0. Keep RecordID and the company, facility, and reference columns needed in the result.

6
Merge and load 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.

Merge Paired Rows Using an Index in Power Query
Check the row pattern: Missing address rows, extra blank rows, or records with different numbers of rows can cause incorrect matches. Review the data pattern before using this method.
Free Microsoft Office alternative

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.

Review company and address columns in a familiar spreadsheet interfaceOpen and edit Microsoft Excel XLSX filesUse a free, lightweight office suiteMove existing spreadsheet files into WPS for everyday editing
microsoft office alternative - wps office

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.