7.22 Merge Queries and All Join Kinds in Power Query with Example

By Ravindra Bagale Last updated: 6 October 2026

Merge queries (left outer join) in Power Query with example — in 30 seconds

What: Merge brings columns from another table by matching a key, like VLOOKUP.

Why: Orders only has Product ID. The manager wants to see Category.

Tiny example: P-021 matches Dairy & Breakfast. P-999 has no match, so Category is null.

First look at the data (before → after):

Merge queries (left outer join) in Power Query with example: before and after tables, fictional Blinkit Maharashtra data

Same before / after tables as text

Before — Orders

Table 1: columns Order ID, Product ID, Qty
Order ID	Product ID	Qty
BLK-301	P-021	2
BLK-302	P-041	1
BLK-303	P-999	1

About this data: This table uses columns: Order ID, Product ID, Qty. This table uses columns: Product ID, Category. This table uses columns: Order ID, Product ID, Qty, Category. This table uses columns: Wrong result, Reason, Correction. Values shown are the lesson examples already on this page (not a new dataset).

Before — Product

Table 2: columns Product ID, Category
Product ID	Category
P-021	Dairy & Breakfast
P-041	Snacks

Formula / click path

Home › Merge Queries · match Product ID · Join Kind: Left Outer
Expand ⇔ › tick Category

After — Orders after merge (null = missing product master)

Table 3: columns Order ID, Product ID, Qty, Category
Order ID	Product ID	Qty	Category
BLK-301	P-021	2	Dairy & Breakfast
BLK-302	P-041	1	Snacks
BLK-303	P-999	1	null

Do it step by step:

Select the Orders query.
Click Home › Merge Queries.
In the lower box choose Product.
Click Product ID in both tables.
Join Kind: Left Outer (all from first, matching from second). Click OK.
Click the expand icon on the new column. Tick only Category. Untick Use original column name as prefix.
Filter Category = null to find missing products. Tell the data owner.

Merge joins tables side by side using a key, like SQL JOIN or Excel's XLOOKUP.

Start with a problem (merge)

Shahrukh needs Category on each order for a Sambhaji Nagar fictional store report. Product names live in Products.xlsx. Merge joins side by side on Product ID — unlike append, row count stays (for left outer) while columns grow.

Checkpoint — merge

Left outer on the lesson's three orders: BLK-1 and BLK-2 get names; BLK-3 (P-999) has null Product Name — that orphan is a data-quality signal, not a bug in Merge.

Explore further (join kinds)

Inner, Left outer, Right outer, Full outer, Left anti, Right anti — practise each once on the tiny tables in this section. Anti joins find orphans (orders without products, products never sold).

Fix common mistakes (merge)

Table 4: columns Wrong result, Reason, Correction
Wrong result	Reason	Correction
Row count exploded	Many-to-many key / duplicates on right	Clean keys; use proper cardinality
All nulls from right	Key type mismatch (text vs number)	Set both Product ID columns to same type before merge

Practise independently (merge)

Lab — merge practice products

Use merge_append_products.csv with the appended Orders. Left outer on Product ID. Expect Dairy on P-101 lines.

Solution

Expand Product Name and Category after merge. P-310 unused on left outer. ANSWERS.md.

Think like a professional (merge)

Prefer relationships in the model for Category when the dimension is reused; use Merge when you must denormalise for export or a flat teaching file. Always check row count before vs after.

Table 5: columns Append Queries, Merge Queries
	Append Queries	Merge Queries
What it does	Adds rows (stacks)	Adds columns (joins)
SQL equivalent	`UNION ALL`	`JOIN`
Requirement	Same column names	A common key (e.g. Product ID)
Example	Blinkit + Amazon Now orders	Orders + Product → Category

Our two small tables for the examples:

Orders (left)

Table 6: columns Order ID, Product ID
Order ID	Product ID
BLK-1	P-101
BLK-2	P-205
BLK-3	P-999

Product (right)

Table 7: columns Product ID, Product Name
Product ID	Product Name
P-101	Gokul Cow Milk 500 ml
P-205	Poha 1 kg
P-310	Cotton Bedsheet

P-999 does not exist in Product (a bad code), and P-310 was never ordered.

Table 8: columns Join kind, Rows returned, Result for our tables, Typical use
Join kind	Rows returned	Result for our tables	Typical use
Left Outer (default)	All left rows + matches	BLK-1 Milk, BLK-2 Poha, BLK-3 null	Look up attributes
Right Outer	All right rows + matches	Milk–BLK-1, Poha–BLK-2, Chaddar–null	Keep the full product list
Full Outer	All rows from both	BLK-1, BLK-2, BLK-3 (null product), Chaddar (null order)	Reconciliation
Inner	Only matching rows	BLK-1 Milk, BLK-2 Poha	Keep only valid lines
Left Anti	Left rows with no match	BLK-3 (P-999)	Find bad product codes
Right Anti	Right rows with no match	P-310 Cotton Bedsheet	Find products never sold

Steps in Power BI – Left Anti to find unmatched codes

Select Orders › Home › Merge Queries › Merge Queries as New.
Top table Orders → click Product ID; bottom table Product → click Product ID.
Join Kind: Left Anti (rows only in first). The dialog shows how many rows match. Read it! › OK.
The result lists order lines whose Product ID is not in Product. Rename it DQ_Unknown_Products, and turn off Enable load (or load it for a data-quality page).
For a normal lookup, use Left Outer, then click the expand icon on the new column › tick only Product Name, Category › untick Use original column name as prefix.
To join on two columns (for example City + Platform), hold Ctrl and click both columns in the same order in both tables.

Lookup  = Table.NestedJoin(Orders, {"Product ID"}, Product, {"Product ID"}, "Product", JoinKind.LeftOuter),
Expand  = Table.ExpandTableColumn(Lookup, "Product", {"Product Name", "Category"}),
Unknown = Table.NestedJoin(Orders, {"Product ID"}, Product, {"Product ID"}, "P", JoinKind.LeftAnti)

Merge in Power Query or relationship in the model?

You do not need to merge every lookup table into the fact table (व्यवहारांची नोंद असलेला तक्ता). Usually it is better to keep dimensions separate and create relationships (संबंध) (star schema, Module 11). Merge when you need to flatten a snowflake, standardise values (7.9), or check data quality (anti joins).

Merge aani join kinds interview madhe khup vicharle jaatat. Sahahi join kinds swatah try kara.

Ravindra Bagale's Tip

Look, friends: merge keys with different types (text "101" vs number 101) or different case and spaces never match. Also, duplicate keys in the lookup table multiply rows: if P-101 appears twice in Product, every P-101 order line appears twice after expanding. Clean both keys first and check that the lookup key is unique. Clear?

Practice task

Use a Right Anti join between Orders and DarkStore to list dark stores (for example a new Wakad store) that have no orders yet.

Ravi Sir's Cards for this lesson

Merge queries and join kinds. Columns you need are often in another table. Merge brings them in.

Revision card What join kinds are there in Power Query Merge? Six: Left outer (all left rows), Right outer, Full outer (all rows from both), Inner (only matches), Left anti (left rows with no match) and Right anti.

What join kinds are there in Power Query Merge? Six: Left outer (all left rows), Right outer, Full outer (all rows from both), Inner (only matches), Left anti (left rows with no match) and Right anti.

Interview card

मराठी cards