Power BI - Full Outer Join

Introduction

Different kinds of joins are available in the Power Query Editor, like Left outer join, Right join, Inner join, and so on. Users may swiftly combine the tables and examine the result by employing these joins. In this article, we will explore the fundamental concepts of Full Outer Join where all the records from both tables are to be retrieved based on the equivalent value presented in the tables’ common column. If there are any missing matches in any of the tables, null values must be entered.

Full Outer Join in Power BI Desktop

Step 1

Click on the “Power BI Desktop” icon, switch to the “Home” tab select the “Get data” tile, and select the first option “Excel workbook” to load the two tables named Employee.xls and Department.xlsx separately.

Step 2

Select the file name from the specified location of the system and then click on the “Open” button.

Step 3

You can see the preview of the Employee table depicted in the Navigator window. Users need to choose the “Sheet1” checkbox and press the “Load” button as given below −

Users need to follow the same steps to load the second table Department.xlsx in the Power BI.

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

You must select the “Home” tab and then expand the Transform data tile and then select the Transform data option as highlighted below image −

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 “Full Outer(all rows from both)” from the list presented under the “Join Kind” and then click on the OK button.

Step 8

The resultant table is given below −

Step 9

Expand the “Sheet1(2)” column double arrow tick the Department checkbox and click on the OK button as given below −

You can see that all the rows from both tables are extracted.

Conclusion

It can be concluded that all the records from two distinct tables fetched by using Full Outer join. Users may become more proficient with Power BI skills through this simple technique.