Excel Power Query Joins: How to Combine Tables Correctly

How-to basics · Updated November 21, 2026

To join tables in Excel Power Query, load each table as a query, choose Home > Merge Queries, select matching key columns, choose a join kind, and expand the merged column to bring fields into the result. A merge matches rows by shared values in one or more columns; it does not simply place two tables side by side by row position.

What joining tables in Power Query means

Power Query is Excel’s tool for importing, shaping, and refreshing data. A join, called a merge in its interface, connects two queries through a common key such as CustomerID, ProductCode, or OrderNumber. The result is a new query containing columns from the first table and a nested-table column representing matches from the second.

For example, a sales table might contain OrderID, CustomerID, and Amount, while a customer table contains CustomerID, Name, and Region. Merging on CustomerID lets you add the customer’s name and region to each sale. The shared column is the key: Power Query compares its values and associates rows that match.

The distinction matters because a merge can return more or fewer rows than either source. If a key appears several times in the second table, one source row can match multiple rows. If there is no match, the result depends on the join kind. Reviewing the key columns and the expected row count before expanding the result helps prevent surprises.

Load both Excel tables and merge them

For data already in the workbook, format each source range as an Excel table and give the tables clear names. Select a cell in the first range and use Insert > Table, or press Ctrl+T on Windows (Command+T on Mac). Confirm that the table has headers, then repeat for the second range. Using named tables makes the source easier to identify and maintain.

  1. Select a cell in a source table and choose Data > From Table/Range. Power Query Editor opens with that table as a query. Repeat for the other table. If the data comes from a file or database, use the appropriate command under Data > Get Data instead.
  2. In Power Query Editor, select the query that should supply the main rows. On the Home tab, choose Merge Queries to add the merge to that query, or Merge Queries as New to create a separate result query while keeping both source queries unchanged.
  3. In the Merge dialog, select the second query from the lower list. Click the matching key column in the preview of the first table, then click its corresponding column in the second table.
  4. Choose a join kind, check that the selected columns are the intended keys, and select OK. Power Query adds a column containing the matching rows from the second query.
  5. Select the expand button in that new column’s header, choose the fields to include, and confirm. Rename columns or remove unnecessary fields, then choose Home > Close & Load to return the result to Excel.

If the match depends on more than one field, select the first key column in each preview, then hold Ctrl on Windows or Command on Mac while selecting the next key in the same order. For instance, a monthly sales table may require both StoreID and Month to identify a record. The number and order of selected columns must agree between the two queries.

Choose the right join kind

The join kind determines which rows remain in the result. The first query selected in the dialog is the left table, and the second is the right table. Power Query offers these standard choices:

  • 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. This is a common choice when enriching a main list.
  • Right outer: Keeps every row from the second table and matching rows from the first. It can be useful when the second query is the list whose records must all be retained.
  • Full outer: Keeps rows from both tables, including unmatched rows from either side. Unavailable fields appear as null.
  • Inner: Keeps only rows with a match in both tables. Unmatched records are excluded.
  • Left anti: Returns rows from the first table that have no match in the second. Use it to find missing reference data, such as products in a sales list that are absent from a product catalogue.
  • Right anti: Returns rows from the second table that have no match in the first.

For a customer list that needs all customers, including those with no orders, use a left outer join with Customers first and Orders second. For a list of customers who actually placed orders, an inner join may be suitable. If you need customers without any orders, use Left anti. The right and left labels are relative to the order of the queries in the merge dialog, so check that order before confirming.

A concrete example: add customer details to orders

Suppose Orders has four rows: order 101 for customer C01, order 102 for C02, order 103 for C01, and order 104 for C04. Customers has one row each for C01, C02, and C03, with names and regions. Merge Orders with Customers on CustomerID using a left outer join, then expand Name and Region.

The output retains all four orders. Orders 101 and 103 receive C01’s details, order 102 receives C02’s, and order 104 remains with null customer details because C04 is missing from Customers. C03 does not appear, because it has no matching order and the left outer join preserves Orders rather than every customer. Switching to a full outer join would also bring in C03 as an unmatched customer record.

This example also shows why key uniqueness matters. If Customers accidentally contains two C01 rows, each C01 order can match both rows. Expanding the nested results then produces two output rows for each such order. A merge does not automatically decide which duplicate is correct; clean or deduplicate the lookup table first if each customer should have exactly one record.

Prepare keys and expand the merged column

Matching values need compatible data types and consistent representations. A number stored as 123 is not necessarily the same key as text stored as 123. Spaces, inconsistent capitalization, leading zeros, and different date formats can also explain why an apparent match fails. In Power Query, select the key column and set its type through the data-type control on the Transform or Home tab. Choose a type that preserves the identifier as intended.

