Excel Power Query Merge Returns Fewer Rows Than Expected: Fix

When you merge two tables in Power Query, the result may contain fewer rows than the left table. This happens because the default join kind is a left outer join, which only keeps rows from the left table that match at least one row in the right table. If the right table has duplicate keys or missing matches, rows from the left table can be dropped or duplicated incorrectly. This article explains why row counts change during a merge and provides step-by-step fixes to preserve all expected rows.

Why Power Query Merge Returns Fewer Rows Than the Left Table

Power Query merge operations use join logic from relational databases. The default join kind is Left Outer, which keeps all rows from the left table but only includes rows from the right table that have a matching key. If a key in the left table does not exist in the right table, the left row still appears in the output with null values for the right table columns. That behavior does not reduce row count.

The row count decreases only when you use a join kind that filters out non-matching rows. The most common join kinds that reduce row count are:

Inner Join

An inner join keeps only rows where the key exists in both tables. Any left table row without a matching key in the right table is dropped. This is the most frequent cause of fewer rows.

Left Anti Join

A left anti join keeps only rows from the left table that have no match in the right table. This intentionally reduces row count to the set of unmatched rows.

Right Outer Join

A right outer join keeps all rows from the right table and only matching rows from the left. The result can have fewer rows than the left table if the right table is smaller.

Beyond join kind, two other factors cause unexpected row loss: duplicate keys in the right table and data type mismatches. When the right table has duplicate keys, a left outer join multiplies rows — each match adds a row, so the result can have more rows than the left table. But if you then apply a filter or remove duplicates after the merge, row count drops. Data type mismatches cause merge keys to never match, so an inner join returns zero rows while a left outer join returns all left rows with nulls.

Steps to Diagnose and Fix the Merge Row Count

Follow these steps in order. After each step, check the row count in the Power Query preview pane at the bottom left.

1. Verify the join kind in the Merge dialog
   Go to Home > Merge Queries. In the Merge dialog, look at the Join Kind dropdown. If it is set to Inner, Left Anti, or Right Outer, change it to Left Outer (the default). Left Outer preserves all left table rows. Click OK and check the row count.
2. Remove duplicate keys from the right table before merging
   Duplicate keys in the right table cause the merge to produce more rows than the left table. To fix this, select the right query in the Queries pane. Select the key column or columns. Go to Home > Remove Rows > Remove Duplicates. Now re-run the merge. The row count should match the left table.
3. Check for leading or trailing spaces in key columns
   In both tables, select the key column. Go to Transform > Format > Trim to remove leading and trailing spaces. Then go to Transform > Format > Clean to remove non-printable characters. Re-run the merge.
4. Ensure matching data types on key columns
   In both tables, select the key column. Look at the data type icon in the column header (123 for number, ABC for text, calendar for date). Both key columns must have the same data type. If they differ, click the data type icon and choose the matching type. Text and number types never match, even if the values look identical.
5. Use a Left Anti join to identify missing keys
   Create a new merge with the same two tables but set Join Kind to Left Anti. This returns only left table rows that have no match in the right table. Examine these rows to understand why the keys do not match — look for spaces, different casing, or missing values.
6. Check for filters applied after the merge step
   In the Applied Steps pane on the right, click each step after the merge. Look for steps named Filtered Rows or Removed Duplicates. If any filter step exists after the merge, it may be dropping rows. Delete or modify the filter step to restore the expected row count.

If Power Query Merge Still Returns Fewer Rows

Merge produces more rows than expected, not fewer

This is the opposite problem but often the root cause of row loss after further steps. If the right table has duplicate keys, the merge multiplies rows. You then might add a Remove Duplicates step to get back to the left table row count, but that can drop rows unintentionally. Instead, remove duplicates from the right table before the merge step.

Null values appear in merged columns for all rows

When every row in the merged result shows null values for right table columns, the join keys never matched. The most common cause is a data type mismatch — for example, one key column is Text and the other is Whole Number. Change both to the same type. Another cause is that the right table is empty. Check the row count of the right table in the Queries pane.

Row count changes after refreshing the query

If the source data changes between refreshes, the merge row count can change. For example, new rows added to the left table may have keys that do not exist in the right table. With a Left Outer join, those rows still appear. With an Inner join, they are dropped. To keep all left table rows, use Left Outer join. To diagnose, refresh the query and immediately check the row count in the preview pane before applying any further steps.

Left Outer Join vs Inner Join: Key Differences

Item	Left Outer Join	Inner Join
Rows from left table	All rows preserved	Only rows with a match in right table
Rows from right table	Only matching rows, nulls for non-matches	Only matching rows
Result row count vs left table	Equal or greater if right table has duplicates	Equal or fewer
Use case	Keep all left data, add related info from right	Keep only records that exist in both tables

Now you can identify why a Power Query merge returns fewer rows than expected. Start by checking the join kind — switch to Left Outer if you need all left table rows. Then remove duplicates from the right table and verify data types on the key columns. For persistent issues, use a Left Anti join to isolate unmatched rows. As an advanced tip, create a parameter that stores the join kind name and reference it in the merge dialog to quickly switch between join types during testing.