To merge data in Excel Power Query, use the Merge Queries command on the Home tab (or Add Column > Merge Queries), select the two tables you want to combine, choose the matching columns, and pick a join kind (like Left Outer or Inner). After merging, expand the resulting column to bring in the fields you need. This creates a new query that refreshes automatically when your source data changes.
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 different from appending: append stacks rows (e.g., two monthly sales files), while merge joins columns based on a common key, similar to a database JOIN. For example, you have a Customers table with CustomerID and a Orders table with CustomerID and OrderAmount. A merge lets you add customer names to each order, without copying data manually.
The key advantage over VLOOKUP or XLOOKUP is that Power Query stores the merge steps as a query. When the source files update, you just click Refresh (or set auto-refresh), and the merged output updates. It also handles large datasets more efficiently and lets you combine data from different sources (Excel files, CSV, SQL databases, web pages) in one query.
Step-by-Step: How to Merge Two Queries
Assume you have two Excel tables: Products (columns: ProductID, ProductName) and Sales (columns: SaleID, ProductID, Quantity). You want a list of sales with product names.
- Load both tables into Power Query: select each table, go to Data > From Table/Range (or From Sheet in newer versions). This opens the Power Query Editor with two queries listed on the left.
- Select the Sales query (the one you want to be the primary table).
- Go to Home > Merge Queries (or Merge Queries as New to keep the original).
- In the Merge dialog, choose Sales as the top table and Products as the bottom table.
- Click on the
ProductIDcolumn in both tables to select them as the matching keys. Hold Ctrl to select multiple columns if needed. - Choose a join kind from the dropdown. For this example, Left Outer keeps all sales rows and matches product info where available.
- Click OK. A new column named Products appears in the Sales query, containing tables.
- Click the expand icon (double arrow) in the header of that column. Uncheck “Use original column name as prefix” if you don’t want it, then select the columns you want (e.g., ProductName). Click OK.
- Click Close & Load to load the merged data back into a worksheet, or Close & Load To to choose a destination.
On Mac, the ribbon path is similar: Data > From Table/Range, then Home > Merge Queries. Excel for the web does not have Power Query; you would need to use the desktop version.
Choosing the Right Join Kind
The join kind determines which rows appear in the merged result. Here are the six options and when to use them:
- Left Outer – all rows from the first (left) table, plus matches from the second. Use when you want to keep every record from your primary table, even if some have no match.
- Right Outer – all rows from the second (right) table, plus matches from the first. Use when the second table is the primary.
- Full Outer – all rows from both tables, with nulls where no match exists. Use to see all records from both sides.
- Inner – only rows that have a match in both tables. Use when you only want complete records.
- Left Anti – rows from the first table that have no match in the second. Useful for finding missing records (e.g., products with no sales).
- Right Anti – rows from the second table that have no match in the first.
For most merges, Left Outer is the default and works well. If you need to identify unmatched records, Anti joins are your friend. For example, to find customers who haven’t ordered, merge Customers with Orders using Left Anti on CustomerID.
Common Problems and How to Fix Them
Merging often fails or gives unexpected results due to data type mismatches. The most common issue: the key columns have different data types. For instance, CustomerID in one table is text (“001”) and in the other is a number (1). Power Query will not match them. Fix by selecting the column, going to Home > Data Type, and setting both to the same type (e.g., Text).
Another frequent problem is duplicate keys. If the right table has multiple rows for the same key, the merge will produce multiple rows in the result (a one-to-many relationship). That’s often intended, but if you expected a single match, you’ll see duplicates. Check your source data for duplicates before merging.
If the expand icon shows “Table” but you can’t expand, it might be because the merge created a column with errors. Look for rows with “Error” and investigate the source. Also, ensure you have the correct columns selected as keys—clicking the wrong column will produce no matches.
Finally, if you merge two queries that come from the same source table, you might get a circular reference warning. Avoid merging a query with itself directly; instead, create a duplicate of the query first.
Power Query Merge vs. VLOOKUP vs. XLOOKUP
Many users wonder when to use Power Query merge instead of worksheet formulas. Here’s a quick comparison:
- VLOOKUP/XLOOKUP – good for quick, one-off lookups in the same workbook. They are formulas that recalculate when data changes, but they don’t handle large datasets well and can be slow with thousands of rows.
- Power Query Merge – best for repeated merges, large data, or combining data from multiple files. It creates a query that you can refresh, and it doesn’t bloat the workbook with formulas. It also allows complex joins (like anti joins) that formulas can’t do easily.
- PivotTable relationships – if you just need to analyze data in a PivotTable, you can create relationships between tables without merging. But that only works within a PivotTable, not for a standalone dataset.
For example, if you have a 100,000-row sales table and a 5,000-row product table, a VLOOKUP might freeze your workbook. Power Query handles it smoothly and loads only the merged result.
Merging Multiple Tables or Files
Sometimes you need to merge more than two tables. Power Query doesn’t have a direct “merge all” button, but you can chain merges: merge the first two, then merge the result with the third, and so on. Alternatively, use Merge Queries as New to keep intermediate steps.
If you have multiple files in a folder (e.g., monthly sales reports), you can use Data > Get Data > From File > From Folder. Power Query will combine all files into one table, and then you can merge that with a lookup table. This is a huge time-saver for recurring reports.
Refreshing and Managing Merged Queries
Once you load a merged query to a worksheet, it becomes a table with a connection. To update it with new source data, click Data > Refresh All (or right-click the table and choose Refresh). You can also set automatic refresh by going to Data > Queries & Connections, right-click the query, select Properties, and check “Refresh data when opening the file.”
If you need to change the merge (e.g., add a column to the right table), go to Data > Queries & Connections, double-click the query to open the Power Query Editor, and modify the merge step. The changes will flow through when you close and load again.
Frequently Asked Questions
Can I merge two tables with different column names? Yes, you can select any pair of columns as keys, regardless of their header names. Just click the columns that contain matching values.
Why does my merge return “Error” values? This usually happens when the key columns have different data types or when the right table has nulls in the key column. Check the data types and clean the data before merging.
How do I merge data from two different Excel files? In the Power Query Editor, go to Home > New Source > Excel to add the second file as a query, then merge as described. You don’t need to open both files in Excel first.
Is there a way to merge without loading the queries to the worksheet? Yes, you can create the merge as a “connection only” query. In the Load To dialog, choose “Only create connection.” The merged data stays in Power Query and can be used by other queries or PivotTables.
Can I merge based on multiple columns? Absolutely. In the Merge dialog, hold Ctrl and click multiple columns in each table. For example, merge on both Year and Month to combine sales data.
What’s the difference between Merge and Append? Merge joins columns horizontally (like a JOIN), while Append stacks rows vertically (like UNION ALL). Use Append when you have the same columns in multiple tables and want to combine them into one long list.
Related
- Excel Normalize Text: Clean Data Fast
- Excel Normalize Data Range: 3 Easy Methods
- Excel Name Manager Dynamic Range Explained
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- PowerPoint Table Diagonal Split Cell: Practical MethodsPowerPoint does not provide a true diagonal split that turns one table cell into two independently editable cells.…
- Google Sheets COUNTIF With Multiple Criteria ExplainedGoogle Sheets’ COUNTIF function accepts one condition; to count cells that meet multiple conditions at the same time,…
- How to Find and Clear a Circular Reference in ExcelTo clear a circular reference in Excel, find the flagged cell using Formulas > Error Checking > Circular…
- Group Rows in Excel: Steps, Outlines, and FixesTo group rows in Excel for Microsoft 365 on Windows, select the rows, then choose Data > Outline…
- Excel Column Merge: Combine Cells or Join Text in Excel?In Excel, “merge columns” can mean either joining cells visually or combining the text they contain. To make…
- Excel Scatter Plot: How to Swap X and Y AxesTo swap the X and Y data in an Excel scatter plot, select the chart, choose Chart Design…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.