How to How to Merge Queries in Power Query in Excel in Excel - Practical Guide

Learn to merge queries in Power Query by combining data from multiple sources using Inner, Left, Right, and Full Outer joins. This advanced technique eliminates manual data consolidation, reduces errors, and enables seamless integration of related datasets for comprehensive analysis.

Why This Matters

Merging queries is essential for combining related datasets from multiple sources without manual copying, ensuring data accuracy and saving significant time in ETL workflows.

Prerequisites

•Basic understanding of Power Query and how to load data sources
•Familiarity with relational data concepts and join types
•Excel 2016 or later with Power Query enabled

Step-by-Step Instructions

Open Power Query Editor

In Excel, go to Data > Get & Transform Data > Get Data > From Other Sources > Blank Query (or open existing queries). This opens the Power Query Editor where you'll manage your merge operation.

Load Your First Query

Import or create your primary query with the base dataset. Navigate to Home > New Source and select your data source (CSV, database, Excel table, etc.), then click Load to Power Query.

Load Your Second Query

Repeat the process to load your second dataset as a separate query in the Power Query Editor. Ensure both queries contain common key columns for matching records.

Execute the Merge

In Power Query Editor, select your first query in the left panel, then go to Home > Merge Queries > Merge Queries. Choose your second query from the dropdown and select the matching key column from both tables.

Select Join Type and Finalize

Choose your join type (Inner, Left Outer, Right Outer, or Full Outer) based on your needs, click OK, then expand the new merged column using the expand icon. Load the result to Excel via Home > Close & Load.

Alternative Methods

Using Merge Queries as New

Instead of merging into an existing query, use Home > Merge Queries > Merge Queries as New to create a separate query without modifying your original datasets. This preserves your source queries for reuse.

Using Append Instead of Merge

If stacking data vertically rather than joining horizontally, use Home > Append Queries to combine queries with identical column structures into a single table.

Tips & Tricks

✓Always verify that key columns contain matching data types and values before merging to avoid mismatches.
✓Use Left Outer join to retain all rows from your primary query and add matching data from the secondary query.
✓Remove unnecessary columns from your queries before merging to improve performance and reduce file size.
✓Rename queries with descriptive names like 'Sales_Data' and 'Customer_Info' for clarity in complex workflows.

Pro Tips

★Use Merge Queries as New for complex multi-step merges to maintain a clean query dependency structure and enable easy troubleshooting.
★Apply filters to queries before merging to reduce dataset size and improve merge performance on large datasets.
★Leverage the 'Expand All' option after merging to quickly add all columns from the related table without manual selection.
★Create a helper index column if your key columns contain duplicates to perform many-to-one or many-to-many joins accurately.

Troubleshooting

Merge Queries button is greyed out

Ensure you have loaded at least two queries in Power Query Editor. The merge option only appears when multiple queries are available in the left panel.

Key column shows as 'not found' in merge dialog

Verify the column exists in both queries by checking the column headers. Rename columns if they have different names but represent the same data.

Merged results show only one row instead of expected matches

Check if your key column has unique values; if duplicates exist, Power Query will match each occurrence. Use grouping or aggregation if you need summary data.

Memory error or slow performance after merge

Remove unnecessary columns from both queries before merging and apply filters to reduce the dataset size. Consider splitting large merges into multiple steps.

Related Excel Formulas

VLOOKUP INDEX/MATCH XLOOKUP

Frequently Asked Questions

What is the difference between Inner, Left, Right, and Full Outer joins?

Inner join returns only matching rows from both tables. Left Outer keeps all rows from the left table plus matches from the right. Right Outer does the opposite. Full Outer returns all rows from both tables, with nulls where no match exists.

Can I merge on multiple columns?

Yes, in the merge dialog you can click 'Add New Key' to add additional matching columns, allowing composite key merges when single columns aren't unique identifiers.

How do I handle many-to-many relationships in Power Query merges?

Power Query will generate a cross-join (Cartesian product) matching all instances, resulting in duplicate rows. Use grouping, aggregation, or an index column to control the relationship behavior.

What's the difference between Merge Queries and Merge Queries as New?

Merge Queries replaces your first query with the merged result, while Merge Queries as New creates a separate query, preserving your original queries for reuse and maintaining cleaner dependencies.

How do I expand all columns from a merged query at once?

After merging, click the expand icon (double-headed arrow) in the merged column header. Select 'Expand All' from the dropdown to include all columns, or choose specific columns to keep the data lean.