Power Query Join Tables: A Practical Guide

To join tables in Power Query, use Merge Queries: open the Power Query Editor, choose Home > Merge Queries, select the two tables and matching key columns, pick a join kind, and expand the resulting column. A merge matches rows using shared values, so clean, consistent keys are essential for reliable results.

What joining tables means in Power Query

Power Query is Excel’s data import and transformation tool. A join, called a merge in its interface, combines information from two queries by matching values in one or more columns. For example, a sales table might contain a customer ID and order amount, while a customer table contains the same ID and the customer’s region. Merging lets you bring the region into the sales data without manually looking up each customer.

The merge initially creates a column containing nested table values. You normally expand that column to select the fields you want to add. The result is a new query, or an updated query if you choose to merge into the existing one, that can be loaded to a worksheet or the Data Model.

Joining is different from placing rows from one table underneath another. Power Query calls that operation Append Queries. Merge matches related records across columns; append stacks records with compatible columns. Choosing the right operation is the first step toward a useful result.

Prepare the tables and choose matching keys

Before merging, identify the column or columns that represent the same thing in both tables. A customer ID is usually a better key than a customer name because names can repeat, change, or be entered inconsistently. The key columns do not have to have the same heading, but their values and data types need to correspond.

Check data types. An ID stored as text in one query and as a number in the other may not match as expected. In the Power Query Editor, check the type icon beside each column name and set both key columns to compatible types.
Look for extra spaces and inconsistent formatting. Values such as North and North can fail to match. Use Transform > Format > Trim for leading and trailing spaces, and consider cleaning inconsistent capitalization or punctuation where appropriate.
Check whether the key is unique. The lookup table should generally have one row per key if each result row is meant to represent one original record. Repeated keys on the lookup side can multiply rows after a merge.
Decide what unmatched rows should do. If every sales row must remain, use a left outer join. If only records found in both tables matter, an inner join may be the right choice.

When a record is identified by a combination, such as department plus employee number, use both columns as the key. In the merge dialog, select the first column in each table, then hold Ctrl on Windows while selecting additional columns in the same order. For a composite key, matching only one component can connect unrelated records.

How to merge tables in Excel for Microsoft 365

Turn each source range into an Excel table if it is not already one: select the data and choose Insert > Table. Confirm whether the table has headers, then give it a meaningful name on the Table Design tab.
Select a cell in a source table and choose Data > From Table/Range. This opens the Power Query Editor and creates a query for that table. Repeat for the other table, or use existing queries already listed in the editor.
In the Power Query Editor, select the query you want to serve as the primary, or left-side, table. Choose Home > Merge Queries to combine into that query, or Home > Merge Queries as New to create a separate query and leave both inputs unchanged.
In the Merge dialog, choose the second query from the lower list. Select the matching key column in the top preview and the corresponding column in the lower preview. The selected columns are marked with a number; for multiple keys, select them in the same sequence in both previews.
Choose the join kind, review the match information shown in the dialog, and select OK. Power Query adds a column whose cells contain table values from the second query.
Select the expand button in the new column header. Choose the fields to include, and clear the option to use the original column name as a prefix if you prefer shorter headings. Select OK.
Review the output, rename columns if useful, and choose Home > Close & Load. Use Close & Load To if you need to choose a destination or load the result to the Data Model.

These are the Windows Microsoft 365 desktop paths. Power Query is also available in supported Microsoft 365 versions for Mac, but available connectors and interface details can vary by build. Excel for the web has different Power Query capabilities and may not expose every editing option or connector; if Merge Queries is missing, use the desktop app to create or edit the query.

Choose the right join kind

The join kind determines which rows survive the match. The two preview tables in the dialog indicate which side contributes rows. For a sales-to-customer example, the sales query is the first table and the customer query is the second.

Left outer: Keeps every row from the first table and adds matching values from the second. Rows without a match remain, with nulls in the expanded fields. This is a common choice when enriching a complete transaction list.
Right outer: Keeps every row from the second table and matching rows from the first. It can be useful when the second table is the population that must be retained, though reversing the table order and using left outer may be easier to understand.
Full outer: Keeps all rows from both tables. Unmatched records from either side remain, with nulls for fields unavailable on that side. Use it to compare lists or find gaps, not simply to add lookup information.
Inner: Keeps only rows with a match on both sides. This filters out unmatched records, so use it only when that exclusion is intended.
Left anti and right anti: Return rows from one side that have no match on the other. These are useful for finding sales records with unknown customer IDs, or customers with no corresponding activity.

A left outer join does not guarantee one output row for each row in the first table. If the second table contains three records with the same key, a first-table row matching that key can appear three times after expansion. Join type controls row inclusion; key uniqueness controls whether matches multiply.

Expand columns and understand the result

After merging, the new column holds nested results rather than ordinary text. The expand control lets you choose which columns from the second query to bring into the output. You rarely need every field: selecting only useful attributes keeps the result narrower and reduces confusion about similarly named columns.

