Excel Power Query Join Kind: Which Merge Should You Use?

How-to basics · Updated November 21, 2026

In Excel Power Query, a join kind determines which rows are kept when you merge two queries using matching columns. Choose a kind such as Left Outer, Inner, or Full Outer in the Merge dialog; Left Outer is the default and keeps every row from the first query.

What “join kind” means in Power Query

A merge connects two tables by comparing one or more key columns, much like matching customer IDs in an orders table to customer IDs in a customer list. The join kind controls which rows survive that comparison. It does not change the source data, and it is not the same as appending queries: a merge matches columns across tables, while an append stacks rows from one table under another.

In Excel for Microsoft 365 on Windows, open the Power Query Editor, select Home > Combine > Merge Queries or Merge Queries as New, choose the second query, select the matching column or columns in both previews, and pick a Join Kind. Merge Queries adds the result to the current query; Merge Queries as New creates a separate query. The merged result initially contains a nested table column, which you can expand to bring selected fields from the second query into the rows.

Think of the first table in the dialog as the left table and the second as the right table. “Left” and “right” refer to their positions in the merge, not to a permanent property of either query. Reversing the order can therefore change the result for a left or right join.

The six join kinds and what each keeps

  • Left Outer: Keeps every row from the first (left) table and matching rows from the second. If a left row has no match, expanded fields from the right side are null. This is the usual choice when enriching a main list without dropping its records.
  • Right Outer: Keeps every row from the second (right) table and matching rows from the first. Unmatched right-side records remain, with nulls for fields from the left. It can be useful, though swapping the table order and using Left Outer is often easier to read.
  • Full Outer: Keeps all rows from both tables. Matching records are combined; records with no counterpart remain with nulls for the missing side. Use it to reconcile lists or identify records present in only one source.
  • Inner: Keeps only rows with a match in both tables. Rows without a match on either side are excluded. This is useful when the output should contain only confirmed matches.
  • Left Anti: Keeps rows from the left table that have no match in the right. It is a direct way to find left-side records missing from a reference list.
  • Right Anti: Keeps rows from the right table that have no match in the left. Use it to find items that appear in the second list but not the first.

Anti joins are especially handy for exception reports. For example, a Left Anti merge from Orders to Customers can surface order rows whose customer ID is absent from the customer list. It returns the unmatched side’s records rather than a combined row with blank fields.

How to choose the right kind

Start by asking which records must remain, including records that do not have a counterpart. If every row in your primary table must survive, select Left Outer and put that table first. If only valid matches belong in the output, select Inner. If the goal is to compare the coverage of two lists, use Full Outer or an Anti join, depending on whether you need a combined reconciliation or just the exceptions.

  • Attach descriptive data: Use Left Outer for a transaction table joined to a product, employee, or customer lookup table.
  • Keep matched transactions only: Use Inner when unmatched transactions should not appear in the result.
  • Find missing reference records: Use Left Anti or Right Anti, choosing the side whose unmatched rows you want to inspect.
  • Reconcile two inventories: Use Full Outer when you need to see records unique to either inventory as well as matches.

A common misconception is that Left Outer means “show only matches, plus the left table.” It actually preserves all left-side rows. The right-side values are populated where a match exists; otherwise, those values are null. An Inner join is the option that discards unmatched rows from both sides.

Set up a merge correctly

  1. Load or create both tables as Power Query queries, then open the query that should be the first table.
  2. Choose Home > Combine > Merge Queries (or Merge Queries as New), select the other query, and click the key column in each preview.
  3. For a composite key, select the same number of columns in the same order in both previews. Use Ctrl-click on Windows to select multiple columns.
  4. Choose the join kind, review the match indicator shown beneath the previews, and confirm the merge.
  5. Use the expand button in the new table column to select the fields to bring in. Clear the option to use the original column name as a prefix if you prefer shorter column headings.

Before confirming, check that the chosen key columns represent the same meaning. Matching a product code to a description, for example, may produce few or no useful matches even though both columns contain text. If you select two key columns, both values must correspond in the selected order for the rows to match.

On Excel for Mac, Power Query features and ribbon details can vary by Microsoft 365 update. If the desktop editor offers the Merge command, the same join concepts apply; consult the current ribbon labels in that build. Excel for the web does not provide the same full desktop authoring experience in every setup, so use Excel for Windows when you need the complete, consistent merge workflow.

Keys, data types, and match quality

