Power BI - Right Outer Join

Introduction

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 which is Left Outer Join. In this article, we will gain in-depth knowledge of Right Outer Join which is one of the most important types of joins. 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.

Procedure 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 −

Conclusion

It can be concluded that users may perform Right outer joins in Power BI by following the given steps in this article. The primitive process of joining two tables is to load the two tables separately in the Power BI desktop and then transform their data. The Power Query Editor window permits users to select the Right Outer join or any other desired join and generate the resulting table by selecting the merge queries option.