Power Query Merge: How to Join Tables in Microsoft Excel

Power Query Merge joins rows from two queries using one or more matching columns. In Excel, open the Power Query Editor, choose Home > Merge Queries, select the tables and join type, then expand the matching data you want to use.

What a Power Query merge does

A merge combines information from two tables by comparing key columns, much like a database join. For example, an orders table might contain a product ID but no product description; a product table can supply that description when the IDs match. The result is a query that keeps data from the first table and adds related data from the second.

In the editor, a merge initially creates a column containing nested tables. That column represents the matching rows from the second query for each row in the first. You normally expand it to select the fields you want to add. This means a merge is not simply a command to place two tables side by side: the chosen keys and join type determine which rows are retained and how many matches appear.

The operation is repeatable. Power Query stores the steps in the query, so refreshing it can apply the same merge to updated source data. This is useful for recurring reports where the inputs change but the relationship between them remains the same.

A practical example

Suppose a sales table has columns OrderID, ProductID, Quantity, and OrderDate. A separate product table has ProductID, ProductName, and Category. Merge the sales query with the product query on ProductID, then expand ProductName and Category. The resulting sales data can show both the transaction details and the product information.

For this example, sales is the first, or left, table and products is the second, or right, table. A left outer join keeps every sales row, including any sale whose ProductID is missing from the product list. The unmatched row remains, but the expanded product fields are blank or null. That is often preferable to silently losing a transaction.

If a ProductID appears twice in the product table, a single sales row can match both product rows. Expanding the merge then produces two output rows for that sale. This is not necessarily a Power Query error; it reflects the fact that the key does not identify one unique product record.

How to merge queries in Excel

Make each source range an Excel table, or connect to the relevant data sources. Give the tables clear names and check that their column headers are meaningful.
For a worksheet table, select a cell in it and choose Data > From Table/Range. Repeat for the other table. Power Query opens and creates a query for each source.
In the Power Query Editor, choose Home > Merge Queries. Choose Merge Queries to add the operation to the current query, or Merge Queries as New to create a separate result query and leave the originals unchanged.
In the merge window, select the second query from the list. Click the key column in the preview of the first table and the corresponding key column in the second. For a multi-column key, select the columns in the same order in both previews.
Choose a join kind, review the preview and any match-count information shown, and select OK.
In the resulting query, use the expand icon on the merged column to choose fields from the second table. Clear fields you do not need; remove the column-name prefix option if you want shorter output headers.
Choose Home > Close & Load, or use Close & Load To when you want to control where the results go.

If the queries already exist, you can open the editor from Excel’s query tools and start at the merge step. Button placement can vary slightly between Microsoft 365 builds. Excel for Mac has Power Query features, but available connectors and some editor details can differ by version; Excel for the web does not provide the full desktop editor for authoring this merge.

Choosing the right join kind

The first query is treated as the left table and the second as the right table. The join kind determines which records survive the comparison:

Left outer: Keeps every row in the first table and matching rows from the second. Unmatched second-table fields are null. This is a common choice when enriching a main list.
Right outer: Keeps every row in the second table and matching rows from the first. Use it when the second query is the list whose full set of records must remain.
Full outer: Keeps rows from both tables, matching records where possible. It is useful for comparing lists or finding records that exist on only one side.
Inner: Keeps only rows with a match in both tables. Unmatched records are excluded.
Left anti: Keeps rows from the first table that have no match in the second. For example, use it to find sales product IDs missing from a product catalog.
Right anti: Keeps rows from the second table that have no match in the first, such as catalog items that have never appeared in sales.

Choose based on which records you need to retain, not on which option sounds most comprehensive. An inner join can make a result look tidy while dropping important unmatched records, so use an anti join or a full outer join when auditing the gaps matters.

Preparing reliable matching columns

