Power Query Problems

How to Merge Excel Datasets by Patient ID Without Duplicates Using Power Query

Camila Milosovich Oct 9, 2026 868 views

Question details

The user needs to combine patient data from two separate Excel datasets by matching a unique Patient ID, ensuring that only one row per patient is retained without generating duplicates.

How to Merge Excel Datasets by Patient ID Without Duplicate Rows

Product: Excel
Device & OS: not provided
Scenario: Combining a primary patient dataset with a secondary dataset (e.g., transplant information) that contains different columns, linking them via a shared Patient ID.
Observed behavior: When merging datasets, users often encounter unwanted duplicate rows if the secondary dataset has multiple entries for a single Patient ID. The goal is to perform a clean merge that retains the primary dataset's row count.

Before you start

Ensure that the 'Patient ID' column has the exact same data type (e.g., Text or Whole Number) and formatting in both Excel tables before starting the merge process, as mismatched types will prevent a successful match.

Solution 1Recommended

Merge Datasets Using Power Query Left Outer Join

Use Power Query's Merge Queries feature with a Left Outer join to combine columns while preserving the exact row count of your primary dataset.

A Left Outer join ensures that all rows from your first (primary) dataset are kept, and only matching data from the second dataset is appended. To prevent duplicate rows, it is crucial that the secondary dataset contains only unique Patient IDs.

Load Datasets into Power Query

Select any cell in your primary dataset, go to the 'Data' tab on the ribbon, and click 'From Table/Range'. Repeat this step for your secondary dataset so both are loaded into the Power Query Editor.

Initiate the Merge

In the Power Query Editor, navigate to the 'Home' tab and click on 'Merge Queries'. To create a new combined table instead of modifying the existing one, select 'Merge Queries as New'.

Match Patient IDs

In the Merge dialog, select your primary table from the top dropdown and your secondary table from the bottom dropdown. Click on the 'Patient ID' column header in both previews to establish the relationship.

Select the Join Kind

From the 'Join Kind' dropdown menu, choose 'Left Outer (all from first, matching from second)'. Click 'OK' to execute the merge.

Expand the Merged Data

In the new merged column, click the expand icon (two diverging arrows) in the column header. Select the specific fields you want to add (e.g., transplant information), uncheck 'Use original column name as prefix', and click 'OK'.

Merge Datasets Using Power Query Left Outer Join

Resolving Duplicate Row Issues: If you notice duplicate rows after expanding, it means your secondary dataset has multiple records for the same Patient ID. You must return to the secondary query, right-click the Patient ID column, and select 'Remove Duplicates' or use 'Group By' to aggregate the data before merging.

Efficient Data Merging in WPS Spreadsheet

Merge Datasets Seamlessly with WPS Office

If you want to avoid complex Power Query setups, WPS Spreadsheet offers powerful lookup functions like XLOOKUP and VLOOKUP. These functions allow you to instantly combine datasets by Patient ID, naturally pulling exactly one matching record and completely avoiding the duplicate row issues common in database joins.

1. Open Your Datasets: Launch WPS Spreadsheet and open the workbook containing your primary and secondary patient datasets.
2. Prepare the Target Column: In your primary dataset, click on the first empty cell in the row where you want the new data (e.g., Transplant Info) to appear.
3. Apply XLOOKUP Formula: Enter the formula: =XLOOKUP(A2, DatasetB!A:A, DatasetB!B:B) where A2 is the Patient ID, DatasetB!A:A is the ID column in the second sheet, and DatasetB!B:B is the data you want to retrieve.
4. Fill the Column: Press Enter to retrieve the data for the first patient, then double-click the fill handle in the bottom-right corner of the cell to apply the formula to all rows.

Easily combine datasets using XLOOKUP without generating duplicate rowsFully compatible with Microsoft Excel formulas and .xlsx file formatsLightweight, fast, and handles large patient datasets smoothlyFree to download and use for everyday data analysis and reporting

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting duplicate rows after merging in Power Query?

Duplicate rows occur during a Left Outer join if the secondary table contains multiple records for the same matching Patient ID. Power Query creates a new row for every match it finds. To fix this, ensure the secondary table has unique IDs before merging.

How can I remove duplicates from the secondary dataset before merging?

In the Power Query Editor, click on your secondary query in the left pane. Right-click the 'Patient ID' column header and choose 'Remove Duplicates'. This ensures only the first instance of each ID is kept, preventing duplication in the final merge.

What if the Patient IDs look the same but Power Query says there are no matches?

This usually happens due to mismatched data types (e.g., one is formatted as Number and the other as Text) or hidden spaces. Ensure both ID columns are formatted identically and consider using the 'Trim' function (Transform > Format > Trim) in Power Query to remove invisible trailing spaces.

Excel & Data Problems