Excel Power Query Merge Table Types and When to Use Them

How-to basics · Updated November 19, 2026

Excel Power Query offers six merge join types: Left outer, Right outer, Full outer, Inner, Left anti, and Right anti. Choose one according to which rows you want to keep from the two tables; a merge matches records using one or more columns, then lets you add selected columns from the matching table.

What a Power Query merge does

A merge joins two queries by comparing key columns, much like a database join or a lookup. For example, suppose an Orders query has OrderID and CustomerID, while a Customers query has CustomerID, CustomerName, and Region. Merging on CustomerID can attach customer details to the order rows. The two inputs remain separate queries; the merged result is a new query or a change to the current query, depending on the command you choose.

A merge is not the same as appending. Merge adds related information across columns by matching keys. Append stacks rows from one table under another, usually when the tables have similar columns. If you are combining January and February sales records, append is generally the relevant operation; if you are bringing product names into a sales table using ProductID, merge is the relevant one.

After a merge, Power Query initially adds a column containing nested tables for the matches. Expand that column to choose which fields to bring into the result. The join type determines which input rows survive; expansion determines which fields from the other query appear.

The six merge join types

In the descriptions below, the first table is the primary table selected in the merge dialog, and the second is the table being joined to it. The direction matters for outer and anti joins. The default is typically Left outer.

  • Left outer: Keeps every row from the first table and matching rows from the second. If there is no match, expanded fields from the second table are null. Use this when the first table is your authoritative list, such as all orders, and you want to add customer details where available.
  • Right outer: Keeps every row from the second table and matching rows from the first. This can be useful when the second table is the list you need to preserve, though many users find it easier to swap the tables and use Left outer so the preserved table is on the left.
  • Full outer: Keeps rows from both tables, whether or not they match. Matching rows are combined; unmatched rows have nulls for fields from the other side. Use it to reconcile two lists, such as comparing the products in a current inventory against a supplier catalog.
  • Inner: Keeps only rows with a match in both tables. Use it when unmatched records are not relevant to the output, such as a report limited to orders with a valid customer record.
  • Left anti: Keeps rows from the first table that have no match in the second. It is useful for finding exceptions, such as orders whose CustomerID is missing from the customer master.
  • Right anti: Keeps rows from the second table that have no match in the first. Use it to identify entries present in the second list but absent from the first, such as supplier items not yet found in the inventory list.

Anti joins answer a common question: how do I find unmatched records? Pick the list you want to audit as the preserved side, choose its anti join, and compare it against the reference list. The result is the unmatched side only, rather than a combined table with blank fields.

How matching and expansion work

Power Query compares the selected key columns row by row. A key can be a single field, such as CustomerID, or a combination, such as Country and AccountNumber. When using multiple keys, select corresponding columns in the same order in both previews. Power Query displays small numbered indicators to show that order.

Key values need to represent the same thing in both queries. A number stored as a whole number in one query and as text in another may fail to match or trigger a type issue. Leading or trailing spaces, inconsistent punctuation, different date representations, and inconsistent identifiers can also prevent expected matches. Clean and standardize keys before merging rather than assuming visually similar values are identical.

Once the join runs, use the expand button on the new table column to select fields from the second query. You can usually clear the option to prefix expanded field names with the source query name if the resulting headers become unwieldy. Keep only fields needed for the analysis; carrying every column forward can make a query harder to understand and slower to refresh.

A key does not have to be unique, but duplicates affect the result. If a key appears twice in the first table and three times in the second, the matching combination can produce six rows. That is valid many-to-many join behavior, not necessarily a Power Query error. Check uniqueness when you expect one customer record per customer ID or one product record per product code.

Choosing the right type for your task

Start by asking which rows must remain in the result. If all rows from your main dataset should stay, use Left outer. If only confirmed matches belong in the output, use Inner. If the purpose is to compare two lists and retain everything from both, use Full outer. If you are looking for missing references, choose the anti join for the list whose exceptions you want to see.

For example, take a Transactions table and a Stores table. A Left outer merge from Transactions to Stores preserves every transaction and adds the store name where the StoreID matches. An Inner merge excludes transactions with unknown StoreIDs. A Left anti merge returns only transactions with no matching store. A Full outer merge is more suitable for a reconciliation that also needs to reveal Stores with no transaction rows.

Think about table direction before selecting Right outer or Right anti. Reversing the order of the two tables and choosing the corresponding left-side option can make the logic easier to read. The join results may be equivalent when the sides and join type are switched appropriately, but the result column layout and later query steps can differ.

Steps to merge queries in Excel

