Power QueryExercises

Merge queries: choosing a join kind

Pick the join kind that returns exactly the rows the question asks for.

~12 min read · intermediate · Module 12: Merging (joining) tables

By Tahir Nas · Examples run in our simulator · Published · Updated

Before this: Append tables and align their columns

A merge matches rows of one table with rows of another on key columns, then adds the matches as a nested table you can expand. The join kind decides which rows survive. Here are three orders and three customers; order A-3 has an unknown customer C9, and customer C3 has no orders.

let
    Orders = #table({"OrderID", "Cust"}, {{"A-1", "C1"}, {"A-2", "C2"}, {"A-3", "C9"}}),
    Customers = #table({"Cust", "City"}, {{"C1", "Leeds"}, {"C2", "York"}, {"C3", "Hull"}}),
    Count = (kind) => Table.RowCount(Table.NestedJoin(Orders, {"Cust"}, Customers, {"Cust"}, "M", kind))
in
    #table({"Join kind", "Rows"}, {
        {"Left outer", Count(JoinKind.LeftOuter)},
        {"Inner", Count(JoinKind.Inner)},
        {"Left anti", Count(JoinKind.LeftAnti)},
        {"Right anti", Count(JoinKind.RightAnti)},
        {"Full outer", Count(JoinKind.FullOuter)},
        {"Right outer", Count(JoinKind.RightOuter)}
    })

Result

Join kindRows
Left outer3
Inner2
Left anti1
Right anti1
Full outer4
Right outer3

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Join kindKeepsTypical use
Left outerAll left rows, matches where foundLookups
InnerOnly rows with a matchIntersections
Left antiLeft rows with no matchData-quality checks
Right antiRight rows with no matchUnused master records
Full outerEverything from bothReconciliations
Right outerAll right rows, matches where foundRarely needed: swap the tables and use left outer

A lookup

let
    Orders = #table({"OrderID", "Cust"}, {{"A-1", "C1"}, {"A-2", "C2"}, {"A-3", "C9"}}),
    Customers = #table({"Cust", "City"}, {{"C1", "Leeds"}, {"C2", "York"}, {"C3", "Hull"}}),
    Merged = Table.NestedJoin(Orders, {"Cust"}, Customers, {"Cust"}, "Customer", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Merged, "Customer", {"City"}, {"City"})
in
    Expanded

Result

OrderIDCustCity
A-1C1Leeds
A-2C2York
A-3C9null

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Expand only the columns you need, and untick Use original column name as prefix unless you want names like Customer.City. The null City for A-3 is information: the customer is missing from the master list.

Finding what's missing

let
    Orders = #table({"OrderID", "Cust"}, {{"A-1", "C1"}, {"A-2", "C2"}, {"A-3", "C9"}}),
    Customers = #table({"Cust", "City"}, {{"C1", "Leeds"}, {"C2", "York"}, {"C3", "Hull"}}),
    Unmatched = Table.NestedJoin(Orders, {"Cust"}, Customers, {"Cust"}, "Customer", JoinKind.LeftAnti)
in
    Table.RemoveColumns(Unmatched, {"Customer"})

Result

OrderIDCust
A-3C9

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

A left anti join is a quick, refresh-safe data-quality check: it lists exactly the orders whose customer isn't known.

Where to find this in Excel

Excel for Windows
Home > Merge Queries (Combine group)
What you see
The Merge dialog shows the left and right tables, the matching columns and a Join Kind list with six options.

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Merge queries overview (Microsoft Learn).

Manage Power Query queries in Excel: load, refresh and edit

Common mistakes

  • Using an inner join for a lookup, silently dropping orders whose customer is missing.
  • Merging on keys of different types (number vs text) or with stray spaces, so nothing matches.
  • Expanding every column with the prefix option on, then referring to the wrong names later.

Excel, Power BI and the simulator

  • Excel and Power BI Desktop: Home → Merge Queries (adds a step) or Merge Queries as New. The dialog shows how many rows matched, which is a useful first check.
  • The merge dialog's fuzzy matching option uses Table.FuzzyNestedJoin. The simulator doesn't run fuzzy merges; they raise UnsupportedFeature.
  • Right and full outer joins in the simulator list matched left rows first, then unmatched right rows (KD04). Real hosts don't guarantee an order, so sort afterwards if it matters.

Next step

Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's part of Pro.

Practise: Look up each order's customer city (kite shop)

Practice this

Each exercise runs in the browser and is checked against fresh data, not just the example.