Join results are only as reliable as their keys. A number stored as text is not necessarily equivalent to the same digits stored as a number. In the Power Query Editor, inspect the type icon beside each key column and set compatible data types on both sides before merging. Also look for leading or trailing spaces, inconsistent capitalization, different punctuation, and alternate spellings. Transformations such as Trim or Clean can help with some text problems, but apply them deliberately: changing a key can also change which records match.

Power Query can match on more than one column. For instance, a table with repeated invoice numbers across different years may need both Invoice Number and Year as the key. Selecting only Invoice Number could match a record to the wrong year. Conversely, adding a column that differs between otherwise corresponding records can prevent a valid match.

The Merge dialog offers fuzzy matching for supported text-key scenarios. It can identify approximate text matches, which may help with inconsistent names, but it is not a substitute for a dependable unique identifier. Review the results carefully: approximate matching can connect records that look similar but refer to different entities. Exact matching is generally preferable for codes, IDs, and financial or operational records.

Duplicates can multiply rows

A merge does not automatically make a key unique. If one left-side key matches three rows on the right, expanding the merged column can produce three output rows for that left-side record. If the key occurs twice on the left and three times on the right, the result can contain six pairings for that key. This is expected many-to-many behavior, not necessarily a Power Query error.

Before merging, check whether the lookup side is supposed to have one row per key. If so, remove duplicates only after deciding which record should be retained, or group and summarize the source to one row per key. Simply removing duplicates can conceal meaningful differences. If a row count increases unexpectedly after expansion, inspect duplicate key values in both queries and decide whether the multiplicity reflects genuine relationships or dirty data.

Unmatched rows also deserve attention. For a Left Outer merge, expand the right-side field and filter for nulls to review left records with no counterpart. For a Full Outer merge, nulls on one side indicate rows that exist only in the other table. Null keys and blank-looking values can behave differently depending on how the source represents them, so standardize and test these values rather than assuming they are equivalent.

Merge versus lookup, append, and relationships

A Power Query merge is a data-preparation operation: it combines data during query evaluation and can be refreshed when its sources change. It differs from worksheet lookup functions, which calculate results in cells, and from the Data Model’s table relationships, which let PivotTables and measures work across related tables without physically expanding one query into another. Choose a merge when the output itself needs columns from both sources; consider a relationship when you want to preserve separate tables for analysis.

Append is the other common Combine operation. It adds rows, matching columns by their names and filling absent columns with nulls; it does not match records by a key. For example, merging January sales with a product catalog adds product attributes to sales rows, whereas appending January sales to February sales creates a longer sales table.

Power Query join kinds in M

Power Query records merge operations in its M language. A generated step uses the Table.NestedJoin function, with a JoinKind value specifying the behavior. For example, the interface’s Left Outer choice corresponds to JoinKind.LeftOuter; other common values include JoinKind.Inner, JoinKind.FullOuter, JoinKind.LeftAnti, JoinKind.RightOuter, and JoinKind.RightAnti. You do not need to edit M to use the Merge dialog, but recognizing these names can help when reading an existing query or troubleshooting an advanced transformation.

The nested result is intentional: each source row can have zero, one, or several matches, so Power Query first stores the matches as a table within a cell-like field. Expanding that field makes the chosen columns visible and can increase the number of rows when multiple matches exist. If you want to keep the matched records grouped rather than expand them, the nested column can remain in the query.

Frequently Asked Questions

What is the default join kind in Power Query?
Merge Queries defaults to Left Outer. It keeps all rows from the first table and includes matching data from the second, with nulls where no match is found.

Which join keeps only matching rows?
Choose Inner. It returns records that have a key match in both tables and excludes unmatched records from either side.

How do I find rows that do not match?
Use Left Anti to find unmatched rows from the first table or Right Anti for unmatched rows from the second. Check which query occupies each side of the Merge dialog before choosing.

Why did a merge create more rows than the first table?
The second table may contain multiple rows for a key, so expanding its matches repeats the corresponding first-table row. Check key uniqueness and confirm whether those multiple matches are expected.

Can I merge on two columns?
Yes. Select both columns in each preview, in the same order and with compatible data types. A row matches only when the selected key combination corresponds on both sides.

Should I use Merge Queries or Merge Queries as New?
Use Merge Queries to add the merge step to the current query. Use Merge Queries as New when you want a separate output query and want to leave the original queries unchanged.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.