How to merge tables using Inner Join and Left Outer Join in Power BI?

Introduction

In this article, we will gain deep insights into Inner Join and Left Outer Join in Power BI. A wide variety of joins, like full join, inner join, and left anti-join are available in Power BI to join tables. The inner join merges two tables and extracts only those records that contain the matching values in the common column of both tables. The left-outer join also matches the common values from the two tables but retrieves all the first table records along with the corresponding records of the matched values from the second table.

Implementation of Inner Join in Power BI

Step 1

Users need to load two tables named employee.xls and emp_dept.xls in Power BI. The structure of these tables is shown below −

Step 2

Open “Power BI Desktop” and then navigate to the “Home” tab and choose the “Get data” option to load the Excel workbook as highlighted below image −

Step 3

Select the specific location of the Excel workbook from the system like this. After that, click on the “Open” button.

Step 4

The “Navigator” dialog box will appear. Here you see, the Excel workbook “Employee” is being displayed that contains only one worksheet “Sheet1”. Now, choose the “Sheet1” checkbox and then click on the “Load” button as given below −

Repeat the similar steps for the second table emp_dept.xls to load the second table in the Power BI.

Step 5

You can see the two tables are imported in the Power BI desktop. Once you click on Sheet1 under the “Data” section, the complete information of the Employee table is displayed as shown below image −

Step 6

Switch to the “Home” tab and then click on the Transform data tile and then select the Transform data option as given below −

Step 7

The Power Query Editor window will appear. Click on the “Combine” tile, expand the Merge Queries tile, and select the “Merge Queries” option as given below −

Step 8

Another dialog box “Merge” will open. Choose the Sheet1(2) table from the drop-down menu as highlighted below image −

Step 9

Choose the “Inner(only matching rows)” option under the “Join Kind” section and select the common column Employee ID in both Table Sheet1 and Sheet1(2). Furthermore, click on the “OK” button.

Step 10

You can see only two rows are matched out of 14 rows, and their corresponding records are retrieved as shown in the below image.

Step 11

To showcase their department, you can click on the Sheet1(2) column bidirectional arrow select only the Department column and click on the OK button.

Left Join in Power BI

Step 1

Go to Step 5 of the previous example, and choose the “Left Outer(all from first, matching from second)” option under the “Join Kind” section. Also, select the common field Employee ID in both tables as depicted in the below image. Finally, users need to click the “OK” button.

Step 2

The finalized table is depicted in the below image −

Step 3

Suppose you wish to see the corresponding department of the matched values; in that case, you can click on the double arrow just adjacent to the Sheet1(2) column untick the Employee ID checkbox, and select only the Department checkbox. Moreover, click on the OK button.

Note − If the records are not matched, then null is retrieved in the Department column.

Conclusion

Before performing these methods, users need to properly load and transform the tables in Power BI. The step-by-step detailed explanation along with screenshots are described in this article to utilize the Inner Join and Left Outer join effectively.