How to join tables in Excel: Power Query vs. Merge Tables Wizard

Source data

As an example, let's join 3 tables based on the common columns Order ID and Seller. Please note that our tables have different numbers of rows, and although table 1 has duplicates in the Seller column, table 3 contains only unique entries.

Our task is to map the data in table 1 with the relevant records from the other two tables, and combine all the data into a new table like this:

Before you start joining, I'd advise you to give some descriptive names to your tables, so it will be easier for you to recognize and manage them later. Also, although we say "tables", you do not actually need to create an Excel table. Your "tables" could be usual ranges or named ranges as in this example:

• Table 1 is named Orders
• Table 2 is named Products
• Table 3 is named Commissions

Create Power Query connections

Not to clutter your workbook with copies of your original tables, we are going to convert them into connections, do the merge within the Power Query Editor, and then load only the resulting table.

To save a table as a connection in Power Query, here's what you do:

1. Select your first table (Orders) or any cell in that table.
2. Go to the Data tab > Get & Transform group and click From Table/Range.
   
3. In the Power Query Editor that opens, click on the Close & Load drop-down arrow (not the button itself!) and select the Close and Load To… option.
   
4. In the Import Data dialog box, select the Only Create Connection option and click OK.
   

   This will create a connection with the name of your table/range and display that connection in the Queries & Connections pane that appears on the right-hand side of your workbook.

5. Repeat the above steps for all other tables you want to merge (two more tables, Products and Commissions, in our case).

When finished, you will see all the connections on the pane:

Merge two connections into one table

With the connections in place, let's see how you can join two tables into one:

1. On the Data tab, in the Get & Transform Data group, click the Get Data button, choose Combine Queries in the drop-down list, and click Merge:
   
2. In the Merge dialog box, do the following:
   • Select your 1st table (Orders) from the first drop-down.
   • Select your 2nd table (Products) from the second drop-down.
   • In both previews, click on the matching column (Order ID) to select it. The selected column will get highlighted in green.
   • In the Join Kind drop-down list, leave the default option: Left Outer (all from first, matching from second).
   • Click OK.

   

Upon completion of the above steps, the Power Query Editor will show your first table (Orders) with one additional column named like your second table (Products) added to the end. This additional column does not have any values yet, just the word "Table" in all the cells. But don't feel discouraged, you did everything right, and we are going to fix that in a moment!

Select the columns to add from the second table

At this point, you have a table resembling the one in the screenshot below. To complete the merging process, perform the following steps within the Power Query Editor:

1. In the added column (Products), click on the two-sided arrow in the header.
   
2. In the box that opens, do this:
   • Keep the Expand radio button selected.
   • Unselect all columns, and then select only the column(s) you want to copy from the second table. In this example, we select only the Product column because our first table already has Seller and Order ID.
   • Uncheck the Use original column name as prefix box (unless you want the column name to be prefixed with the table name from which this column is taken).
   • Click OK.

   

As the result, you will get a new table that contains every record from your first table and the additional column(s) from the second table:

If you need to merge only two tables, you may consider the work almost done and go load the resulting table in Excel.

Merge more tables (optional)

In case you have three or more tables to join, there is some more work for you to do. I will outline the steps briefly here, because you have already done all this when joining the first two tables:

1. Save the table you've got in the previous step (shown in the screenshot above) as a connection:
   • In the Power Query Editor, click Close & Load drop-down arrow and select Close and Load To….
   • In the Import Data dialog box, select Only Create Connection, and click OK.

   This will add one more connection, named Merge1, to the Queries & Connections pane. You can rename this connection if you want (right-click and select Rename in the pop-up menu).
   

2. Combine Merge1 with your third table (Commissions) by performing these steps (Data tab > Get Data > Combine Queries > Merge).

   The screenshot below shows my settings:
   

3. Clicking OK in the Merge dialog box opens the Power Query Editor, where you select the columns to be added from table 3.

In this example, we add only the Commission column:

As the result, you get a merged table that consists of the first table, plus the additional columns copied from the other two tables.

Import the merged table to Excel

With the resulting table in the Power Query Editor, there is just one thing left for you to do - load it in your Excel workbook. And it is the easiest part!

1. On the Home tab of the Power Query Editor, click the Close & Load drop-down arrow, and choose Close and Load To….
2. In the Import Data dialog box, select Table and New Worksheet options.
3. Click OK.

A new table combining the data from two or more sources appears in a new worksheet. Congratulations, you did it!

As a finishing touch, you may want to apply the right number format to some columns and maybe change the default table style to your favorite one. After these improvements, my combined table looks very nice:

How to join tables based on multiple columns with Power Query

In the previous example, we were combining tables by matching data in one key column. But there is nothing that would prevent you from selecting two or more column pairs. Here's how:

In the Merge dialog box, hold the Ctrl key and click on the key columns one-by-one to select them. It is important that you click on the columns in the same order in both previews, so the matching columns have the same numbers. For example, Seller is key column 1 and Product is key column 2. Blank cells or rows that Power Query is unable to match show null:

After that, perform exactly the same steps as described above, and your tables will be merged by matching values in all the key columns.

How to update/refresh the merged table

The best thing about Power Query is that it is a one-time setup. When you make some changes to a source table, you don't have to repeat the whole process again. Simply, click the Refresh button on the Queries & Connections pane, and the merged table will update at once:

If the pane has disappeared from your Excel, click the Queries & Connections button on the Data tab to get it back.

Alternatively, you can click the Refresh all button on the Data tab tab or the Refresh button on the Query (this tab activates once you select any cell within a merged table).