Table of Contents
An inner join in Excel is a type of join used to combine two tables based on common columns between them. For example, if you have a table with customer information and another table with sales data, you can use an inner join to combine them by their customer ID. To do this, you would select the two tables, click the Data tab, click the ‘Get & Transform Data’ group, and select ‘From Table/Range’, then select the ‘Data’ tab, and click ‘Merge Queries’ and select ‘Inner Join’. Finally, you can select the columns you want to merge and click ‘OK’.
An inner join allows you to join together two tables in which the only rows in the resulting table are the ones where there are matching values in a column common to both tables.
The following step-by-step example shows how to use the Power Query functionality in Excel to perform an inner join on two tables.
Step 1: Enter the Values for Each Table
First, let’s enter the following values for two tables in Excel:
We will perform an inner join in which we only keep the rows from each table that have matching values in the Team column.
Step 2: Convert Each Range to a Table
Next, we must convert each range of values into a table.
First, highlight the cell range A1:B8 and then click the Insert tab along the top ribbon and then click the Table icon:
In the new window that pops up, click the OK button.
Repeat these steps for the cell range D1:E6.
Both ranges of data will now appear as tables:
Step 3: Use Power Query to Perform Inner Join
Next, right click any cell in the first table and then click Get Data from Table/Range from the dropdown menu:
Next, simply click the Close & Load icon in the top left corner and then click Close & Load To from the dropdown menu:
In the new window that appears, select Only Create Connection and then click OK:
Next, right click any cell in the second table and then click Get Data from Table/Range from the dropdown menu:
The second table will be loaded into the Power Query Editor.
Next, click the Merge Queries icon in the Combine group along the top ribbon of the Power Query Editor.
In the new window that appears, place the Table1 in the first box and Table2 in the second box. Then click the team column in each table. Then click Inner under the Join Kind dropdown menu, then click OK:
Once you click OK, the inner join will be performed:
Next, click the left and right arrow icons on the column titled Table2 and then click OK:
The rebounds column from the second table will appear:
Lastly, click the Close and Load To icon once more.
In the new window that appears, click Table and New worksheet, then click OK:
The final table that resulted from the inner join will now be displayed in a new worksheet:
Notice that only the rows with the team names that appear in both tables are in the final table.