Power BI supports all major joins that are available in SQL. Let me show you how to use joins in Power BI to join multiple tables with an example. The following join types are the Standard join types in Power BI and SQL
- 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 SQL Right table.
How to Perform Joins in Power BI
To demonstrate the Power BI join types, we are going to use the tables that we imported in the Load Data from Multiple Data Sources article.
To show the join operations on existing tables, please click the Edit Queries option under the Home tab.
The below Power BI screenshot show you the data present in the Employees table
The following screenshot shows you the data present in the Department Table.
To perform Power BI 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: It merges the second table inside this original table.
- Merge Queries as New: It creates a new table with the result of joining the first and second tables.
Let me select the Employees table, and select Merge Queries as a New option because we don’t want to disturb the original table
The following window will open. Use this window to select the Second Table, Join Type, and standard 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 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. And the Dept Id is the common filed, and right outer join as the join type.
Now you can see the new table result of the Power BI 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. Here, we select Dept Id is the common filed, and full outer join as the join type.
Now you can see the result of Power BI Full outer join in a new table
Inner 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 Power BI Inner join in a new table