These steps describe Excel for Microsoft 365 on Windows. Load or create both tables as Power Query queries first. Then open the Power Query Editor, and use the merge command on its Home tab.

  1. In Excel, select a cell in a source table and choose Data > From Table/Range if it is not already a query. Confirm the table has headers and sensible column types.
  2. In Power Query Editor, select Home > Merge Queries. Choose Merge Queries as New if you want a separate result query and want to leave the original query unchanged.
  3. Choose the second query from the lower drop-down list. Click the matching key column in each preview. For a multi-column key, select the corresponding columns in the same order.
  4. Choose the join kind, such as Left outer or Left anti, and review the match preview. The preview is a useful check, but it does not replace validating the full result.
  5. Select OK. For a standard merge, expand the new nested-table column and select the fields to include. Rename the query or columns if needed.
  6. Choose Home > Close & Load to return the result to Excel, or use Close & Load To to choose a destination and whether to create a table or connection.

On Excel for Mac, Power Query support and available connectors or editor options can differ by Microsoft 365 build. The general idea is the same, but the ribbon labels and feature availability may not match the Windows steps exactly. Excel for the web also does not provide the same full Power Query authoring experience as desktop Excel.

Practical example: matching orders to customers

Imagine an Orders query with 1,000 rows and a Customers query with 120 unique CustomerID values. A Left outer merge on CustomerID should keep 1,000 order rows if each order has at most one customer match. After expanding CustomerName, any order with an unknown ID still remains, with a null CustomerName. A Left anti merge instead returns just those orders with no customer match, making it a useful quality-control query.

If the Customers query accidentally contains two rows for CustomerID 42, every order for customer 42 can appear twice after expansion. Before changing the join type to hide the extra rows, inspect why the customer table has duplicates. You may need to correct the source, group or deduplicate records using a defensible rule, or add another key such as an effective date. Removing duplicates arbitrarily can discard meaningful information.

Common problems and how to diagnose them

  • Fewer matches than expected: Check data types, leading zeros, spaces, punctuation, and inconsistent casing. For identifiers, converting to text may preserve leading zeros that would disappear if treated as numbers. Apply the same cleanup steps to both queries.
  • More output rows than expected: Check for duplicate keys on either side. A merge does not automatically choose one match when several rows share the key; each valid match can contribute to the expanded output.
  • Nulls after expansion: A null can mean that the join key had no match, or that the matched source field itself was blank. Use an anti join to isolate unmatched keys and inspect the source values separately.
  • Wrong records appear to match: A key may not be sufficiently specific. For example, merging on LastName alone can connect unrelated people with the same surname. Prefer a stable unique ID, or use a carefully chosen combination of fields.
  • Refresh is slow: Reduce unnecessary columns and rows before merging, and avoid expanding fields you do not use. Large sources, complex transformations, and many-to-many matches can increase refresh time and output size.

Case handling can be a source of confusion when text keys differ only by letter case. Do not rely on a particular comparison behavior if case is important to your data. Normalize both keys consistently, for example by applying the same uppercase or lowercase transformation, and test representative values. Likewise, distinguish true null values from empty strings and whitespace-only values; they may look alike in a worksheet but are not necessarily the same source value.

Fuzzy matching and alternatives

The merge dialog includes a fuzzy matching option for supported text comparisons. It can help with approximate names, such as matching a supplier label with a minor spelling variation, but it is not a substitute for a reliable key. Similarity thresholds and other options influence which candidates are considered; a result can be plausible and still wrong. Review uncertain matches, and use stable IDs wherever possible. Fuzzy matching is not intended for numeric identifiers or precise financial records.

If your goal is to stack same-shaped datasets, use Append Queries rather than a merge. If you only need to return one value from a table in a worksheet, a lookup formula such as XLOOKUP may be simpler, where available in Microsoft 365 or Excel 2021 and later. Power Query is often preferable for repeatable imports and cleanup because the steps can refresh when the source data changes. Choose based on whether you need a reusable transformation, a worksheet-level lookup, or a row-wise combination of datasets.

Frequently Asked Questions

What is the default merge type in Power Query?
Left outer is typically selected by default. It keeps every row from the first table and adds matches from the second, leaving nulls for unmatched second-table fields after expansion.

Which merge type shows only records that match?
Choose Inner. It returns rows with matching key values in both tables and excludes unmatched rows 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. Confirm the table order in the merge dialog before choosing.

Why did a merge create duplicate rows?
At least one matching key likely appears more than once in one or both tables. Multiple matches produce multiple joined rows. Check key uniqueness and decide how repeated source records should be handled.

Should I use merge or append?
Merge matches records across tables and brings related fields into columns. Append stacks one table’s rows beneath another. Use merge for related records and append for datasets with corresponding columns.

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.