Merge Queries & Append Queries
Two operations that sound similar and do opposite things. Append stacks tables on top of each other — more rows. Merge joins them side by side — more columns. Choosing the wrong one is a common beginner error; choosing the wrong join kind is a common expert error, and far more expensive.
Learning Objective
Merge has six join kinds and most people only ever use one. The other five — especially the anti-joins — are the most useful validation tools in Power Query.
- Choose correctly between Append and Merge for a given problem
- Use all six join kinds, and say what each returns
- Use anti-joins to validate data before building a model
- Recognise and prevent row explosion from a many-to-many merge
- Decide whether a join belongs in Power Query or in the data model
Why This Matters
Two failures, one from each operation.
Row explosion. You merge orders to a product table, expecting one product per order line. The product table has duplicates — two rows per product because someone loaded two price lists. Your 15,000-row order table becomes 28,000 rows and every revenue figure is inflated. Nothing errors.
Silent row loss. You use an inner join instead of a left outer, and every order whose product is missing from the product table simply disappears. The total drops and there is no indication why.
Concept
Append versus Merge
| Append | Merge | |
|---|---|---|
| Does | Stacks tables vertically | Joins tables horizontally |
| Result | More rows, same columns | More columns, ideally the same rows |
| Matches on | Column names | Column values (the join key) |
| Use for | The same kind of data from several sources — Jan, Feb, Mar files | Different kinds of data about the same entity — orders plus product details |
| SQL equivalent | UNION ALL | JOIN |
Append
Home › Append Queries. Two or more queries; the result has all their rows.
NetSales and another has Net_Sales, you do not get an error. You get both columns, each half-full of nulls — one populated for the first table's rows, the other for the second's.Standardise column names before appending. And afterwards, check for unexpected columns full of nulls — that is the signature of this problem.
Two practical points:
- Add a source column before appending. A
Sourcecolumn naming which table each row came from costs almost nothing and makes debugging trivial. - The Folder connector already does this for you. If you are appending files with identical structure, the Folder connector (Module 2 Lesson 2) is a better tool.
Merge, and the six join kinds
Home › Merge Queries. Pick two tables, click the join column in each, and choose a join kind. This is the dropdown people skim past.
| Join kind | Returns | Use it for |
|---|---|---|
| Left Outer | All rows from the first table, matched rows from the second | The default and usually right. Enriching a table without losing any of its rows |
| Right Outer | All rows from the second, matched from the first | Rare — usually you would swap the table order and use left outer |
| Full Outer | All rows from both, matched where possible | Reconciling two lists where either may contain rows the other lacks |
| Inner | Only rows that match in both | When an unmatched row is genuinely meaningless. Loses rows silently |
| Left Anti | Rows in the first table with no match in the second | Validation. "Which orders reference a product I do not have?" |
| Right Anti | Rows in the second with no match in the first | Validation. "Which products have never been ordered?" |
The anti-joins are the under-used ones
A left anti-join from fact to dimension answers it in ten seconds. If it returns zero rows, every key matches and the relationship will behave. If it returns rows, those are the orphans — and they will end up in a blank row on the dimension side, quietly excluded from every slice.
This one check prevents a whole category of "my totals do not add up when I slice" problems, and almost nobody does it.
Row explosion
A merge produces one output row for every matching pair. If the right table has duplicate keys, one left row becomes several.
| Relationship | Left rows | Right matches per key | Result |
|---|---|---|---|
| One-to-one | 1,000 | 1 | 1,000 — as expected |
| Many-to-one | 15,000 | 1 | 15,000 — as expected |
| One-to-many | 1,000 | 3 | 3,000 — intentional if you meant it |
| Many-to-many | 15,000 | 2 (unintended duplicates) | ~28,000 — explosion |
The dangerous case is the last one, because the duplicates are usually accidental — a dimension table that was not properly deduplicated, or a key that is not as unique as its name suggests.
This is the same check from Lesson 1, applied at the moment it matters most.
Expanding the merged column
A merge adds one column containing nested tables. Click the expand icon in its header to choose which columns to bring through.
- Untick "Use original column name as prefix" unless you need it. Otherwise you get
Products.Categoryrather thanCategory. - Expand only what you need. Every expanded column is loaded and stored.
- Consider aggregating instead. The expand dialog offers Aggregate as well as Expand — useful when the nested table has several rows and you want a count or a sum rather than an explosion.
Should the join be in Power Query at all?
This is the question worth asking every time. In Power BI you have two ways to combine tables: merge them into one, or load both and create a relationship.
| Merge in Power Query | Relationship in the model | |
|---|---|---|
| Result | One wide table | Two tables, joined at query time |
| Storage | Dimension attributes repeated on every fact row | Stored once in the dimension |
| Compression | Worse on large facts | Better |
| Star schema | Destroys it | Is it |
| Right for | Small lookups, mapping tables, adding one or two attributes | Dimension tables — the default for anything you slice by |
If you are pulling in a standardised country name from a mapping table — merge. If you are bringing in Category, Subcategory, Brand and Supplier from a product table so you can slice by them — do not merge. Load the product table separately and build a relationship. Module 4 explains why in full, and it is the single most consequential modelling decision you will make.
Visual Explanation
The six join kinds, on the same two tables.
| OrderID | ProductKey | NetSales |
|---|---|---|
| ORD-001 | P-1001 | 249.98 |
| ORD-002 | P-1002 | 89.99 |
| ORD-003 | P-9999 | 44.97 |
| ORD-004 | P-1001 | 124.99 |
| ProductKey | Category |
|---|---|
| P-1001 | Electronics |
| P-1002 | Apparel |
| P-1003 | Beauty |
| Join kind | Rows | Contains |
|---|---|---|
| Left Outer | 4 | All 4 orders. ORD-003 gets null for Category |
| Inner | 3 | ORD-003 silently disappears |
| Right Outer | 4 | All 3 products; P-1003 gets nulls for the order columns |
| Full Outer | 5 | All 4 orders plus P-1003 with nulls |
| Left Anti | 1 | Just ORD-003 — the order with an unknown product. This is the validation result |
| Right Anti | 1 | Just P-1003 — the product never ordered |
Default to left outer. Then use a left anti-join to see what did not match, and decide deliberately what to do about it.
Power BI Demonstration
bwb-retail-sales.csv and bwb-products.csv. Record both row counts: 15,067 and 114.ProductKey in the products query and check column distribution. Distinct = unique = 114. Safe.Category and Subcategory, and untick "Use original column name as prefix".Use as New for every anti-join validation. You get the answer without disturbing your working query, and you can keep the validation query around — disable its load and it becomes a permanent check you can re-run after any refresh.
Example Dataset
Merge this to bwb-retail-sales.csv on ProductKey. Both are clean, which makes them a good baseline before you deliberately break them.
6 KB · 114 rows · 6 columns · CSV
Columns & data types
| Column | Data type | Meaning |
|---|---|---|
ProductKey | Text | Primary key. Joins to bwb-retail-sales.csv. |
ProductName | Text | Display name for the product. |
Category | Text | One of six top-level categories. |
Subcategory | Text | Finer grouping inside the category. Builds a natural hierarchy. |
UnitPrice | Decimal | Current list price. |
UnitCost | Decimal | Current unit cost. |
Questions this dataset can answer
- Which subcategories carry the widest price range?
- What is the theoretical margin per category before discounting?
Step-by-Step Exercise
Expected Result
| Check | Expected |
|---|---|
| Left anti-join, clean data | 0 rows — every ProductKey exists in products |
| Right anti-join, clean data | Some rows — products that have never been ordered |
| Left outer merge row count | 15,067 — unchanged |
| Revenue total after merge | Unchanged at about $6,311,491 |
| With one orphan, left outer | 15,067 rows; that row's Category is null |
| With one orphan, inner join | 15,066 rows — the orphan is gone, and so is its revenue |
| With 3 duplicated products | Row count rises; revenue total inflates proportionally |
| Append with a renamed column | Two columns, each half null; no error |
Default to left outer. You can always see and handle the nulls; you cannot see rows that were never there.
The modelling question
Merging Category into the 15,067-row sales table repeats each of 6 category values thousands of times. Loading products as a dimension and building a relationship stores each value once, compresses far better, and produces the star schema the engine is optimised for.
The merge is fine for this exercise, and would be the wrong design in a real model. Module 4 makes this case properly — this exercise exists partly so you feel the difference before it is explained.
Common Mistakes
Fix: Default to left outer. Use a left anti-join to see what did not match, then handle it deliberately.
Fix: Check distinct = unique = row count on the join column before every merge. Ten seconds.
Fix: Record rows and your main total before and after. They should match exactly.
Fix: Standardise column names before appending, and check for unexpected null-heavy columns afterwards.
Fix: Merge for lookups. Relate for dimensions. If you will slice by it, it belongs in its own table.
Professional Tip
• Name it clearly —
DQ - Orders with unknown product• Right-click → uncheck Enable load so it does not enter the model as a table
• Or better: load it and put its row count on a hidden validation page
It costs nothing to keep and it becomes a standing check. When someone adds a product code to the ordering system before adding it to the product master — which happens constantly in real organisations — you find out on the next refresh rather than three months later when someone asks why a category total looks low.
Two more:
- Use "Merge Queries as New" for anything exploratory. It leaves your working query untouched, so a failed experiment costs nothing.
- Prefer merging on a single key column. Multi-column merges work and are slower and harder to debug. If you regularly need a composite match, build the composite key as a column first (Lesson 4) and merge on that.
Knowledge Check
Knowledge Check
5 questions. Answer each one to see why the right answer is right.
You merge a 15,000-row order table to a product table and end up with 28,000 rows. What happened?
Which join kind answers "which orders reference a product that does not exist in my product table?"
Why is inner join dangerous as a default?
You append two queries and end up with both NetSales and Net_Sales, each half full of nulls. Why?
You need Category, Subcategory and Brand from a product table so users can slice by them. Merge or relate?
Practical Challenge
Validate a join before you trust it
You are given an order file and a product master that were exported from two different systems. Before merging them, prove whether the join is safe — and quantify exactly what each possible mistake would cost.
- Deliberately introduce faults: at least 3 orphaned product keys in orders, and at least 2 duplicate keys in the product master
- Run both anti-joins and report what each found
- Quantify the revenue that inner join would silently remove
- Quantify the revenue inflation the duplicate keys would cause
- Produce a safe merge that neither loses nor inflates anything
- State what you would escalate to the source owners, and why
Order of operations matters here. Validate first, fix the dimension, then merge:
- Left anti-join to find orphans — these are an orders problem.
- Check the product key's uniqueness — duplicates are a product master problem.
- Fix the duplicates before merging, or the explosion happens regardless of join kind.
- Then merge with left outer, and handle the orphans visibly.
For the quantification, sum NetSales on the anti-join result — that is exactly the revenue at risk.
Solution
Try the challenge properly before opening this. Reading a solution you have not attempted feels like learning, but it is not.
The validation
1 · Left anti-join — orphaned keys
let
Orphans = Table.NestedJoin(
Orders, {"ProductKey"},
Products, {"ProductKey"},
"match", JoinKind.LeftAnti
),
Summary = Table.Group(Orphans, {"ProductKey"}, {
{"Orders", Table.RowCount, Int64.Type},
{"Revenue", each List.Sum([NetSales]), type number}
})
in
Summary| ProductKey | Orders | Revenue at risk |
|---|---|---|
| P-9001 | 14 | 4,820.60 |
| P-9002 | 9 | 2,145.35 |
| P-9003 | 22 | 8,903.11 |
| Total | 45 | 15,869.06 |
What inner join would silently remove: 45 orders and £15,869.06 of revenue. No error, no warning, no visual indication. Every category total, every regional total and the grand total would be understated by that amount, permanently.
2 · Uniqueness check — duplicate keys in the product master
Table.SelectRows(
Table.Group(Products, {"ProductKey"}, {{"n", Table.RowCount, Int64.Type}}),
each [n] > 1
)| ProductKey | Rows in master | Orders affected | Multiplier |
|---|---|---|---|
| P-1015 | 2 | 312 | ×2 |
| P-1042 | 3 | 187 | ×3 |
Revenue inflation if merged unfixed: P-1015's 312 orders would become 624 rows, and P-1042's 187 would become 561. The duplicated rows carry the same NetSales values, so revenue for those products would be doubled and tripled respectively.
On this sample that is roughly £41,000 of phantom revenue — and critically, it would be concentrated in two products, so it would distort the "top products" analysis specifically, which is exactly the visual most likely to be looked at.
3 · The safe merge
let
// --- fix the dimension FIRST -------------------------------------
// duplicates here are a product-master defect, not something to
// work around silently - but we must not explode the fact table
// while waiting for it to be fixed at source.
ProductsDeduped =
Table.Distinct(Products, {"ProductKey"}),
// --- left outer: never lose a fact row ---------------------------
Merged = Table.NestedJoin(
Orders, {"ProductKey"},
ProductsDeduped, {"ProductKey"},
"prod", JoinKind.LeftOuter
),
Expanded = Table.ExpandTableColumn(Merged, "prod",
{"Category", "Subcategory"},
{"Category", "Subcategory"}), // no prefix
// --- make the gap VISIBLE, not silent ---------------------------
Flagged = Table.TransformColumns(Expanded, {
{"Category", each _ ?? "[Unknown product]", type text},
{"Subcategory", each _ ?? "[Unknown product]", type text}
})
in
Flagged| Check | Before | After | Verdict |
|---|---|---|---|
| Order rows | 15,067 | 15,067 | unchanged |
| Total NetSales | 6,311,491.41 | 6,311,491.41 | unchanged |
| Rows with [Unknown product] | — | 45 | visible, not lost |
| Revenue in [Unknown product] | — | 15,869.06 | still counted in the total |
Leaving null keeps the revenue in the grand total but puts it in a blank category, which most people reading a chart will not notice and some visuals will hide.
Flagging with
[Unknown product] keeps the revenue and makes the gap appear as a labelled bar in every category chart. Someone will ask what it is — which is exactly the outcome you want, because it gets the source data fixed.4 · What I would escalate
To the product master owner: "Two product codes appear more than once in the master export — P-1015 twice and P-1042 three times. A product code should be unique. I am de-duplicating on my side so the reporting is not affected, but that means I am arbitrarily keeping one row and discarding the others — if those rows differ in any way that matters (different price, different category), my choice may be the wrong one. Could you confirm whether these are genuine duplicates or two different products sharing a code?"
The judgement being demonstrated
Three things separate this from a merely correct answer.
The faults are attributed to the right owners. Orphaned keys are an ordering-system problem; duplicate keys are a product-master problem. Sending one combined complaint to whoever you happen to know gets neither fixed.
The impact is quantified in money. "There are some orphaned keys" gets deprioritised. "£15,869 of revenue cannot be categorised" gets actioned.
The de-duplication is flagged as an assumption, not presented as a fix. Table.Distinct keeps an arbitrary row. Saying so explicitly — rather than quietly deduplicating and moving on — is the difference between managing the risk and hiding it.
Lesson Summary
- Append stacks rows; Merge joins columns. Append matches on column name, Merge on key values.
- Default to Left Outer. Inner join silently drops unmatched rows and looks identical on clean data.
- Anti-joins are validation tools. Left anti finds orphaned foreign keys; right anti finds unused dimension rows.
- Check the right table's key is unique before merging — distinct = unique = row count — or you get row explosion.
- A many-to-one merge must not change the row count. If it did, something is wrong.
- Flag unmatched rows visibly with a token like
[Unknown]rather than dropping them or leaving nulls. - Merge for lookups. Relate for dimensions. Anything you slice by belongs in its own table.
- Keep anti-join validations as permanent check queries, not one-off investigations.
Syllabus topics delivered
Progress
Marking a lesson complete stores your progress in this browser and updates your BitWithBite learner dashboard. Nothing is uploaded anywhere.