How to Merge Two Tables in Excel Using Power Query

How-to basics · Updated November 20, 2026

To merge two tables in Excel with Power Query, load each table into Power Query, choose Home > Merge Queries, select the matching columns and a join type, then expand the resulting column to bring in the fields you need. The merge matches related rows using key values, so check that both tables use compatible, consistently formatted keys before loading the result back to Excel.

What merging means in Power Query

A Power Query merge combines information from two tables by matching values in one or more columns. It is similar to a database join or a lookup, but the result is a query you can refresh when the source tables change. For example, a sales table might contain a ProductID and units sold, while a products table contains each ProductID, product name and category. Merging lets you add product details to the sales rows.

Merging is different from appending. A merge adds columns based on matching records; an append stacks rows from tables with similar columns. If you have January and February sales tables and want one longer list of transactions, append them. If you want to add a customer’s region to each order by matching CustomerID, merge them.

The original Excel tables are not rewritten by a merge. Power Query creates a query result, which you can load to a worksheet or the Data Model. This separation is useful: you can retain the source data and repeat the same transformation after new records are added.

Prepare both tables before merging

Power Query works most predictably when each source is an Excel Table with a header row. Select a cell in the source range and choose Insert > Table, confirm the range, and check My table has headers if the first row contains field names. Give each table a clear name from Table Design > Table Name, such as Sales and Products. Descriptive names make the query list easier to understand.

Decide which field, or combination of fields, identifies a record in each table. In the example, ProductID is the key. The lookup table should normally have one row per ProductID. If a key appears several times in the second table, one sales row can match several product rows, increasing the number of output rows. That may be correct for a one-to-many relationship, but it is often an accidental duplicate.

Before loading, check for issues that make visually similar keys fail to match:

  • Make the key columns the same data type in both queries, such as text in both or whole number in both.
  • Check for extra spaces, inconsistent capitalization where relevant, blank values and spelling differences.
  • Preserve leading zeros when they are part of an identifier. A code such as 0042 should generally be treated as text rather than a number.
  • Confirm that the chosen key identifies the same kind of thing in both tables. A customer number should not be matched to an order number simply because both contain numbers.

Load the tables into Power Query

In Excel for Microsoft 365 on Windows, click inside the first table and choose Data > From Table/Range. The Power Query Editor opens with that table as a query. Check that the first row is being used as headers and that the key column has the intended data type. If needed, use the data-type control beside the column name to change it.

Choose Home > Close & Load To if you want to set the initial loading destination, or use the editor’s close-and-load option to create a worksheet result. Then repeat Data > From Table/Range for the second Excel Table. You need both tables to appear as queries in the Power Query Editor before you can merge them. You can also create a query from a selected table through the Data tab’s Get & Transform Data commands; ribbon wording can vary slightly between Microsoft 365 builds.

Give each query a useful name in the query settings pane. Names such as Sales and Products are easier to recognize in the merge dialog than generic names. It is usually simplest to keep the source queries as connection-only queries or otherwise avoid loading intermediate copies to worksheets if you only need the final merged result.

Merge the queries step by step

  1. Open the query that should form the main row set. For the example, select Sales in the Power Query Editor.
  2. Choose Home > Merge Queries. Select Merge Queries as New if you want a separate output query and want to leave the Sales query unchanged. Choose Merge Queries to add the merge step to the query currently selected.
  3. In the merge dialog, choose the second query, such as Products, from the lower table list. Click the matching key column in the top preview and then the corresponding column in the lower preview. The selected columns are highlighted and numbered.
  4. If the relationship uses multiple fields, hold Ctrl on Windows while selecting each additional column in the same order in both previews. The order must correspond: for example, select Region and then Account in each table, not Account and then Region in one of them.
  5. Choose the join kind, review the preview and select OK. Power Query adds a column containing nested matching rows from the second query.
  6. In the new column header, select the expand icon, usually shown as two arrows. Select the fields to bring into the result, and decide whether to keep the source column name as a prefix. Clear fields you do not need to keep the output manageable.
  7. Check the resulting columns and row count, then choose Home > Close & Load or Close & Load To to send the result to Excel.

For instance, after merging Sales with Products on ProductID, expanding ProductName and Category adds those two product fields to each sales record. The nested merge column itself is not a finished, flat table; expanding it is what exposes the selected fields as ordinary columns.

Choose the right join type

