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 an example. Following join types 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 the 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
The 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 a New option because we don’t want to disturb the 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 the first table, Department table as the second table, Dept Id is the common filed, and right outer join as the join type
Now you can see the new table result of the right outer join
Full Outer Join in Power BI
Let me select the Employees table as the first table, Department table as the second table, Dept Id is the common filed, and full outer join as the join type
Now you can see the result of Full outer join in a new table
Inner Outer Join in Power BI
Let me select the Employees and Department tables, Dept Id as the common field, an inner join as the join type
Now you can see the result of Inner join in a new table
