Power Query Editor - Merge Queries (Part 1)

---

---

What is Right Outer Join?

When two tables require their common column field values to match, a right outer join is used to combine them, and all the records of the second table are extracted along with their matching field value from the first table. It is just the opposite of another type Left Outer Join. The users must transform the data before performing the Right Outer Join in the power query editor.

For Example

Assume that we have two tables named Employee and Department. The employee table contains five columns Employee ID, Name, Designation, Salary, and Age. The department table comprises two columns named Employee ID and Department. We have to apply Right Outer Join to merge these tables.

Steps to Conduct Right Outer Join in Power BI Desktop

Step 1 − You must open "Power BI Desktop" and click on the "Home" tab and expand the "Get data" tile and further, choose the "Excel workbook" to load the employee and department workbook separately.

Step 2 − Select the file name from the specified location of the system and then click on the "Open" button.

Step 3 − Moreover, the preview of the Employee table is depicted. Users need to choose the "Sheet1" checkbox and press 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.

Step 4 − 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 5 − User can select the "Home" tab and then click on the Transform data tile and then select the Transform data option as given below −

Step 6 − 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 7 − The Merge dialog Box will open where users can select the "Employee ID" column in both tables as it contains the few common IDs. Now, select the "Right Outer(all from the second, matching from the first)" from the list presented under the Join Kind and then click on the OK button.

Step 8 − The resultant table is shown below image −

Left Anti Join 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.

For Instance

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 3 − 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 4 − If users click on Sheet1 under the "Data" section, the preview of the Employee table is displayed as shown below image −

Step 5 − Switch to the "Home" tab and then click on the Transform data tile and then select the Transform data option as given below −

Step 6 − 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 7 − You can select the Sheet1(2) table from the drop-down menu as highlighted below image −

Step 8 − 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 9 − 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 10 − 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 11 − The unmatched records from the second table are displayed.