Home Course home Curriculum Learner dashboard Skill tree Business projects Datasets Final project
🧹 Module 3 · Power Query & Data Transformation Intermediate LESSON 22 / 72

Merge Queries & Append Queries

32 min 5 quiz questions 1 practical challenge 5 syllabus topics

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.

Lesson 22 of 72 · Power Query & Data Transformation 31%
01

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.

By the end of this lesson you will be able to
  • 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
02

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.

Both are silent, both change your numbers
This is the third time this module has made the same point, and it is because merging is where it bites hardest. Check the row count before and after every merge. If it changed and you did not expect it to, stop and find out why before doing anything else.
03

Concept

Append versus Merge

AppendMerge
DoesStacks tables verticallyJoins tables horizontally
ResultMore rows, same columnsMore columns, ideally the same rows
Matches onColumn namesColumn values (the join key)
Use forThe same kind of data from several sources — Jan, Feb, Mar filesDifferent kinds of data about the same entity — orders plus product details
SQL equivalentUNION ALLJOIN

Append

Home › Append Queries. Two or more queries; the result has all their rows.

Append matches on column name, and is unforgiving about it
If one table has 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 Source column 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.

All six join kinds
Join kindReturnsUse it for
Left OuterAll rows from the first table, matched rows from the secondThe default and usually right. Enriching a table without losing any of its rows
Right OuterAll rows from the second, matched from the firstRare — usually you would swap the table order and use left outer
Full OuterAll rows from both, matched where possibleReconciling two lists where either may contain rows the other lacks
InnerOnly rows that match in bothWhen an unmatched row is genuinely meaningless. Loses rows silently
Left AntiRows in the first table with no match in the secondValidation. "Which orders reference a product I do not have?"
Right AntiRows in the second with no match in the firstValidation. "Which products have never been ordered?"

The anti-joins are the under-used ones

Run a left anti-join before every relationship you build
Before you create a relationship in Module 4, ask: does every foreign key in my fact table actually exist in the dimension?

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.

