Excel Lesson 29 – Merging Tables | Dataplexa

Excel Course

I. Excel Basics & Fundamentals
1. Introduction to Excel 2. Excel Interface 3. Ribbons & Tabs 4. Workbooks & Sheets 5. Cells & Ranges 6. Basic Formatting 7. Number Formatting 8. Sort & Filter 9. Find & Replace 10. Basic Formulas
II. Intermediate Excel (Formulas & Functions)
11. Text Functions 12. Date Functions 13. Logical Functions 14. Math Functions 15. Lookup Functions 16. XLOOKUP 17. XMATCH 18. Dynamic Arrays 19. TEXTSPLIT 20. TEXTBEFORE / TEXTAFTER
III. Advanced Excel Functions
21. FILTER 22. SORT 23. UNIQUE 24. VSTACK 25. HSTACK
IV. Power Query
26. Power Query Introduction 27. Transforming Data 28. Data Cleaning 29. Merging Tables 30. Automation in Power Query
V. Power Pivot & DAX
31. Power Pivot Basics 32. Data Models 33. DAX Basics 34. DAX Advanced 35. Measures
VI. Excel Dashboards
36. Dashboard Basics 37. Charts 38. Slicers 39. PivotCharts 40. Full Dashboard Project
VII. Automation & Office Scripts
41. Office Scripts Intro 42. Recording Scripts 43. Editing Scripts 44. Automating Tasks 45. Advanced Scripting
VIII. Final Excel Projects
46. Data Cleaning Project 47. Sales Dashboard 48. HR Dashboard 49. Finance Dashboard 50. Excel Capstone Project
Lesson 29 · Power Query Practical

Merging Tables in Power Query

Merging is Power Query's answer to VLOOKUP — but done properly. Where VLOOKUP retrieves one value at a time and breaks when columns move, a Power Query merge joins two entire tables on a matching key column and brings across as many columns as you need, all at once, automatically, on every refresh. If you have ever built a report that required fifteen VLOOKUP columns pulling from a reference table, merging in Power Query will feel like a revelation. This lesson covers all six join types and the practical patterns you will use most.

What Is a Merge?

A merge combines two queries — a left table and a right table — by matching rows on one or more key columns. The result is a new query where each row from the left table gains additional columns from the right table wherever the key values match. You control how unmatched rows are handled by choosing a join type.

Merge — Joining Sales Data With a Department Reference Table
Left Table — Sales
Emp ID Sales
1001£119,000
1002£93,000
1003£117,000
1004£82,000
Right Table — Employee Reference
Emp ID Name Department
1001PriyaFinance
1002SarahOperations
1003JamesSales
1005MariaHR
→
Merged Result (Left Join)
Emp ID Sales Name Department
1001£119,000PriyaFinance
1002£93,000SarahOperations
1003£117,000JamesSales
1004£82,000nullnull
Emp 1004 kept (left join) — no match in reference table → null · Emp 1005 not in sales → excluded

The Six Join Types

The join type controls what happens to rows that do not have a match in the other table. Power Query offers all six standard database join types. Understanding which to use is the key skill in merging — the data you get back changes completely depending on your choice.

Join Type What it returns Use for
Left Outer ★ All left rows · matching right columns (null where no match) Adding lookup columns — keep all original rows regardless of match
Right Outer All right rows · matching left columns (null where no match) When the right (reference) table must be fully preserved
Full Outer All rows from both tables (null wherever there is no match) Seeing everything — comparing two lists for gaps on either side
Inner Only rows that match in both tables Clean output with no nulls — only confirmed matching records
Left Anti Left rows with NO match in the right table Finding records missing from the reference — orphan row detection
Right Anti Right rows with NO match in the left table Finding reference items that have no transactions — unused records
★ Left Outer is the default and by far the most commonly used join type — equivalent to VLOOKUP behaviour

Join Type Visual — Same Two Tables, Different Results

Left has IDs: 1001, 1002, 1003, 1004 · Right has IDs: 1001, 1002, 1003, 1005
Left Outer
Returns: 1001 ✓ 1002 ✓ 1003 ✓ 1004
All 4 left rows kept. 1004 has null for right columns — no match found.
Inner Join
Returns: 1001 ✓ 1002 ✓ 1003 ✓
Only matched rows. 1004 (no match) and 1005 (left only) both excluded.
Full Outer
Returns: 1001 ✓ 1002 ✓ 1003 ✓ 1004 1005
All 5 unique IDs across both tables. Both 1004 and 1005 have nulls on the non-matching side.
Left Anti
Returns: 1004 only
Only left rows with NO match. Useful for finding orphaned or unmatched records.
Right Anti
Returns: 1005 only
Only right rows with NO match. Useful for finding reference items with no activity.
Right Outer
Returns: 1001 ✓ 1002 ✓ 1003 ✓ 1005
All 4 right rows kept. 1005 has null for left columns — no match in sales.

