In Power Query, you can merge two or more queries based on a matching column or columns.
When working with data from multiple sources, you may often need to combine these datasets based on common columns. This approach is particularly useful when you have related information spread across different tables that you want to analyze together
Let me show you how to merge multiple queries to get one single dataset in Power Query.
Suppose you have a workbook containing two tables on separate sheets as shown below.
The Sales table is on the Sales worksheet.
The Products table is on the Products sheet.
The first thing you need to do is create two queries—Sales and Products—in Power Query based on the tables..
The queries you want to merge must have at least one common column with matching values that can act as the key for the merge. The common column should contain unique values in at least one of the queries to ensure accurate matching. In this example, the matching column in both queries is ProductID.
Create the Queries
Here are the steps you can use to create the two queries:
- Click the Data tab.
- Open the Get Data drop-down on the Get & Transform Data group, and click the Launch Power Query Editor option.
The above step opens the Power Query Editor.
- On the Power Query Editor, open the New Source drop-down on the Ribbon’s far right, hover the mouse pointer over the File option, and click the Excel Workbook on the submenu.
The above step opens the Import Data dialog box.
- On the Import Data dialog box, navigate to the folder where the target workbook is stored, select it, and click the Import command button.
The above step opens the Navigator feature.
- On the Navigator feature, check the ‘Select multiple items’ option, select the Products and Sales tables, and click OK.
The above step creates the Sales and Products queries in the Power Query Editor.
Merge the Queries
You can now merge the Sales and Products queries using the steps below.
- Select the Sales query, which is the primary query where you want to add the product name and price columns from the Products query.
- Open the Merge Queries drop-down on the Combine group.
The Merge Queries drop-down shows two options: Merge Queries and Merge Queries as New.
Use the Merge Queries option to merge the primary query with another query and get the results in the primary query itself.
Use Merge Queries as New option to create a new query by merging the primary query with another one, while keeping the original queries unchanged.
In this example, I will go with the Merge Queries as New option.
- Click the Merge Queries as New option on the Merge Queries drop-down list.
The above step opens the Merge feature with the primary query (Sales) already selected.
- On the Merge feature, select the second query (Products) from the second drop-down.
- Select the matching column in both queries—in this case, ProductID—leave the default join type as Left Outer, and click OK.
The above step creates a new query named ‘Merge1’, which includes a new merged column with a table icon. You can rename this query to something more meaningful—for example, I’ve renamed it ‘Sales_Products.’
- Expand the merged column and select the columns you want to bring into the primary query, using the steps below.
- Click the Expand icon in the header of the Products merged column.
- Uncheck the Select All Columns option.
- Select the ProductName and Price (USD) columns.
- Uncheck the ‘Use original column name as prefix’ option.
- Click OK.
The above step brings the ProductName and Price (USD) columns into the new query.
- Rearrange the new columns in the merged query to more logical positions, as shown below.
- Click the Close & Load command button on the Close group.
The above step saves changes to the merged query, closes the Power Query Editor, and loads the results to a new sheet.
I have shown you how to merge queries in Power Query. I hope you found the tutorial helpful.
Other Excel articles you may also like:
