Create intermediate Works with: Any AI

Power Query Merge Two Tables Like a Lookup

Checklist Has example

• 📊 Excel
• 📈 Google Sheets

Merge two tables in Power Query to replace slow lookups, with join types explained in plain words, a match check and refresh instructions.

The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.

[VERIFY] is an editorial flag, not a blank to fill. Wherever the prompt shows [VERIFY], check that item against a reliable source before you use it.

Help me merge two tables in Power Query instead of using lookup formulas. Table 1: [TABLE_ONE_NAME] with columns [TABLE_ONE_COLUMNS]. Table 2: [TABLE_TWO_NAME] with columns [TABLE_TWO_COLUMNS]. The matching column in each: [KEY_COLUMNS]. What I want to end up with: [DESIRED_RESULT]. Known problems with the keys (leading zeros, case, duplicates, spaces): [KEY_ISSUES]. Excel version: [EXCEL_VERSION].

Produce:

1. Numbered steps with menu paths ([VERIFY] for my version): load both as queries, use Merge Queries, pick the join type, expand the columns I need, and load the result.

2. A table of join types with columns Join type, Rows it keeps, When I would use it, using my tables as the example.

3. A "check the match" section: how to find the rows that did not match (use a left anti join) and how to count them.

4. Key cleaning steps before the merge: trim, change case, fix data types, remove duplicates in the lookup table.

5. A comparison of Power Query merge versus XLOOKUP for my use case, in 4 or 5 lines.

Show me how the row count should change before and after as a sanity check. Do not guess my data; ask if the key is unclear. Do not use macros. Keep the explanation concrete, with my column names in each step.

Add a worked example with invented figures: 1,200 order rows merged with 85 customers, 12 orders with unmatched customers.

Fill in before use

Optional: type your details and the prompt above updates. Nothing is saved or sent.

Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.

Invented example
Table 1 (Orders): 1,200 rows. Table 2 (Customers): 85 rows. Key: CustomerID.

Join chosen: Left outer (keep every order, add customer details where found).
Expected row count after merge: 1,200. If it grows to 1,260, the Customers table has duplicate IDs (60 extra matches).

Unmatched check: a left anti join of Orders against Customers returned 12 rows, 1% of orders (12 / 1,200). Most share the ID format "c-0xx" instead of "C0xx", so trim and uppercase the key first, then refresh.

[VERIFY] menu names in your version.