Ravindra BagaleCourses & study guides

7. Data Cleaning A–Z in Power Query

7.22 Merge Queries and All Join Kinds

By Last updated:

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

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

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)

Order ID Product ID
BLK-1 P-101
BLK-2 P-205
BLK-3 P-999

Product (right)

Product ID Product Name
P-101 Gokul Cow Milk 500 ml
P-205 Poha 1 kg
P-310 Solapuri Chaddar

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

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 Solapuri Chaddar Find products never sold

Steps in Power BI – Left Anti to find unmatched codes

  1. Select Orders › Home › Merge Queries › Merge Queries as New.
  2. Top table Orders → click Product ID; bottom table Product → click Product ID.
  3. Join Kind: Left Anti (rows only in first). The dialog shows how many rows match. Read it! › OK.
  4. 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).
  5. 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.
  6. 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.