Excel Power Query: Merge 3 Tables

How-to basics · Updated November 22, 2026

To merge three tables in Excel Power Query, you perform two sequential merge operations: first merge two tables, then merge the result with the third table. This is done from the Home tab in the Power Query Editor, using the Merge Queries command, and choosing the appropriate join kind for each step.

Why Merge Three Tables in Power Query?

Merging tables in Power Query is the equivalent of a SQL JOIN. It combines columns from different tables based on a common key field. You might need to merge three tables when your data is normalized across multiple sources—for example, an orders table, a customers table, and a products table. Instead of using VLOOKUP or INDEX/MATCH formulas, Power Query offers a more robust, repeatable process that can handle large datasets and refresh automatically.

Step-by-Step: Merging Three Tables

Assume you have three tables in an Excel workbook: Orders (with columns: OrderID, CustomerID, ProductID, Quantity), Customers (CustomerID, CustomerName), and Products (ProductID, ProductName, Price). Here’s how to merge them:

  1. Load each table into Power Query: select any cell in a table, go to Data > From Table/Range. Repeat for each table. Each will open as a query in the Power Query Editor.
  2. In the Power Query Editor, select the Orders query (the one you want as the base).
  3. Go to Home > Merge Queries (or Merge Queries as New if you want a separate query). In the dialog, choose Customers as the second table, and click on the CustomerID column in both tables to set the join keys.
  4. Choose a join kind. For most cases, Left Outer (all rows from Orders, matching rows from Customers) is appropriate. Click OK. You’ll see a new column called Customers (the name of the merged table) with a Table value inside each cell.
  5. Click the expand icon (double arrow) on the new column header. Uncheck “Use original column name as prefix” if you want clean column names, then select the columns you want (e.g., CustomerName). Click OK.
  6. Now, with the Orders query still selected, go to Home > Merge Queries again. This time choose Products as the second table, and select ProductID in both tables. Choose the join kind (Left Outer) and click OK.
  7. Expand the new Products column, selecting ProductName and Price. Click OK.
  8. Click Close & Load to load the merged table back into Excel, or Close & Load To to choose a destination.

The result is a single table with columns from all three sources, combined by the key fields.

Choosing the Right Join Kind

Power Query offers six join kinds, and your choice affects which rows appear in the result:

  • Left Outer: all rows from the first (left) table, and matching rows from the second. Non-matching rows show nulls for the second table’s columns. This is the most common for merging lookup data.
  • Right Outer: all rows from the second (right) table, matching from the first.
  • Full Outer: all rows from both tables, with nulls where no match exists.
  • Inner: only rows that have matches in both tables.
  • Left Anti: rows from the first table that have no match in the second. Useful for finding missing records.
  • Right Anti: rows from the second table that have no match in the first.

For merging three tables, you typically use Left Outer for each step if you want to preserve all rows from the base table. If you need to keep only records that exist in all three tables, use Inner for both merges.

Common Problems and How to Avoid Them

Merging three tables can hit a few snags:

  • Mismatched data types: If the key column is text in one table and number in another, the merge will fail or produce no matches. Ensure both columns have the same data type (e.g., both Text or both Whole Number). You can change types in Power Query by selecting the column and choosing Home > Data Type.
  • Duplicate keys: If the lookup table has duplicate keys, the merge will multiply rows. For example, if a customer appears twice in the Customers table, each order for that customer will appear twice. Use Remove Duplicates on the key column in the lookup table before merging, or use a join kind that handles duplicates differently.
  • Column name conflicts: When expanding, you might get columns with the same name (e.g., “Name” from both Customers and Products). Power Query will rename them automatically (e.g., “Name.1”), but you can rename them manually in the editor.
  • Performance issues: Merging very large tables can be slow. Try to filter rows before merging, and avoid expanding unnecessary columns.

Merging vs. Appending: What’s the Difference?

A common confusion is between merging and appending. Merging combines columns side-by-side based on a key (like a JOIN). Appending stacks rows on top of each other (like a UNION). If you have three tables with the same columns (e.g., sales data from three regions), you would append them, not merge. In Power Query, appending is done via Home > Append Queries, and you can append multiple tables at once. Merging is for adding related columns from other tables.

Using Merge Queries as New vs. In-Place

When you click Merge Queries, Power Query merges into the current query. If you click the dropdown arrow next to it, you can choose Merge Queries as New. This creates a separate query with the merged result, leaving the original queries untouched. This is useful if you want to keep your base queries clean for other uses. For a three-table merge, you might prefer “Merge as New” for the first step to avoid altering the original Orders query, then merge the new query with the third table.

Frequently Asked Questions

Can I merge more than three tables in one step?

No, Power Query’s Merge command only allows two tables at a time. To combine three or more, you must perform sequential merges, as described above. The result of the first merge becomes the input for the second.

What if my tables are in different workbooks or databases?

Power Query can merge tables from different sources. For example, you can load one table from an Excel file and another from a SQL Server database, then merge them in the editor. Just load each source as a query first, then merge.

How do I refresh the merged data when the source tables change?

After you load the merged result to Excel, you can refresh it by right-clicking the table and choosing Refresh, or by going to Data > Refresh All. Power Query will re-run the merge steps, pulling the latest data from the sources.

Why does my merge return nulls for some rows?

Nulls appear when the join key in the base table has no match in the second table, and you used a Left Outer join. That means the row has no corresponding record in the lookup table. Check for typos or data type mismatches in the key columns.

Can I merge three tables without a common key?

No, merging requires at least one column to match between tables. If there’s no common key, you cannot merge them in a meaningful way. You could append them if they have the same structure, but that’s a different operation.

Is there a way to merge three tables using Power Pivot instead?

Yes, Power Pivot uses DAX relationships to relate tables, but it doesn’t physically merge them into one table. If you need a single flattened table, Power Query is the right tool. Power Pivot is better for creating a data model with multiple related tables for pivot reporting.

Final Tips for a Smooth Three-Table Merge

Before merging, clean your data in Power Query: remove duplicate rows, fix data types, and rename columns to avoid confusion. Use the View > Column Quality and Column Distribution options to spot issues. Also, consider using a Fuzzy Merge if your keys have slight variations (e.g., “John Smith” vs. “John A. Smith”), but be cautious—fuzzy matching can be slow and imprecise. Finally, always test your merge on a small sample before applying it to millions of rows.

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.