Expression.Error: Multiple matches found in a Join (Cartesian product trap).

In Power Query, Expression.Error: Multiple matches found in a Join occurs during a relational merge when the join algorithm expects a strict 1-to-1 or unique-key relationship, but encounters duplicate occurrences of the lookup key in the lookup table. More broadly, unexpected duplicates during standard joins trigger an uncontrolled row explosion known as a Cartesian product.

Quick Answer

Open the lookup table used in your merge, select the key column(s), and apply Remove Duplicates (or Group By) before executing the merge. Power Query joins match every duplicate occurrence in both tables. If a key appears multiple times in both sources, the row count multiplies uncontrollably, generating errors or massive performance slowdowns.

---

The Cartesian Product Trap Explained

When merging two tables in Power Query (via Home > Merge Queries or Table.NestedJoin), the engine evaluates the matching key values between Table A and Table B.

Understanding the math behind row generation is critical:

• If key 1001 appears 1 time in Table A and 1 time in Table B: the result contains $1 \times 1 = 1$ row.
• If key 1001 appears 5 times in Table A and 1 time in Table B (a typical 1-to-Many join): the result contains $5$$\times$ $1 = 5$ rows.
• If key 1001 appears 5 times in Table A and 5 times in Table B (an unintended Many-to-Many join): the result produces $5$$5 = 25$ rows.

Table A (5 rows with Key 1001)  \
                                  ==> Merged Output: 25 Rows!
Table B (5 rows with Key 1001)  /

When unexpected duplicates exist in both tables across thousands of keys, a table of 10,000 rows can balloon into tens of millions of rows in seconds. This either triggers strict join integrity errors or exhausts memory, resulting in Handling “Expression.Error: Evaluation ran out of memory.”

---

Step-by-Step Fixes to Neutralize Duplicate Join Keys

1. Deduplicate the Dimension/Lookup Table

If the table you are joining against is meant to be a master catalog, dimension table, or reference list, its join key must be strictly unique.

1. In the Power Query editor, select the query that serves as your lookup reference.
2. Select the key column (e.g., ProductID, CustomerCode, or Account#).
3. If the unique key is composite (spans multiple columns), hold Ctrl and select all columns that form the composite key.
4. Right-click the column header and select Remove Duplicates.
5. Return to your primary fact query and refresh the merge step.

2. Pre-Aggregate the Lookup Table Before Merging

If the lookup table naturally contains multiple entries per key (such as multiple payments for a single invoice), you cannot simply drop rows without losing critical business numbers. You must aggregate the details first.

1. Select the detail table in the left panel.
2. Go to the Transform tab and select Group By.
3. Group by your join key (e.g., InvoiceID).
4. Add an operation:
   • New column name: TotalAmount
   • Operation: Sum
   • Column: PaymentAmount
5. Click OK.
6. Because each InvoiceID now appears on exactly one row with its grand total, merging it into your header table will not multiply rows.

3. Audit for Hidden Spaces and Case Differences Creating False Duplicates

Often, analysts believe their keys are unique, but trailing spaces or differing text cases prevent standard grouping, causing dirty duplicates to enter downstream joins:

• "A100" and "A100 " are treated as distinct values during deduplication, but certain database backends or external systems collapse them during join evaluation.
• Fix: Prior to deduplicating or merging, select your key columns, go to the Transform tab, and run Format > Trim, followed by Format > Clean, and ensure consistent casing using Format > Uppercase.

For comprehensive techniques on scrubbing hidden spacing bugs prior to lookups, see #N/A in VLOOKUP: The “Trailing Space” Trap.

---

Verification and Sanity Checking

After configuring any merge:

1. Note the total row count of your primary table before the merge step.
2. Step through to the expanded table step.
3. Check the row count at the bottom-left corner of the Power Query window.
4. If a standard Left Outer lookup join increased your primary row count, unexpected duplicates exist in your secondary table. Locate and clean those duplicate keys immediately before loading the data into your model.