Identifiers with leading zeros, such as 00418, are often best kept as text. Converting them to a number can turn 00418 into 418, removing information that may be essential to matching. If spaces are the problem, use Transform > Format > Trim on text columns. Apply the same cleanup to both queries, and be careful about changing case or removing punctuation where those differences might be meaningful.

After a merge, the new column usually shows Table values. Its expand control opens a list of fields from the second query. Select only what you need: expanding every field can make the final table wide and harder to maintain. You can clear the option to use the original column name as a prefix if you prefer shorter headers, but keep prefixes when names such as Status exist in both sources and could become ambiguous.

Refresh, load, and preserve the source data

A merge is a sequence of query steps, not a one-time copy-and-paste operation. When source data changes, use Data > Refresh All in Excel to refresh the query results. If new rows are added to a properly defined Excel table, the table can expand to include them, and the query can read the updated source on refresh. Confirm that each source query points to the intended table or file.

Close & Load places the result in a worksheet by default, while Close & Load To lets you choose a destination and other available loading options. Depending on the workflow, you can load a result as a table or create a connection for use by another query or the workbook’s data model. Keeping staging queries as connections rather than loading every intermediate step can reduce clutter in a workbook.

Power Query preserves the source data and records transformations as applied steps; it does not rewrite the original tables just because you merge them. That makes it useful for repeatable reporting. Still, save the workbook and check the refreshed output before relying on it, especially when source files, column names, or credentials change.

Common merge problems and how to diagnose them

  • No matches appear: Compare data types, spaces, leading zeros, and the actual key values in both previews. A value that looks identical on screen may be stored differently.
  • The output has more rows than expected: Look for duplicate keys in the second query. Multiple matches are valid join behavior, but may indicate that the lookup table needs deduplication or a more specific multi-column key.
  • Some rows disappear: Check whether you selected Inner instead of Left outer, or whether the table you need to preserve was placed first. Anti joins intentionally return only unmatched rows.
  • Expanded fields contain nulls: Those rows may have no match, or the match may be blocked by a type or formatting mismatch. Null means the joined field has no value for that row; it does not by itself tell you which cause applies.
  • Refresh fails: Check that the source file or table still exists, that column names have not changed, and that any required sign-in or data-source permissions are current.

For fuzzy text matching, the Merge dialog may offer Use fuzzy matching to perform the merge when the selected keys are text. This can help with near matches such as small spelling differences, but it is not a substitute for a reliable ID. Fuzzy matching can associate the wrong records, so inspect the results and adjust the available similarity settings carefully before using them in a report.

Merge versus append, lookups, and other approaches

Merge and append solve different problems. Merge adds columns by matching keys, like a database join. Append stacks rows from one table beneath another, like combining January and February transactions that share the same column structure. In Power Query, append is available through Home > Append Queries or Append Queries as New. Appending does not match customer IDs to add customer details.

A worksheet lookup such as XLOOKUP can also retrieve values from another range, and VLOOKUP or INDEX with MATCH can serve similar purposes in appropriate workbooks. These formulas can be convenient for a small, immediate lookup, but they live in worksheet cells and may need filling or range management. A Power Query merge is often more suitable when the task is a repeatable import-and-clean process, when several columns must be brought across, or when you want refreshable transformation steps.

Power Query is not automatically the best option for every task. For a one-off calculation or a small live worksheet where values should update as cells change, a formula may be simpler. For combining periodic files with consistent schemas, a query can save repeated manual work. Choose based on whether you need a refreshed data-preparation process or a cell-level calculation, and verify that the chosen join preserves the records your analysis requires.

Frequently Asked Questions

Can Power Query join tables from different workbooks?

Yes. Load each workbook or its relevant table as a query, then merge those queries on a shared key. The workbooks do not have to be the same file, but the source paths, access permissions, and column structures must remain usable when you refresh.

Does a Power Query merge change the original Excel tables?

No. A merge creates or modifies a query result; it does not overwrite the source tables. The result changes when you edit query steps or refresh from updated source data.

Why does merging two tables create duplicate rows?

Usually, one or more key values appear multiple times in the second table. Each matching row is returned, so a single row in the first table can expand into several rows. Check whether the second table should have unique keys or whether the merge needs additional key columns.

Can I merge on more than one column?

Yes. In the Merge dialog, select corresponding columns in both previews in the same order, holding Ctrl on Windows or Command on Mac as you select additional columns. Every selected key value must match for the rows to join.

Is merging the same as appending?

No. Merging matches rows through one or more key columns and adds fields from another query. Appending stacks rows from tables with similar columns. Use Merge Queries for a lookup-style join and Append Queries for combining records vertically.

Can I edit Power Query merges in Excel for the web or Mac?

Excel for Microsoft 365 on Windows provides the full desktop authoring workflow described here. Excel for Mac includes Power Query features, but connector and editing support can vary by version and source. Excel for the web does not offer the same full Power Query authoring experience as desktop Excel, so use the desktop app if the merge-editing commands are unavailable.

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.