To merge three queries in Excel Power Query, you perform two sequential merge operations: first merge two queries, then merge the result with the third query. This is done in the Power Query Editor using the Merge Queries command, which combines rows based on matching columns, similar to a SQL JOIN.
Background: Why Merge Three Queries?
In real-world data work, you often need to combine data from multiple sources before loading it into Excel. For example, you might have a Sales table, a Products table, and a Regions table. To analyze sales by product and region, you need to bring all three together. Power Query allows you to merge queries without writing complex formulas, and you can chain merges to combine as many queries as needed.
How Merging Works in Power Query
Merging in Power Query is based on the concept of join kinds. When you merge two queries, you select a column (or columns) from each that contain matching values. The result is a new query where each row from the first query is combined with matching rows from the second. For three queries, you repeat this process: after the first merge, you have a new query (let’s call it Merged1), then you merge Merged1 with the third query.
Power Query automatically creates a new column in the merged result that contains a table (the matching rows from the second query). You then expand that column to bring the desired fields into the main table.
Step-by-Step: Merging Three Queries
Here is the exact process for merging three queries, using the example of Sales, Products, and Regions.
- Load all three queries into Power Query Editor (Data > Get Data > From Other Sources > Blank Query, or from tables/ranges). Ensure each has a common column (e.g., ProductID in Sales and Products, RegionID in Sales and Regions).
- First merge: In the Power Query Editor, select the Sales query. Go to Home > Merge Queries (or the Merge button on the Combine group). In the dialog, choose Products as the second table, select the matching columns (ProductID in both), and choose a join kind (usually Left Outer to keep all sales rows). Click OK.
- Expand the first merge: A new column appears named “Products” (or similar) containing tables. Click the expand icon (double arrow) in that column header. Uncheck “Use original column name as prefix” if you want clean names, then select the columns you need (e.g., ProductName, Price). Click OK.
- Second merge: With the merged query still selected, go to Home > Merge Queries again. This time choose Regions as the second table, select the matching columns (RegionID in both), choose join kind, and click OK.
- Expand the second merge: Click the expand icon on the new “Regions” column, select the fields you want (e.g., RegionName, Manager), and click OK.
- Load to Excel: Click Close & Load to load the final merged table into a worksheet.
After the first merge, the result is a query that contains the expanded data from the first merge. That query becomes the “left” table for the second merge. You can also merge the second and third first, then merge with the first—order doesn’t matter as long as the join keys are correct.
Choosing the Right Join Kind
Power Query offers six join kinds. For merging three queries, the most common are:
- Left Outer – keeps all rows from the first (left) query, and only matching rows from the second. Use this when the first query is your primary data and you want to enrich it.
- Inner – keeps only rows that have matches in both queries. Use when you only want complete records.
- Full Outer – keeps all rows from both, filling nulls where no match exists. Rarely needed for three-way merges.
For example, if you want all sales even if a product is missing, use Left Outer. If you only want sales that have both a product and a region, use Inner for both merges.
Common Problems and Solutions
Merging three queries can hit a few snags:
- Column name mismatches: The join columns must have the same data type and ideally the same name, but you can select different columns. If you get an error, check for trailing spaces or number vs. text formats.
- Duplicate rows after expansion: If the second query has multiple rows per key, the merge will multiply rows. For example, if a product has multiple categories, you’ll get duplicate sales rows. To avoid this, aggregate or deduplicate the second query before merging.
- Performance issues: Merging large tables can be slow. Try to filter or remove unnecessary columns before merging.
- Expansion column not showing: If you don’t see the expand icon, it might be because the merge created a column with a table, but the column is hidden. Check the column headers; the icon appears on the right side of the header.
Comparison: Merge vs. Append vs. Lookup
Merging is different from Append, which stacks queries vertically (like adding rows). If you need to combine three tables with the same columns, you use Append, not Merge. Merging is for combining columns from different tables based on a key. Also, Power Query’s merge is more powerful than Excel’s VLOOKUP because it can handle multiple join columns and different join types, and it refreshes automatically.
Frequently Asked Questions
Can I merge more than three queries at once?
Yes, you can chain as many merges as you need. Each merge produces a new query that can be merged again. There is no hard limit, but performance may degrade with very large numbers of merges.
What if my join columns have different names?
That’s fine. In the Merge dialog, you select the column from each table independently. They don’t need to have the same name, only compatible data types and matching values.
How do I merge using multiple columns as the key?
Hold down the Ctrl key (Cmd on Mac) while clicking column headers in the Merge dialog to select multiple columns from each table. The merge will match on the combination of those columns.
Why does my merged query show a column with “Table” values?
That’s normal. The merge creates a nested table column. You must click the expand icon to bring the fields into the main table. If you don’t expand, the data is not usable in Excel.
Can I undo a merge after expanding?
Yes, you can remove the expansion by clicking the “X” on the expand icon or by going to the query steps on the right and deleting the “Expanded” step. However, if you’ve made other changes, it may be easier to redo the merge.
Final Tips for Clean Results
Before merging, ensure your data is clean: remove duplicate rows in the lookup tables, standardize text case, and convert data types to match. After merging, rename columns as needed, and consider removing the original key columns if they are duplicated. Always preview the data before loading to catch any unexpected row multiplication.
Related
- Excel Gradebook Template for Teachers: How to Use
- Excel Group Lake Jackson TX: What It Is & Services
- Excel Flexi Timesheet Template UK: How to Build One
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Copy Selected Cells in Excel Without ErrorsTo copy selected cells in Excel for Microsoft 365 on Windows, select a cell or range, press Ctrl+C,…
- How to Add Weekly Dates in ExcelTo add weekly dates in Excel, enter your starting date in A2, then enter =A2+7 in A3 and…
- Excel Protect Sheet: Can a Table Expand Automatically?Usually, no: protecting an Excel worksheet does not provide a dependable permission that lets an Excel table add…
- Excel Slicers: How to Create and Use ThemTo create a slicer in Excel for Microsoft 365 on Windows, click inside a PivotTable and choose PivotTable…
- Report Builder CountRows: Count Rows in a GroupIn Microsoft Report Builder, count rows in a group with =CountRows("GroupName"), replacing GroupName with the tablix group’s actual…
- How to Freeze a Reference in Excel with Dollar SignsTo freeze a cell reference in an Excel formula, add dollar signs to the cell address—for example, change…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.