.svg){kind=small}
How to Merge Tables with Power Query
Introduction
When you work with data in Excel, you often have information split across different tables. For example:
- A Sales table with Order ID, Product, Quantity, and Amount
- A Customers table with Customer ID, Name, and Region
- A Products table with Product ID, Category, and Price
To build a useful report, you usually need to combine these tables. In old-school Excel, you might use VLOOKUP or XLOOKUP. That works, but it can get slow and messy when the data grows or changes.
Power Query gives you a cleaner approach. It lets you merge tables using a join (just like a database). You pick your matching columns, choose the type of join, and Power Query creates a new column you can expand to bring in the fields you need. Best of all, once the merge is set up, you can click Refresh and it will update automatically.
In this guide you’ll learn:
- What “merge” means in Power Query (and how it differs from “append”)
- How to prepare your tables so merges work first time
- The different join types (Left, Inner, Full, Anti joins) in plain English
- A step-by-step example you can follow
- How to fix common merge problems (nulls, duplicates, mismatched keys)
- Best practices to keep your queries fast and reliable
Merge vs Append (don’t mix these up)
Power Query has two main ways to combine tables:
Merge (join side-by-side)
- Adds new columns from a second table, based on matching keys
- Like XLOOKUP, but more powerful and repeatable
- Example: add Customer Name and Region to each Sales row
Append (stack on top)
- Adds more rows by stacking tables with the same columns
- Like combining January + February + March into one long list
This guide focuses on Merge.
What you need before you merge (the “keys”)
A merge works by matching one or more columns called a key. For example:
- Sales table has CustomerID
- Customers table also has CustomerID
Power Query matches them.
Good keys are:
- Consistent: same format in both tables
- Clean: no extra spaces, hidden characters, or mixed types
- Unique on the lookup table: ideally one row per key
If your key is not clean or not unique, the merge might still work, but the results can be confusing (duplicate matches, missing values, or unexpected row counts).
Step 1 — Load both tables into Power Query
You can merge data from lots of sources, but the simplest is two Excel tables.
If your data is on worksheets
- Select your first range and press Ctrl + T to make it an Excel table.
- Give it a clear name in Table Design > Table Name (e.g.,
tblSales). - Repeat for the second table (e.g.,
tblCustomers).
Now load each table into Power Query:
- Click inside
tblSales→ Data > From Table/Range. - In the Power Query Editor, choose Home > Close & Load To… → Only Create Connection.
- Repeat for
tblCustomers.
You now have two queries in the Queries pane, ready to merge.
Tip: “Only Create Connection” keeps your workbook tidy and avoids loading extra tables onto sheets.
Step 2 — Clean your keys (do this before merging)
This is the #1 reason merges fail. Clean keys in both tables.
A) Make sure the data type matches
Click the key column header (e.g., CustomerID).
- If it is numbers, set it to Whole Number.
- If it contains leading zeros (like 00123), set it to Text (otherwise Excel may drop the zeros).
B) Trim and clean text keys
If your key is text:
- Select the key column
- Transform > Format > Trim (removes extra spaces at the ends)
- Transform > Format > Clean (removes non-printing characters)
C) Standardise case (optional)
If keys are letters and case might differ:
- Transform > Format > Uppercase (or Lowercase)
D) Remove blanks
Filter the key column and remove null/blank rows if they should not exist.
Step 3 — Merge queries (the main workflow)
Now you’re ready to merge.
- In Power Query, click your main table query (e.g., Sales).
- Go to Home > Merge Queries (or Merge Queries as New).
- Merge Queries updates the current query
- Merge Queries as New creates a new query and keeps the originals unchanged
- In the merge window:
- Top table: Sales
- Bottom table: Customers
- Click the matching key column in Sales (CustomerID).
- Click the matching key column in Customers (CustomerID).
- Choose the Join kind (start with Left Outer for most lookup merges).
- Click OK.
Power Query creates a new column on your Sales table. It contains the matching Customer row as a small “table” inside each cell.
Step 4 — Expand the merged column
- In the new merged column header, click the expand icon (two arrows).
- Untick Use original column name as prefix if you want cleaner headings.
- Select the fields you want to bring in (e.g., CustomerName, Region).
- Click OK.
Now your Sales table has those extra columns.
Join types explained in plain English
When you merge, the join type decides which rows to keep.
Left Outer (most common)
- Keep all rows from the first table
- Bring in matches from the second table when available
- Unmatched rows show null for the added columns
Use it for: adding lookup columns (Sales + Customers)
Inner
- Keep only rows that match in both tables
- No match = removed
Use it for: keeping only valid transactions (e.g., Sales that have a recognised CustomerID)
Right Outer
- Keep all rows from the second table, match from the first
Less common in Power Query workbooks. You can usually swap table order and use Left Outer instead.
Full Outer
- Keep all rows from both tables
- Matches combine, non-matches appear with nulls on one side
Use it for: comparing two lists and spotting differences.
Left Anti (very useful)
- Keep rows from the first table that do not match the second table
Use it for: finding Sales rows with CustomerIDs not found in Customers.
Right Anti
- Keep rows from the second table that do not match the first
Use it for: finding Customers with no Sales.
A practical example: Sales + Customers + Products
Let’s say you want a final table that includes:
- Sales: OrderDate, CustomerID, ProductID, Qty, Amount
- Customers: CustomerName, Region
- Products: ProductName, Category
Step-by-step
- Start with the Sales query
- Merge Sales with Customers on CustomerID (Left Outer)
- Expand CustomerName and Region
- Merge the result with Products on ProductID (Left Outer)
- Expand ProductName and Category
- Close & Load To… (Table or Data Model)
This creates a tidy dataset ready for PivotTables or dashboards.
When merges create extra rows (and why)
Sometimes you merge and your row count increases. That usually means the lookup table has duplicate keys.
Example:
- Customers table should have one row per CustomerID
- But CustomerID 1001 appears twice
- A Sales row for 1001 will match two rows, creating “multiple matches” inside the merged cell. When expanded, it can duplicate the Sales row.
Fix duplicate keys in the lookup table
In the lookup query:
- Select the key column (CustomerID)
- Home > Remove Rows > Remove Duplicates
- Or group by CustomerID and decide which record to keep
If duplicates are real and meaningful, you may need a different key (CustomerID + Region, or CustomerID + StartDate).
Merging on multiple columns (composite keys)
Sometimes one column is not enough. Example: Product price changes by date.
You might need:
- ProductID and PriceDate
or - CustomerID and Department
To merge on multiple columns:
- In the merge window, click the first key column
- Hold Ctrl and click the second key column
- Repeat on the second table in the same order
- Choose your join type and merge
Order matters. Power Query matches column 1 with column 1, column 2 with column 2.
How to check if your merge worked
1) Look for nulls
After expanding, filter the new columns for null. If you see nulls, those keys did not match.
2) Use a Left Anti merge to find “missing keys”
- Merge Sales to Customers with Left Anti
- The result shows all Sales rows with CustomerIDs missing from Customers
This is one of the best data quality checks you can do.
3) Compare row counts before and after
If Sales had 20,000 rows and still has 20,000 rows after a Left Outer merge, that’s a good sign. If it becomes 21,000, you likely have duplicates in the lookup table.
Common merge problems (and quick fixes)
Problem 1: Everything comes back null
Likely causes:
- Key types don’t match (text vs number)
- Hidden spaces or non-printing characters
- Leading zeros lost
Fix: - Set types the same on both sides
- Trim/Clean keys
- If leading zeros matter, use Text type
Problem 2: Some rows match, some don’t
Likely causes:
- Inconsistent formatting (ABC vs abc)
- Extra spaces in only some records
Fix: - Uppercase both keys and Trim/Clean
Problem 3: Row count increases after expand
Likely cause:
- Duplicate keys in the lookup table
Fix: - Remove duplicates or build a better key
Problem 4: Merge is slow
Likely causes:
- Tables are huge and contain unnecessary columns
- You merge before filtering
Fix: - Remove unused columns early
- Filter down first (if possible)
- Consider loading to the Data Model rather than to a sheet table
Best practices for reliable merges
- Clean keys early (types + trim/clean).
- Rename columns clearly before expanding.
- Keep lookup tables unique on the key.
- Prefer Merge as New while building so you can troubleshoot easier.
- Use anti joins for data quality checks.
- Remove columns you don’t need before merging to keep performance strong.
- Document what each merge is doing (rename steps like “Merged Customers” instead of “Merged Queries”).
Conclusion
Merging tables is one of the most useful Power Query skills. It lets you build clean, repeatable datasets without filling your workbook with lookup formulas. Once the merge is set up, refreshing becomes quick and dependable. With the right keys, the right join type, and a few simple checks, you can combine data like a pro.
.png){kind=small}
