You merge two tables in Excel Power Query by using the Merge Queries command on the Home tab (or Add Column tab), choosing a join kind, and then expanding the resulting column. This combines rows from two tables based on matching columns, similar to a SQL JOIN, without writing formulas or using VLOOKUP.
What Power Query Merging Actually Does
Power Query is Excel’s built-in data transformation tool, available in Excel 2016 and later (including Microsoft 365). Merging is one of its core features: it takes two separate tables (called “queries”) and combines them into one table based on a common field. Unlike VLOOKUP, which returns a single column, a merge lets you bring in many columns at once, and it handles multiple matches correctly.
For example, you might have a Sales table with columns: OrderID, ProductID, Quantity. And a Products table with: ProductID, ProductName, Price. Merging on ProductID produces a table with all sales rows plus the product name and price for each row.
How to Merge Two Tables Step by Step
Here’s the exact process in Excel for Microsoft 365 on Windows (steps are similar on Mac, but the ribbon is at the top of the screen):
- Load both tables into Power Query: select any cell in the first table, go to Data > From Table/Range (or Data > Get Data > From Other Sources > From Table/Range). Repeat for the second table. Each becomes a query in the Power Query Editor.
- In the Power Query Editor, select the first query (the one you want as the base).
- Go to Home > Merge Queries (or Home > Merge Queries > Merge Queries as New if you want to keep the original unchanged).
- In the dialog, choose the second table from the dropdown at the top.
- Click the column header in the first table, then click the matching column header in the second table. They must be the same data type (e.g., both text or both numbers).
- Select a Join Kind from the six options (explained below).
- Click OK. You’ll see a new column with “Table” in each cell.
- Click the expand icon (double arrow) in the header of that new column. Uncheck “Use original column name as prefix” if you don’t want that, then choose which columns to include.
- Click Close & Load to send the result to a new worksheet, or Close & Load To to choose a location.
On Mac, the ribbon path is Data > From Table/Range and then Home > Merge Queries inside the Query Editor. Excel for the web does not support Power Query merging directly; you’d need to use the desktop version.
Understanding the Six Join Kinds
Power Query offers six join types. The choice determines which rows appear in the result:
- Left Outer – all rows from the first (left) table, plus matching rows from the second. Unmatched rows get nulls in the second table’s columns.
- Right Outer – all rows from the second table, plus matching rows from the first.
- Full Outer – all rows from both tables, with nulls where no match exists.
- Inner – only rows that have a match in both tables.
- Left Anti – rows from the first table that have no match in the second.
- Right Anti – rows from the second table that have no match in the first.
For most merges, Left Outer is what people want because it keeps all your base data and adds extra info. Anti joins are useful for finding missing records, like customers who haven’t ordered.
How to Choose Which Table to Merge From
The order matters. The table you select first is the “left” table, and it determines the row count for left and inner joins. A common mistake is picking the wrong base table, which can duplicate or drop rows.
Rule of thumb: start with the table that has the most rows or the one you want as the final output’s primary structure. For instance, if you have a list of orders and a list of customers, start with orders (each order is one row) and merge customers onto it. If you started with customers and merged orders, you’d get multiple rows per customer (one per order), which might be what you want, but only if you intend to expand the order details.
Common Problems and How to Fix Them
Three issues trip up most users:
- Data type mismatch – if one column is text and the other is number, the merge won’t match. Fix by selecting the column, going to Home > Data Type, and changing both to the same type.
- Extra spaces or hidden characters – leading/trailing spaces cause matches to fail. Use Transform > Format > Trim on both columns before merging.
- Duplicate keys – if the right table has duplicate values in the key column, the merge will produce multiple rows for each match. This is expected behavior but can surprise you. To avoid it, ensure the right table’s key column has unique values, or use a different join type.
If you see “Error” in a cell after expanding, it usually means the data type conversion failed. Click on the error to see the details, then fix the source column’s type.
Merging vs. Appending vs. Lookup
People often confuse merging with other operations:
- Merge – combines columns from two tables based on a key (like a JOIN). This is what this article covers.
- Append – stacks rows from two tables that have the same columns (like UNION ALL). Use Home > Append Queries when you have quarterly sales tables with identical structure.
- Lookup / VLOOKUP – a formula that returns one value from another table. Merge is more flexible and faster for large data, and it doesn’t require the tables to be in the same sheet.
If you only need one column back, VLOOKUP might be simpler. But if you need multiple columns or have more than a few thousand rows, merge is better.
Performance and Data Size Considerations
Power Query merges are efficient because they work in memory and compress data. However, extremely large datasets (millions of rows) can slow down the editor. Tips:
- Filter rows before merging to reduce the number of rows.
- Remove columns you don’t need before merging.
- If both tables are from the same source, consider loading them as a single query using a database join instead.
There’s no hard limit, but Excel’s data model (Power Pivot) can handle larger data than the worksheet grid. If you load the merge result to the data model, you can work with more rows.
Frequently Asked Questions
Can I merge more than two tables?
Yes, but not in one step. You merge two tables, then merge the result with a third table. Each merge creates a new query, and you can chain them. Alternatively, use the Merge Queries as New option to keep intermediate steps.
Why does my merge result have more rows than the first table?
This happens when the second table has duplicate values in the key column. Each duplicate creates a separate row in the result. To avoid this, remove duplicates from the second table’s key column before merging, or use a join kind that filters them out (like Left Anti if you only want unmatched).
What’s the difference between Merge and Join in Power Query?
They are the same thing. “Merge” is the command name in the Power Query editor; “Join” is the database term. The dialog shows “Join Kind” options, so you might see both words used.
Can I merge tables from different workbooks?
Yes. Use Data > Get Data > From File > From Workbook to load each table as a separate query, then merge them. The tables don’t need to be in the same file.
Is there a way to merge without opening the Power Query Editor?
No, merging requires the Power Query Editor. But you can use the Data > Get Data > Combine Queries menu to start a merge from the ribbon without manually loading each table first, though you’ll still end up in the editor.
Will my merged table update automatically when the source data changes?
Yes, if you loaded the source tables via Power Query (e.g., from a table or external source). After you refresh the query (right-click the query in the Queries pane and choose Refresh), the merged result updates. If you pasted data directly into a worksheet and used From Table/Range, refreshing will pick up changes in that table.
Related
- Excel: How to Hide Rows in a Table (3 Ways)
- Excel Split Cells Fast: 4 Ways That Work
- Excel: How to Count X’s in a Column
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Resource List Excel Template: Set Up, Track, and ShareA resource list Excel template is a worksheet for recording what resources are available, where they are, who…
- Word: Make One Page Landscape Without Changing OthersTo make just one page landscape in Microsoft Word for Microsoft 365 on Windows, select the content on…
- Hanging Indent in Microsoft Word: Steps, Uses and FixesTo make a hanging indent in Microsoft Word, select the paragraph or paragraphs, open Home > Paragraph using…
- Can You Use Goal Seek on Multiple Cells in Excel at Once?No. In Microsoft Excel for Microsoft 365 on Windows, Goal Seek changes one input cell to make the…
- How to Flip Bar Order in ExcelTo flip the order of bars in an Excel chart, right-click the category axis, choose Format Axis, then…
- Excel Sales Tracking Template by RegionThe quickest way to track sales by region in Excel is to use a PivotTable on raw sales…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.