The join kind determines which rows remain in the merged output. The default is usually Left Outer, which keeps every row from the first, top query and adds matching rows from the second query where available. This suits the common task of enriching a transaction list with lookup details.

  • Left Outer: keep all rows from the first query and matching rows from the second. Unmatched second-table fields appear as null.
  • Right Outer: keep all rows from the second query and matching rows from the first.
  • Full Outer: keep rows from both queries, whether or not they have a match.
  • Inner: keep only rows with a match in both queries.
  • Left Anti: keep rows from the first query that have no match in the second. This is useful for finding IDs missing from a lookup table.
  • Right Anti: keep rows from the second query that have no match in the first.

Join direction matters. If Sales is the first query and you choose Left Outer, all sales rows remain. Switching the query order and choosing Left Outer instead keeps all rows from the other table. Think about which table defines the rows you must preserve before selecting a join.

Understand matches, duplicates and row counts

A merge does not guarantee one output row per row in the first table. If a Sales row has one matching ProductID in Products, expanding the result usually adds one row of product details. If Products has three rows with that ProductID, expanding produces three combinations for that Sales row. This is a normal consequence of a one-to-many match, not necessarily a Power Query error.

When you expect a unique lookup but see more rows than expected, inspect the second table for duplicate keys. You can remove duplicates in Power Query with the key column selected and Home > Remove Rows > Remove Duplicates, but do so only if duplicate records truly are redundant. If the rows represent distinct versions, locations or effective dates, removing them could discard meaningful information. In that case, refine the match using additional key columns or prepare the lookup table according to the business rule.

Unmatched rows are another useful diagnostic. With a Left Outer join, an expanded field from the second table is null when no match exists. Filter that field to identify missing lookup records, then check whether the cause is a missing key, a type mismatch or inconsistent text. An Inner join would hide those unmatched first-table rows, so it is not the best choice when you need to audit missing matches.

Refresh and maintain the merged result

After the query is loaded, changes to the source tables do not necessarily appear in the result immediately. Use Data > Refresh All to rerun workbook queries, or right-click the query result and choose Refresh. If new records are added inside an Excel Table, the table expands to include them, and the query can read them on refresh. Data entered outside the table range is not automatically part of that table, so confirm that new rows were added within the table.

Power Query records transformation steps, such as selecting a join type and expanding columns. You can edit these later by opening the query from Data > Queries & Connections and choosing to edit it. If you rename a source table or change its columns, review the query steps: later steps may refer to the old name or field. A refresh error can therefore indicate a changed source structure, not just a problem with the merge key.

For workbooks shared with others, document which query is the final output and where its source tables are located. Refreshing can also depend on access to external files or databases used by a query. Keep the source files available and ensure users have the needed permissions; a workbook cannot refresh data that its user cannot access.

Power Query on Mac and in Excel for the web

The directions above use Excel for Microsoft 365 on Windows. Microsoft 365 for Mac includes Power Query capabilities, but available connectors and authoring features can differ by platform and release, and ribbon labels may not be identical. If the merge commands or a particular source connector are unavailable on a Mac, open the workbook in desktop Excel for Windows to author or adjust that query.

Excel for the web is not a full substitute for the desktop Power Query Editor for every query-authoring task. Depending on the workbook and available web features, you may be able to work with or refresh supported query results, but use desktop Excel when you need the complete merge workflow or cannot find the required editing controls. Before sharing a workbook, test that the intended users can refresh it in their version of Excel.

Frequently Asked Questions

What is the difference between Merge and Append in Power Query?

Merge matches rows using one or more columns and adds fields from another table. Append stacks rows from two or more tables with corresponding columns. Use Merge to add a product name to sales by ProductID; use Append to combine separate monthly sales lists.

Why does my merge return null values?

In a Left Outer merge, nulls in expanded fields usually mean the first-table key did not find a match in the second table. Compare data types, spaces, spelling, leading zeros and blank keys. Also confirm that you selected the intended columns in both previews.

Why did merging increase the number of rows?

The second query may contain multiple rows for a key, so one first-table row matches several records. Check for duplicate keys and decide whether the relationship is genuinely one-to-many. Do not remove duplicates unless the repeated rows are actually redundant.

Can I merge tables using more than one column?

Yes. In the merge dialog, select each matching column in both previews, using Ctrl-click on Windows for additional columns. Select the fields in the same order in both tables. A multi-column key is useful when no single field uniquely identifies a record.

Will the merged table update when I change the source data?

The query result updates when you refresh it, for example with Data > Refresh All. It does not automatically rewrite itself every time a source value changes. Make sure added rows are inside the source Excel Table and that the query still points to the correct source.

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.