A merge depends on the values in its key columns. Before joining, confirm that the columns represent the same kind of identifier and use compatible data types. A numeric ID in one query and a text ID in another may fail to match as expected, even when they look alike in the preview. In the editor, select a column and check its data type indicator; change the type when appropriate.

Also look for differences in spelling, spaces, punctuation, capitalization, and leading zeroes. The codes 00125 and 125 can represent different identifiers if the zeroes are meaningful. If one source adds extra spaces or inconsistent formatting, clean the values before merging, rather than assuming Power Query will infer that they mean the same thing.

When the relationship uses more than one field, such as Country and AccountNumber, select both columns in each preview in matching order. A match then requires the selected key values to correspond across the chosen fields. Avoid joining on a descriptive field such as a person’s name when a stable unique ID is available.

The merge dialog also offers fuzzy matching for certain text comparisons. It can help identify approximate text matches, such as minor variations in names, but it is not a substitute for a trustworthy identifier. Review approximate matches carefully: similar text can refer to different entities, and a fuzzy result may need manual validation.

Expanding results and understanding duplicates

After the merge, expanding the nested column controls which fields from the second query appear in the output. If the second query contains many fields, select only the ones the report needs. This keeps the result easier to read and reduces unnecessary columns. The option to add a prefix can help distinguish fields with similar names, such as OrderDate and a date field from the lookup table.

Check whether the key is unique in the lookup table. If every ProductID appears exactly once there, each sales row should match no more than one product row. If keys repeat, Power Query can return multiple matching records, and expansion can increase the row count. To diagnose an unexpected increase, group or sort the lookup data by key and investigate repeated values before merging.

Conversely, fewer rows than expected can mean the selected join is filtering unmatched records, the wrong columns were selected, or key values differ in type or formatting. Compare the row counts before and after the merge, and use a left anti join to isolate first-table records that have no counterpart.

Merge versus append

Merge and append solve different problems. Merge adds columns by matching records on key values; append stacks rows from one table beneath rows from another. To combine January and February transactions that have the same column structure, append the monthly queries. To add a customer region to each transaction by CustomerID, merge the transactions with the customer table.

Column names and data types matter for appending: Power Query aligns fields by column name, and missing fields can result in null values. A merge instead needs a meaningful relationship between key columns. Choosing the wrong operation can produce a result with a plausible shape but the wrong meaning, so decide whether you are enriching existing records or adding more records to a list.

Refreshing and loading a merged query

Power Query records the merge as a transformation step. When the source data changes, use Data > Refresh All in Excel to update queries and their dependents. The results can be loaded to a worksheet table or, depending on the query and workbook setup, kept as a connection or added to the Data Model.

Do not treat cells in a loaded query table as the durable place to make corrections. A refresh can replace the displayed results with the output of the saved query steps. Correct the source data or add a transformation in Power Query instead. For repeatable workflows, keep source queries and the final merged query clearly named so that it is easier to trace where the output comes from.

Frequently Asked Questions

Does Power Query Merge add columns or rows?
A merge relates rows and initially adds a nested-table column. Expanding that column adds selected fields from the second query. It can also increase the number of output rows when a key has multiple matches.

Why is my merge returning null values?
Nulls in expanded fields usually mean there was no match for that row. Check the join kind, selected key columns, data types, spaces, leading zeroes, and other formatting differences. A left anti join can list first-table rows without a match.

Can I merge on more than one column?
Yes. Select each matching column in both table previews, using the same order on each side. All selected key fields participate in the match, which is useful when no single column uniquely identifies a record.

What is the difference between Merge Queries and Merge Queries as New?
Merge Queries adds the merge step to the current query. Merge Queries as New creates a separate query for the merged result, which can preserve the original queries as independent inputs for other work.

Will a Power Query merge update automatically when the source changes?
The merge is recalculated when the query refreshes, not necessarily the instant a source cell changes. Use Data > Refresh All, or the refresh controls for the relevant query, to update the loaded result.

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.