How to merge two Excel tables

Excel is powerful and helpful in carrying out documentation and analysis. The spreadsheet contains some rows and columns. However, Excel tables describe the arrangement of data in columns and rows.

Considerably, two more data groups can be combined into a single unified set, as discussed below.

Use of Power Query

Power Query is an addon that enables one to discover, combine and connect data sources to meet your data analysis.

If you are using Excel 2016 and below, you need to install the addin.  The newer versions have the addin pre-installed.  before installing the add-on you need to figure out if your office version is 32bit or 64bit.

How to install power query plugin

  • Open Excel
  • Click on file
  • Click account from the side navigation menu

  • Click About Excel and a pop-up will display your version details.
  • Visit this download page and download Power Query that matches with your Office Version

 

  • Once the download is complete. Click on the set up to install the add-on
  • Click next and accept all terms and conditions
  • Note you need to close for installation to run smoothly
  • After the setup is complete click finish and Launch Excel

Power Query is now installed Successfully and in Excel 2016, it if found in Data tab under the Get and Transform section.

 

How to merge Two Excel Tables Using Power Query

1. Go Assuming you have the following tables. Orders table

And customer information Table

We can use Power Query to merge these two tables.

2. First, select any cell in the customer's table then create a connection. You can do this by clicking on the Power Query tab then clicking from the table or range. The power Query Editing Tab will be open.

3. Click the Home tab on Query, then click the bottom half of the close and load button and select Close and Load To. On the pop-up that shows click Only Create Connection and click Okay/load.  

It will create a connection in the Power query to the customer's table.

4. Open Your second table Locate and click Merge Queries. A pop-up menu will appear.

 

5. Now select the two columns that you want to merge from your tables. Then choose the left Outer Join before clicking okay.  A new Column will be formed on the extreme right.

Now you can Expand the table by clicking the expand Icon.

Untick ID to avoid Duplication. Also, untick use original column name as prefix. Then click okay. Your tables have been successfully merged

The null cells indicate power query did not find any matching value.