Joins in Power BI

Power BI supports all major joins that are available in SQL. In this article we will show you, how to use joins in Power BI to join multiple tables with example. Following join type are the common join types in SQL and Power BI

Inner Join: Returns the rows present in both Left and right table only if there is a match. Otherwise, it returns zero records.
Full Outer Join: It returns all the rows present in both the Left and right table.
Left Outer join: It returns all the rows present in the Left table and matching rows from the right table (if any).
Right Outer Join: Returns matching rows from the left table (if any), and all the rows present in the Right table.

How to Perform Joins in Power BI

To demonstrate this join types, we are going to use the tables that we imported in Load Data from Multiple Data Sources article.

In order to perform join operations on existing tables, please click Edit Queries option under the Home tab.

Below screenshot will show you the data present in the Employees table

Following screenshot will show you the data present in the Department Table.

To perform Join operations on any table, we have to use the Merge Queries button present in the Home tab

This drop-down list has two options:

Merge Queries: This will merge the second table inside this original table.
Merge Queries as New: This will create a new table with the result of joining first and second table.

Let me select the Employees table, and select Merge Queries as New option because, we don’t want to disturb original table

Following window will be opened. Use this window to select the Second Table, Join Type, and common column filed in both the tables.

We are selecting the Department table as the second table.

Now you can see the list of supporting join types.

Left Outer Join in Power BI

For now, we are selecting the Left Outer Join

Next, we selected the DeptID from Employees and Department table as the common fields

Now you can see the new table called Merge1 with all the fields from the Employees table along with one extra filed of values table

Click on the table on any row will show you the respective table information

Please click on the right corner of the Department column header. This will open the following window. Use this window to select the required fields from the department table.

Let me select the Department name from the department table, and click OK

Now you can see the Department name merge with the Employee table.

Right Outer Join in Power BI

Let me select the Employees table as first table, Department table as second table, Dept Id is the common filed, and right outer join as the join type