Excel Power Query Join Types and When to Use Each

Excel Power Query has six join types for merging tables: Left Outer, Right Outer, Full Outer, Inner, Left Anti, and Right Anti. They determine which matching and unmatched rows remain in the result; choose a type based on which table’s records you need to keep, then expand the merged column to show fields from the other table.

What a Power Query join does

A join connects rows in two tables by comparing one or more columns, called the join keys. In Excel for Microsoft 365 on Windows, open the Power Query Editor, select Home > Combine > Merge Queries, choose the second query, and select the matching key column or columns in each table. The preview shows how the selected join type will match the tables.

For example, imagine an Orders table with OrderID, CustomerID, and Amount, and a Customers table with CustomerID and CustomerName. Merging on CustomerID can add each customer’s name to the corresponding order. The tables do not need to use the same name for their key columns, but the selected columns must represent the same kind of value.

A merge initially adds a column containing nested table values from the second query. Select the expand icon in that column’s header to choose which fields to bring into the result. This distinction matters: the join type decides which rows are retained, while expansion decides which fields from the matched table are displayed.

The six join types

Power Query’s merge dialog lists six standard join kinds. Left and right refer to the position of the tables in the merge dialog, not to worksheet columns or a permanent status assigned to either table.

Left Outer (all from first, matching from second): Keeps every row from the first table and adds matching rows from the second. A first-table row without a match remains, with null values for expanded fields from the second table. This is a common choice when enriching a main list with optional details.
Right Outer (all from second, matching from first): Keeps every row from the second table and adds matching rows from the first. Use it when the second table is the one whose complete set of records must be preserved. It is the left-outer equivalent with the table roles reversed.
Full Outer (all rows from both): Keeps matched rows and unmatched rows from both tables. Where a row has no partner, fields from the missing side are null. It is useful for reconciling lists and finding records that appear in only one source.
Inner (only matching rows): Keeps rows that have a matching key in both tables. Any unmatched row is excluded. Use it when the result should contain only records confirmed by both sources.
Left Anti (rows only in first): Keeps first-table rows that have no match in the second table. It is a direct way to find, for example, orders whose CustomerID is absent from a customer reference list.
Right Anti (rows only in second): Keeps second-table rows that have no match in the first table. Use it to identify records present only in the second source.

Anti joins are often misunderstood: they return the nonmatching rows from one side, not matched rows with a special marker. They are useful for exception reports, while a full outer join is better when you need to inspect unmatched records from both sides in one result.

How to choose the right join

Start by asking which table is the authoritative list whose records must survive. If all rows from that table belong in the output, use an outer join with that table on the preserved side. If only records confirmed by both tables belong, use Inner. If the goal is to list missing or unlinked records, choose the appropriate Anti join.

Adding department names to an employee list? Use Left Outer with Employees first, so employees without a department match are still visible.
Keeping only products with a valid supplier record? Use Inner if unmatched products should be omitted.
Finding IDs in a new extract that are not in the old extract? Put the new extract second and use Right Anti, or reverse the order and use Left Anti.
Comparing two customer lists for differences? Use Full Outer, then examine which side has null fields after expansion.

These examples assume one row per key in the reference table. If the second table contains repeated keys, the result can contain more rows than expected; check for duplicates before deciding that the join type is at fault.

Matching keys and table order

For a reliable merge, check that the selected key columns have compatible data types and comparable values. A number stored as a number may not match the same-looking value stored as text. Leading or trailing spaces, inconsistent capitalization, different date representations, and punctuation can also prevent otherwise plausible matches. Clean or standardize keys before merging when the values are meant to represent the same identifier.

You can match on more than one column. In the merge dialog, select the corresponding columns in the same order in both table previews; use Ctrl while selecting additional columns on Windows. For instance, if an invoice number is unique only within a company, matching on both CompanyID and InvoiceNumber can prevent records from different companies being treated as the same invoice.

Changing which query is first can change the meaning of Left Outer, Left Anti, and their right-side counterparts. It does not change the basic idea of Inner or Full Outer, but it can change the displayed column order and how you interpret the result. Give queries clear names and verify which one is first before confirming a merge.

Duplicates and unexpected row counts

A merge is not automatically a one-to-one lookup. If a key occurs twice in the first table and three times in the second, that key can produce six matched row combinations after the nested results are expanded. This is normal join behavior: each matching row on one side pairs with each matching row on the other.

For example, if a customer appears on two order rows and the customer table accidentally has two rows for that CustomerID, expanding the merge can create four order-customer combinations. The extra rows can inflate sums or counts in later reports. Before merging, decide whether repeated keys are legitimate. If the second table should have one record per key, remove or resolve duplicates there rather than hiding the issue by deleting rows from the merged output.

When the final row count looks wrong, compare counts before and after expansion, inspect the key values responsible for repetition, and check whether either source contains duplicate keys. Also confirm that the join type is intended: Inner can reduce the count by dropping unmatched first-table rows, whereas Left Outer preserves them.

Merge versus append, and fuzzy matching

Merge and append solve different problems. Merge joins tables side by side using related key values, like adding CustomerName to Orders. Append stacks rows from tables with compatible columns, like combining January and February sales into one longer table. In Power Query, the append operation is available separately from Merge in the Combine commands.

Fuzzy matching is also not a seventh standard join type. The merge dialog can offer fuzzy matching for text comparisons, which is intended to find similar rather than exactly identical text values. It can help with names or labels that differ slightly, but it should not replace a dependable identifier when one exists. Review fuzzy results carefully: similar text can refer to different entities, and the matching settings influence what is considered close enough.

Refresh behavior and practical checks

A Power Query merge is a saved transformation, not a one-time worksheet copy. When source data changes, refresh the query to apply the merge steps again. This makes it useful for repeatable reports, but it also means changes to source column names, data types, or file locations may affect refreshes.

Before relying on a merged output, run a few checks:

Confirm the first and second queries and the selected key columns.
Verify that key columns have suitable data types and that values are cleaned consistently.
Look for duplicate keys, especially on the side expected to have one row per identifier.
Expand only the fields needed, and consider renaming columns that would otherwise have ambiguous names.
For reconciliation, use anti joins or a full outer join to investigate missing keys rather than assuming the preview has matched everything.

Power Query is available in Excel for Mac as well, but feature availability and the editor interface can vary by Excel version. The menu paths above describe Excel for Microsoft 365 on Windows; if a Mac build presents different merge options, consult the commands available in its Power Query Editor rather than assuming every Windows feature is present.

Frequently Asked Questions

How many join types does Power Query have?
Power Query’s merge dialog provides six standard types: Left Outer, Right Outer, Full Outer, Inner, Left Anti, and Right Anti. Fuzzy matching is an option for text matching, not an additional standard join type.

Which Power Query join keeps every row from the first table?
Choose Left Outer, labeled all from first, matching from second. It preserves every first-table row and includes second-table fields when a match exists.

What is the difference between Left Anti and Inner?
Inner returns rows with matches on both sides. Left Anti returns only rows from the first table that have no matching key in the second.

Why did a merge create duplicate rows?
One or both tables may contain repeated join keys. When multiple rows match the same key, expansion creates combinations of those matches, so inspect key uniqueness and source data before changing the join.

Can Power Query merge tables using multiple columns?
Yes. Select each corresponding key column in both previews, in the same order. This is useful when no single column uniquely identifies a record.

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.