Durjoy Paul
Sometimes, you need to combine two tables in Excel. You can do it manually but that takes a lot of valuable time. Instead of doing this manually, you need to utilize a power query for that purpose. Power query initially set up a connection between two tables in Excel. After that, It merges the tables effectively. This article will show how to combine two tables in a power query in Excel.
Download Practice Workbook
Download the practice workbook below.
Step-by-Step Procedure to Combine Two Tables Using Power Query in Excel
To combine two tables in power query in Excel, we have found step-by-step procedures through which you can have a complete overview. Here, you need to convert the datasets into tables. Then, using the power query, establish a connection between two tables. After that, we would like to merge those connected tables. Finally, we will import them into Excel. To show the whole process, we take two datasets. One denotes the sales information and the other is the region of sellers. In the sales information dataset, we set order date, item, sales rep., quantity, unit price, commission, and total cost.
The region of sellers includes the sales rep. and their sales region.
We would like to combine these two tables in a power query. To do this, follow the steps carefully.
Step 1: Convert Dataset into Table
In the first step, we would like to convert the datasets into tables. Using table format helps you define the name. Then, you can use the name to get the table in the power query. Follow the steps carefully.
- In the first dataset, select the range of cells B4 to H17.
- Then, go to the Insert tab on the ribbon.
- Select Table from the Tables group.
- As a result, the Create Table dialog box.
- As you selected the range of cells previously, it appears there automatically.
- Check on My table has headers.
- Finally, click on OK.
- As a consequence, you will get the following result. See the screenshot.
- Then, select any cell on the table, and it will open up the Table Design tab on the ribbon.
- Select the Table Design tab on the ribbon, and set the Table Name as Sales_Data from the Properties group.
- In the second dataset, select the range of cells B4 to C9.
- Then, go to the Insert tab on the ribbon.
- Select Table from the Tables group.
- As a result, the Create Table dialog box.
- As you selected the range of cells previously, it appears there automatically.
- Check on My table has headers.
- Finally, click on OK.
- As a consequence, you will get the following result. See the screenshot.
- Then, select any cell on the table, and it will open up the Table Design tab on the ribbon.
- Select the Table Design tab on the ribbon, and set the Table Name as Region from the Properties group.
Read More: How to Merge Tables from Different Sheets in Excel (5 Easy Ways)
Step 2: Create Connection Using Power Query
In this step, we would like to create a connection between two tables using the power query. To understand the step closely, follow the steps carefully.
- Select any cell on the first table.
- Then, go to the Data tab on the ribbon.
- Select From Table/Range option from the Get & Transform Data group.
- As a result, it will take the Sales_Data table into the power query.
- To create the connection, select the Home tab on the ribbon.
- Then, select the Close & Load drop-down option from the Close group.
- After that, select the Close & Load To option from the Close & Load drop-down option.
- The Import Data dialog box will appear.
- Then, select Only Create Connection from Select how you want to view this data in your workbook section.
- Finally, click on OK.
- As a result, it will create a connection with the name of the table and appear in the Queries.
- The Queries will appear on the right side of your workbook.
- After that, select any cell on the second table.
- Then, go to the Data tab on the ribbon.
- Select From Table/Range option from the Get & Transform Data group.
- As a result, it will take the Region table into the power query.
- To create the connection, select the Home tab on the ribbon.
- Then, select the Close & Load drop-down option from the Close option.
- After that, select the Close & Load To option from the Close & Load drop-down option.
- The Import Data dialog box will appear.
- Then, select Only Create Connection from Select how you want to view this data in your workbook section.
- Finally, click on OK.
- As a result, it will create a connection with the name of the table and appear in the Queries.
- The Queries will appear on the right side of your workbook.
Step 3: Combine Two Tables into One Table
After creating a connection between two tables in the power query, we would like to merge two connection tables into one table. To do this, follow the steps properly.
- First, select the Data tab on the ribbon.
- Then, select Get Data drop-down option from the Get & Transform Data group.
- After that, from Combine Queries option, select Merge.
- As a result, the Merge dialog box will appear.
- From the drop-down option, select the Sales_Data table and then select the Region table from the second drop-down option.
- Then, select Sales rep. Column for both tables to create a connection.
- Finally, click on OK.
- As a result, it will take us to the power query. See the screenshot.
Read More: How to Merge Two Tables Based on One Column in Excel (3 Ways)
Step 4: Import Combined Table
In this step, we would like to import the combined table in Excel. Before doing this, we have to modify this in the power query. After that, you can import them into Excel. To understand the process, follow the steps.
- First, select the two-sided arrow in the header.
- Because of this, you have a filter option. From there, just select the Region option.
- Then, click on OK.
- As a result, you will get the region in this column. See the screenshot.
- Then, you can drag the region beside the sales rep.
- Just, hold the header by clicking and set it in the desired place.
- Select the Home tab on the ribbon.
- Then, select the Close & Load drop-down option from the Close option.
- After that, select the Close & Load To option from the Close & Load drop-down option.
- The Import Data dialog box will appear.
- Then, select Table from Select how you want to view this data in your workbook section.
- After that, select the New Worksheet option to put the data.
- Finally, click on OK.
- Finally, we will get the following result. See the screenshot.
Read More: How to Merge Two Pivot Tables in Excel (with Quick Steps)
How to Join Tables Based on Multiple Columns Using Power Query in Excel
You can join tables based on multiple columns using a power query. In this process, you have to take two tables. Then, create connections using the power query. After that, we will merge them but this time we will merge them based on multiple columns.
First, follow the procedure that we did previously to make connections between two tables. After that, go to the Data tab on the ribbon. Then, select Get Data drop-down option from the Get & Transform Data group. After that, from Combine Queries option, select Merge. In the Merge dialog box, you need to press Ctrl and click the important columns one after another. As a result, we can join tables based on multiple columns using the power query.
Read More: How to Merge Two Tables in Excel with Common Column (5 Ways)
How to Refresh Combined Table in Excel
The best possible way to use the power query is if you do the one-time setup. If you need to change anything in the power query, you have to refresh it. Otherwise, the change won’t appear.
You can do the refresh from the Queries section which appears on the right side of the workbook.
If the Queries pane disappears from your workbook, you can easily get it. To get this, you need to go to the Data tab on the ribbon. From the Queries & Connections group, select Queries & Connections.
You can refresh the merged table from the Data tab where you must select Refresh All from the Queries & Connections group.
Otherwise, you need to click any cell on the merged table, it will open up the Query option on the ribbon. Then, select Refresh from the Load group.
Things to Remember
- After combing the tables using the power query, if you need to change the data of the table, you can easily do it. But after that, you have to refresh the combined table to verify the changes.
- Power query reduces all the manual work while combining multiple tables.
Conclusion
We have shown a step-by-step procedure to combine two tables in a power query in Excel. All of these steps are fairly easy to use. In this article, we have also shown how to join tables by selecting multiple columns using a power query. We also included the way of refreshing the combined table. I hope we covered all the areas of the topic. If you have any questions, feel free to ask in the comment box. Don’t forget to visit our Exceldemy page.