What each cardinality does to the row count
RelationshipLeft rowsRight matches per keyResult
One-to-one1,00011,000 — as expected
Many-to-one15,000115,000 — as expected
One-to-many1,00033,000 — intentional if you meant it
Many-to-many15,0002 (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.

Before merging, verify the right table's key is unique
Select the key column in the right-hand table and check column distribution: distinct must equal unique must equal row count. If it does not, deduplicate the dimension first — or find out why it has duplicates, because that is usually the real problem.

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.Category rather than Category.
  • 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 versus relationship
Merge in Power QueryRelationship in the model
ResultOne wide tableTwo tables, joined at query time
StorageDimension attributes repeated on every fact rowStored once in the dimension
CompressionWorse on large factsBetter
Star schemaDestroys itIs it
Right forSmall lookups, mapping tables, adding one or two attributesDimension tables — the default for anything you slice by
The rule
Merge for lookups. Relate for dimensions.

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.
04

Visual Explanation

The six join kinds, on the same two tables.

Orders (left)
OrderIDProductKeyNetSales
ORD-001P-1001249.98
ORD-002P-100289.99
ORD-003P-999944.97
ORD-004P-1001124.99
Key not in the product table
Products (right)
ProductKeyCategory
P-1001Electronics
P-1002Apparel
P-1003Beauty
Never ordered
What each join returns
Join kindRowsContains
Left Outer4All 4 orders. ORD-003 gets null for Category
Inner3ORD-003 silently disappears
Right Outer4All 3 products; P-1003 gets nulls for the order columns
Full Outer5All 4 orders plus P-1003 with nulls
Left Anti1Just ORD-003 — the order with an unknown product. This is the validation result
Right Anti1Just P-1003 — the product never ordered
Read the Inner row again
ORD-003's £44.97 vanishes from every total, with no error, no warning and no visual clue. If you had used inner join on a real order table with a few hundred orphaned keys, your revenue would be understated by however much those orders were worth — permanently, and invisibly.

Default to left outer. Then use a left anti-join to see what did not match, and decide deliberately what to do about it.
05

Power BI Demonstration

Load two tables
bwb-retail-sales.csv and bwb-products.csv. Record both row counts: 15,067 and 114.
Verify the product key is unique
Select ProductKey in the products query and check column distribution. Distinct = unique = 114. Safe.
Run a left anti-join first
Home › Merge Queries as New. Sales as the first table, Products as the second, join on ProductKey, kind = Left Anti. Expect zero rows — this dataset is clean. Note that a non-zero result would be your warning.
Run a right anti-join
Same but Right Anti. This shows products never ordered — useful commercial information in its own right, not just a validation check.
Now do the real merge
Merge Queries (not as new), Left Outer, on ProductKey.
Expand without the prefix
Click the expand icon, select only Category and Subcategory, and untick "Use original column name as prefix".
Verify nothing changed
Row count should still be 15,067. Sum of NetSales should be unchanged. If either moved, investigate before proceeding.
Deliberately create an explosion
Duplicate a few rows in the products query, then re-run the merge. Watch the sales row count rise. Undo.
Try an inner join and measure the loss
Change the join kind to Inner and compare the row count. On clean data it is identical — which is exactly why the danger is invisible until the data is not clean.
Append two queries
Split the sales file by year into two queries, then Home › Append Queries. Confirm the total equals the sum of both.
Break the append
Rename one column in one of the two queries and append again. Note the two half-null columns — no error, just wrong shape.
HomeMerge Queries as NewLeft Anti
Merge Queries as New, for validation
Merge Queries modifies the current query. Merge Queries as New creates a separate query and leaves yours untouched.

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.
06

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.

bwb-products.csv
6 KB · 114 rows · 6 columns · CSV
Business context
The product catalogue: category, subcategory, list price and unit cost. A clean dimension table with a genuine primary key — exactly what a star schema needs.

Columns & data types

ColumnData typeMeaning
ProductKeyTextPrimary key. Joins to bwb-retail-sales.csv.
ProductNameTextDisplay name for the product.
CategoryTextOne of six top-level categories.
SubcategoryTextFiner grouping inside the category. Builds a natural hierarchy.
UnitPriceDecimalCurrent list price.
UnitCostDecimalCurrent unit cost.

Questions this dataset can answer

  • Which subcategories carry the widest price range?
  • What is the theoretical margin per category before discounting?
07

Step-by-Step Exercise

Validate before merging
Left anti-join sales to products, and right anti-join products to sales. Record both counts.
Merge and verify
Left outer on ProductKey, expand Category and Subcategory without the prefix. Confirm the row count and revenue total are unchanged.
Create an orphan and observe
In a copy of the sales file, change one ProductKey to something that does not exist. Re-run the anti-join and confirm it finds exactly that row.
Compare left outer against inner on the broken data
Record the row count and revenue total for each. Quantify exactly what inner join silently removed.
Create a duplicate and observe the explosion
Duplicate three product rows. Re-merge and record the new sales row count and revenue total. Calculate the inflation.
Append the customers file to itself
Confirm the row count doubles. Then rename one column first and append again — observe the half-null columns.
Decide the modelling question
You have merged Category into the sales table. Write two sentences on whether that was the right call, or whether products should have stayed a separate related table.
08

Expected Result

Expected results
CheckExpected
Left anti-join, clean data0 rows — every ProductKey exists in products
Right anti-join, clean dataSome rows — products that have never been ordered
Left outer merge row count15,067 — unchanged
Revenue total after mergeUnchanged at about $6,311,491
With one orphan, left outer15,067 rows; that row's Category is null
With one orphan, inner join15,066 rows — the orphan is gone, and so is its revenue
With 3 duplicated productsRow count rises; revenue total inflates proportionally
Append with a renamed columnTwo columns, each half null; no error
The inner-join comparison is the exercise's point
On clean data, left outer and inner produce identical results — which is precisely why people use inner join habitually and never discover the problem. The difference only appears when the data has orphans, which is the moment you most need the rows.

Default to left outer. You can always see and handle the nulls; you cannot see rows that were never there.

The modelling question

The right answer
Products should stay a separate related table.

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.
09

Common Mistakes

Using inner join by habit
It silently drops every row whose key has no match. On clean data it looks identical to left outer, so the habit forms without consequence — until the data is not clean.

Fix: Default to left outer. Use a left anti-join to see what did not match, then handle it deliberately.
Merging without checking the right table's key is unique
Duplicate keys on the right multiply your left rows. Revenue inflates and nothing errors.

Fix: Check distinct = unique = row count on the join column before every merge. Ten seconds.
Not checking the row count after a merge
A many-to-one merge should never change the row count. If it did, something is wrong and you want to know now, not after building twelve visuals.

Fix: Record rows and your main total before and after. They should match exactly.
Appending tables with mismatched column names
Produces extra columns, each half full of nulls. No error, and the shape is subtly wrong.

Fix: Standardise column names before appending, and check for unexpected null-heavy columns afterwards.
Merging a dimension into a fact table instead of relating them
Repeats dimension attributes across millions of fact rows, destroys the star schema, and bloats the model.

Fix: Merge for lookups. Relate for dimensions. If you will slice by it, it belongs in its own table.
10

Professional Tip

Keep your anti-join validations as permanent queries
When you build an anti-join to check a relationship, do not delete it afterwards. Instead:

• 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.
11

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?

12

Practical Challenge

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.

Requirements
  • 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:

  1. Left anti-join to find orphans — these are an orders problem.
  2. Check the product key's uniqueness — duplicates are a product master problem.
  3. Fix the duplicates before merging, or the explosion happens regardless of join kind.
  4. 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.

13

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

Power Query M · Find orders whose product does not exist
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
What it found
ProductKeyOrdersRevenue at risk
P-9001144,820.60
P-900292,145.35
P-9003228,903.11
Total4515,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

Power Query M · Find duplicate product keys
Table.SelectRows(
    Table.Group(Products, {"ProductKey"}, {{"n", Table.RowCount, Int64.Type}}),
    each [n] > 1
)
What it found
ProductKeyRows in masterOrders affectedMultiplier
P-10152312×2
P-10423187×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

Power Query M · Deduplicate the dimension, then merge, then flag orphans
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
Verification
CheckBeforeAfterVerdict
Order rows15,06715,067unchanged
Total NetSales6,311,491.416,311,491.41unchanged
Rows with [Unknown product]—45visible, not lost
Revenue in [Unknown product]—15,869.06still counted in the total
Why flag rather than drop or leave null
Dropping loses £15,869 from every 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

Two separate escalations, to two different owners
To the ordering system owner: "45 orders reference three product codes — P-9001, P-9002, P-9003 — that do not exist in the product master. These represent £15,869 of revenue. Either the product master is missing them, or the ordering system is accepting codes it should reject. Could you confirm which? I have kept these orders in the reporting and labelled them '[Unknown product]' so the revenue is not lost, but they cannot be categorised until this is resolved."

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.

14

Lesson Summary

Key takeaways
  • 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

Merge QueriesAppend QueriesJoin kindsAnti-joins for validationRow explosion
15

Progress

Marking a lesson complete stores your progress in this browser and updates your BitWithBite learner dashboard. Nothing is uploaded anywhere.

Lesson complete — 31% of Power BI Mastery done
Progress saved. Keep the momentum: the next lesson builds directly on this one.