How to Perform a Merge — Step by Step

Step 1 — Open the left query in Power Query Editor
  (the table you want to add columns to)

Step 2 — Home → Merge Queries
  (or Merge Queries as New to keep the original untouched)

Step 3 — In the Merge dialog:
  Top table:     your left query (already selected)
  Bottom table:  choose the right query from the dropdown
  Click the key column in the top table
  Click the matching key column in the bottom table
  Choose your Join Kind

Step 4 — Click OK
  Power Query adds a new column on the right containing "Table" objects

Step 5 — Expand the merged column
  Click the expand icon (⤢) on the new column header
  Untick "Use original column name as prefix" (usually cleaner)
  Select only the columns you need from the right table
  Click OK

Step 5 — expanding the merged column — is the one people most often miss or mishandle. After the merge, Power Query does not immediately show you the values from the right table. It shows a column of Table objects, each one containing the matching rows from the right. Clicking the expand icon opens a selector where you choose which right-table columns to bring across. If you forget to untick "Use original column name as prefix," all your new columns will have names like "EmployeeRef.Name" instead of just "Name."

Merging on Multiple Key Columns

Sometimes a single column is not enough to uniquely identify a match — for example, when joining by both Region and Quarter together. Power Query supports multi-column merges: simply click the first key column, then Ctrl+click the second key column in both the top and bottom tables. The order of selection must match between the two tables. 

Left table: Region, Quarter, Sales
Right table: Region, Quarter, Target

Multi-column merge key: Region + Quarter (both must match)

In the Merge dialog:
  Click "Region" in the top table   → shows (1)
  Ctrl+click "Quarter" in top       → shows (2)
  Click "Region" in the bottom table → shows (1)
  Ctrl+click "Quarter" in bottom    → shows (2)

Result: each row matched on the combination of Region AND Quarter.
"North Q1" only matches "North Q1" — not "North Q2" or "East Q1".
North + Q1 matches North + Q1 → Target column brought across. North + Q2 matches separately.
Multi-Key Merge — Region + Quarter as Combined Key
Region Quarter Sales Target ← merged vs Target
North Q1 £119,000 £100,000 +£19,000
North Q2 £93,000 £100,000 -£7,000
East Q1 £117,000 £90,000 +£27,000
Target column brought across by matching both Region AND Quarter — then vs Target added as a Custom Column

Merge vs Append — Knowing the Difference

Merging and appending are both ways to combine two queries, but they do completely different things. Merging joins tables horizontally — it adds columns from the right table to the left. Appending stacks tables vertically — it adds rows from the second table below the first. This is the same distinction as HSTACK vs VSTACK in formulas.

Merge Queries — adds columns
Two tables joined on a key column. The result has all the original columns plus new columns from the right table.
Use when: enriching rows with data from a reference table
Example: Add department name to a sales table using Employee ID
Append Queries — adds rows
Two tables stacked on top of each other. The result has the same columns but more rows — one table continues where the other ends.
Use when: combining data from multiple periods or regions
Example: Stack Q1, Q2, Q3, Q4 into one annual table
💡 Teacher's Note
The most common mistake with merges is choosing the wrong join type and not noticing. If your result has fewer rows than you expected, you probably used an Inner join when you meant Left Outer. If it has more rows than expected, you may have a one-to-many relationship where one left key matches multiple right rows — each match creates a separate output row. Always check the row count of your merged query against what you expect. The row count is shown in the bottom status bar of the Power Query editor and it is one of the fastest ways to catch a join type mistake before the data ever reaches your sheet.

🟠 Practice

Q1. You have a Sales table and an Employee reference table. You want to add the employee Name and Department to every row in the Sales table. Some sales rows may not have a matching employee. Which join type should you use and why?




Q2. You want to find all Employee IDs in your Sales table that do NOT exist in the Employee reference table. Which join type returns exactly those rows?




Q3. After performing a merge, a new column appears in the preview showing "Table" in every row. What do you need to do to see the actual values from the right table?



🟣 Quiz

Q1. What is the difference between Merge Queries and Append Queries in Power Query?







Q2. You perform a Left Outer merge between a Sales table (100 rows) and a Products reference table. The result has 143 rows. What most likely caused this?







Q3. You want to merge two tables on a combination of Region AND Quarter — not just one column. How do you select both key columns in the Merge dialog?






Next up — Automation in Power Query, where you will learn how to schedule refreshes, manage data source connections, and build queries that keep your reports completely up to date without any manual intervention.

← Previous Course Index Next →