Power Query Problems

How to Merge Parent and Child Excel Records by ID with Power Query

WPS Editor Oct 1, 2026 868 views

Question details

The user needs to combine two large Excel datasets of parent and child accounts linked by a parent ID, placing each child record on a separate row under its matching parent.

How to Merge Parent and Child Excel Records by ID with Power Query

Product: Excel
Device & OS: not provided
Scenario: Organizing and merging large parent-child datasets (up to 52,000 accounts) to structure child records accurately under their respective parent records.
Observed behavior: The user wants to align two datasets so that multiple child records are grouped correctly and expanded into distinct rows tied to the correct parent ID.

Before you start

Ensure both your parent and child datasets are formatted as Excel Tables (press Ctrl + T) and verify that the Parent ID column in both tables is set to the exact same data type (e.g., both Text or both Whole Number) to prevent merge errors.

Solution 1Recommended

Use Power Query to Merge and Expand Records

Import both datasets into Power Query and use a Left Outer Join to merge them by the Parent ID. This ensures all parent accounts are retained while cleanly expanding the associated child records into new rows.

Power Query is the most efficient tool for handling large datasets (like your 52,000 accounts) without slowing down Excel. By utilizing a Left Outer Join, you guarantee that no parent records are lost, even if they currently lack associated child accounts.

Import Datasets into Power Query

Open Excel, click on the 'Data' tab on the ribbon, and select 'Get Data' > 'From Table/Range' for your Parent dataset. Once the Power Query Editor opens, click 'Close & Load To...' > 'Only Create Connection'. Repeat this process for your Child dataset.

Initiate the Merge

In the 'Data' tab, select 'Get Data' > 'Combine Queries' > 'Merge'. The Merge dialog box will appear.

Configure the Left Outer Join

In the Merge dialog, choose your Parent query from the top dropdown and your Child query from the bottom dropdown. Click on the 'Parent ID' column in both preview windows to establish the link. Under 'Join Kind', select 'Left Outer (all from first, matching from second)', then click 'OK'.

Expand the Child Records

In the Power Query Editor, locate the new merged column at the far right of your Parent query. Click the expand icon (two diverging arrows) in the column header. Select the specific child data columns you want to display, uncheck 'Use original column name as prefix', and click 'OK'.

Load the Merged Data

Review your combined dataset. Multiple child records will now appear on separate rows alongside their parent data. Click 'Close & Load' on the Home tab to output the final merged dataset into a new Excel worksheet.

Use Power Query to Merge and Expand Records

Tip for Testing: If you are working with live data, consider testing the merge steps on sample files with fictional data first. This helps you refine the query and select the right columns before processing the full 52,000-account list.

Free Microsoft Office alternative

Try WPS Office for Fast and Reliable Data Management

Handling massive datasets with tens of thousands of rows requires a fast, stable, and highly efficient spreadsheet tool. WPS Office provides a lightweight and powerful alternative to Microsoft Office, offering seamless compatibility with all your existing Excel files and robust data organization tools without the heavy subscription fees.

1. Download and Install: Visit the official WPS Office website to download the free installer. The lightweight installation takes only minutes to complete.
2. Open Your Existing Spreadsheets: Launch WPS Spreadsheet and open your parent and child datasets directly. Your formatting and table structures will remain intact.
3. Organize Your Accounts: Utilize powerful built-in functions like XLOOKUP or data consolidation tools to securely manage your 52,000+ accounts without software crashes.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Lightweight architecture ensures fast loading and smooth scrolling, even with massive datasets.Familiar tabbed interface allows for an immediate, seamless migration.Completely free to download and use for your daily spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why are my parent rows duplicating after the merge?

When you expand child records in Power Query, it automatically duplicates the parent row information for each corresponding child record. This is the expected and correct behavior for displaying a one-to-many relationship in a flat tabular format.

What happens if a parent ID does not have any child records?

Because you selected a Left Outer Join, parent IDs without matching child records will still appear in your final dataset. The expanded child columns for those specific rows will simply display as 'null' (blank) values.

Why is my merge missing some parent IDs even though they exist?

This usually occurs due to data type mismatches (e.g., numbers stored as text in one table and numbers in the other) or hidden trailing spaces. Ensure both ID columns are trimmed and explicitly set to the same data type before initiating the merge.

Can Power Query handle datasets larger than 52,000 rows without crashing?

Yes, Power Query is highly optimized for large datasets and can process millions of rows. It processes the data in the background, minimizing memory usage compared to relying on standard, resource-heavy spreadsheet formulas like VLOOKUP.

Excel & Data Problems