How to merge tables using Left Anti and Right Anti joins in Power BI?

Introduction

Numerous built-in options like Inner Join, Full Join, Left Outer join, and so on are embedded to merge and append tables in the Power query editor. In this article, we will discuss Left Anti and Right Anti joins in Power BI. Left anti-join is used to combine tables where the common column between two tables would be selected, and the unmatched records from the first table would be retrieved along with their corresponding data in the second column. Right Anti Join extracts records from the second table along with their associated field value in the first table and excludes those records that are found to be matched.

Left Anti join and Right Anti Joins in Power BI

Step 1

You have to create 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 move to the “Home” tab and choose the “Get data” option to load the Excel workbook as highlighted below image −

Step 3

Choose the employee workbook from the specified location of the system and then click on the “Open” button.

Step 4

The “Navigator” dialog box will open. The “Employee” workbook comprises only one worksheet “Sheet1”. Furthermore, select the “Sheet1” checkbox and then click on the “Load” button as given below −

Follow the same steps for the second table emp_dept.xls to load the second table in the power bi.

Step 5

If users click on Sheet1 under the “Data” section, the preview 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

Click on the “Combine” tile and then expand the Merge Queries tile, and select the “Merge Queries” option as depicted in the below image −

Step 8

You can select the Sheet1(2) table from the drop-down menu as highlighted below image −

Step 9

Select the “Employee ID” columns from the Sheet1 and Sheet1(2) table choose the “Left Anti(rows only in first)” option from the drop-down menu and click on the “OK” button.

Step 10

Therefore, the resultant merged table is generated that excludes those two rows that have the same Employee IDs in two distinct tables. In the second table, if the departments are not defined corresponding to Employee ID, then a null value is displayed.

Step 11

Suppose you want to retrieve the unmatched values from the second table. To achieve this, just go back to step 8 and select the Right Anti(rows only in second) option under the Join Kind section. And then click on the OK button.

Step 12

The unmatched records from the second table are displayed.

Conclusion

By utilizing these defined techniques, users can master their Power query editor skills and may practice more to become experts in core areas like Joins, DAX functions, and many more.