Level 6 · Which join kind does the merge need?

Merge Queries is Power Query’s join: it matches the rows of two queries on a key column, the way SQL’s JOIN and Excel’s XLOOKUP do. The join kind decides which rows survive, and it’s the setting people most often leave on its default without thinking.

Where it is

In Power BI: Home → Transform data, select the customers query, then Home → Merge Queries (adds a step to that query) or Merge Queries as New (makes a third query and leaves both inputs untouched). In the dialog, pick Orders as the second table, click customer_id in both previews, and choose the Join Kind. At the bottom, the dialog tells you how many rows match.

The merged table has a new column holding a small table of matching Orders rows; the expand icon on its header picks which of those columns to bring in.

The six join kinds

Join kind	Keeps	New customers + Orders here
Left Outer (default)	every first-table row, with matches or nulls	59 rows
Right Outer	every second-table row, with matches or nulls	26 rows
Full Outer	every row from both sides	59 rows
Inner	only rows that match on both sides	26 rows
Left Anti	first-table rows with no match	33 rows
Right Anti	second-table rows with no match	0 rows

The row counts are after expanding order_id: each customer with orders appears once per order, which is why left outer reaches 59 rows from 52 customers.

In the practice editor

Write the join kind after the key; leave it out and the merge is a left outer join, as in the dialog:

Merge queries: Orders on customer_id, left anti
Merge queries: Orders on customer_id, inner, expand order_id, order_date
Merge queries: Orders on customer_id, full outer, expand order_id

An anti join brings in nothing from the other side (every match is gone), so it needs no expand. Right outer, full outer and right anti do: they add rows of the second table, and the first table’s columns are null on those rows.

In M, the kind is the last argument of the join step:

Table.NestedJoin(Source, {"customer_id"}, Orders, {"customer_id"}, "Orders", JoinKind.LeftAnti)

Picking one

• Left outer: you’re adding columns to a table and every row must stay, as with a lookup of the category for each order line. The default, and the right choice most of the time.
• Inner: you only want rows that exist in both, for example order lines whose product is in a promotion list.
• Left anti: “which of these has no partner?”, Sarah’s question.
• Right anti: the same question from the other side. Here, orders whose customer is missing from the list: none, which is a useful check that the data is complete.
• Full outer: reconciling two lists, such as our orders against a marketplace’s settlement file, where rows can be missing on either side.

Read the match count

The Merge dialog says something like “The selection matches 19 of 52 rows from the first table”. Read it every time. If you expected everything to match and it doesn’t, look at the keys: types that differ (text 0127 against the number 127), stray spaces, or different case. Use fuzzy matching in the same dialog can match near-identical text, but check what it paired before trusting it.

Sarah’s list

Left anti gives 33 customers. Across the whole customer table the same merge finds 247, and eighteen of those are second accounts (ids 2001 to 2018) of people already in Customers under an earlier id, with the same email apart from case or a stray space. This level’s query leaves them out; on the full table, drop them before sending, because those people may well have ordered under their first account.

Common mistakes

• Leaving Left Outer on for a “who hasn’t” question. It works, with an extra filter on null, and a lot more rows.
• Inner instead of anti. Inner keeps the people who did order: the opposite list.
• Expanding every column. Bring in only the columns the report needs.
• Ignoring the match count. A merge that matched nothing still runs without an error.