For instance, a customer table may contain customer ID, full name, region, email, and an internal note. If the report needs only region, expand that field alone. If both source tables have a column called Status, keep or rename the relevant version deliberately. Prefixes can prevent duplicate headings, but long query names may make headings unwieldy; renaming the final columns can make them clearer.

In a left outer merge, a null in an expanded field commonly means no matching second-table row was found, though a matched row can also contain a genuinely blank value. To distinguish those cases, expand a field that should always be populated for a valid match, such as the lookup table’s key, and inspect whether that field is null. Do not replace nulls with zero or a label until you know what they mean for the analysis.

Example: add product categories to sales

Suppose a sales query has columns OrderID, ProductCode, and Quantity. A product query has SKU, Description, and Category. Merge Sales with Products by selecting ProductCode in the first preview and SKU in the second, then use a left outer join if every sales line must remain. Expand Description and Category.

If the product query has exactly one row for each SKU, the output should retain the same number of sales rows, with category information added where a match exists. If the result has more rows than the sales input, inspect duplicate SKUs in Products. If it has the same row count but some categories are null, check unmatched codes, data types, spaces, and spelling. This row-count check is a quick diagnostic, not proof that every match is correct.

Common merge problems and how to fix them

No matches despite apparently identical keys: Compare data types, spaces, punctuation, leading zeros, and text case. For example, the text value 00127 is not necessarily equivalent to the number 127. Preserve identifiers as text when their formatting is meaningful.
The result has unexpectedly many rows: Check whether the lookup query has duplicate keys. You can group by the key and count rows, or remove duplicates only if the data rules confirm that one record per key is correct. Removing duplicates without a rule can discard meaningful records.
Some records disappear: An inner join removes unmatched rows by design. Switch to a left outer join if the first table must be preserved, or use an anti join to examine the missing matches.
Columns have confusing suffixes or prefixes: Rename fields before or after the merge, and expand only the required fields. Give query and column names specific labels so that later steps remain readable.
A fuzzy merge gives surprising matches: Fuzzy matching is intended for approximate text comparisons, such as names with minor spelling differences. It is not a substitute for a trustworthy ID. Review the results and use transformation options carefully because similar text can refer to different entities.

Fuzzy matching is available from the merge dialog for supported text columns and can expose options for similarity thresholds and other comparisons. It is not appropriate for every query, and approximate matches can create false positives. For important records, add a stable identifier or maintain an explicit correction table rather than accepting uncertain matches automatically.

Merge versus append, and keeping queries useful

Use Merge Queries when two tables describe related attributes for the same entities, such as orders and customers. Use Append Queries when two tables contain the same kind of records for different periods or sources, such as January sales and February sales. Append aligns fields by column name, so check that headings and data types line up; it does not match records by a key.

A merge is also not the same as a worksheet lookup formula. A formula such as a lookup can return a value in cells, while Power Query stores repeatable transformation steps that run again when the query refreshes. If the source files change, a refresh can reapply the merge and expansion steps. That makes Power Query useful for recurring imports, but it does not mean that a refresh will repair changed column names or altered source structures automatically.

Refresh, performance, and maintenance

Queries retain their transformation steps, including the merge, so changes in the source data can flow into the output after refresh. In Excel, use Data > Refresh All to refresh workbook connections and queries, or right-click a query result and choose Refresh. Check that source files remain accessible and that any credentials or permissions required by the source are still valid.

For larger datasets, reduce unnecessary work before the merge: remove columns you do not need, filter irrelevant rows when appropriate, and use the smallest useful set of fields in the final output. Power Query can sometimes fold steps back to a database source, allowing the source system to perform work, but folding depends on the connector and transformations. If performance matters, inspect the source and query design rather than assuming every merge runs the same way.

Keep the two source queries and the output query named clearly, for example Sales, Products, and SalesWithProducts. This makes dependencies easier to follow. When a merge changes, validate a few known records, compare row counts, and check unmatched keys before relying on the refreshed output.

Frequently Asked Questions

Is Merge Queries the same as a join in SQL?
It serves the same general purpose: matching rows from two sources using key columns and a selected join type. Power Query presents the result through its editor, initially as a nested column that you expand, rather than requiring you to write a SQL JOIN statement.

Can I join tables using more than one column?
Yes. Select each matching key column in the same order in both table previews in the Merge dialog. Use multiple keys when one field alone is not unique, such as a location code plus a product code.

Why did my merge create duplicate rows?
The second query probably has more than one row for at least one selected key. Each matching row can contribute a result, multiplying the corresponding row from the first query. Check the key’s uniqueness and decide which record should represent each key before removing duplicates.

Can Power Query merge tables from different files?
Yes, provided the sources are accessible through supported connectors. Create or load a query for each source, then merge the queries in the same way. File paths, access permissions, and source structure can affect whether refresh succeeds.

What should I use to find rows that do not match?
Choose Left Anti to return rows from the first table with no corresponding row in the second, or Right Anti for the reverse. These joins are useful for auditing missing IDs before building the final left outer or inner merge.

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.