Power BI supports all the main 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 the left and right table only if there is a match. Otherwise, it returns zero records.
- Full outer join: returns all rows present in the left and right table.
- Left outer join - Returns all rows present in the left table and matching rows in the right table (if any).
- Right outer join - Returns matching rows from the left table (if applicable) and all rows present from the right SQL table.
How to join in Power BI
To demonstrate Power BI join types, we'll use the tables we imported in the article Load data from multiple data sources.
To display join operations on existing tables, click the Edit queries option under the Home tongue.
<>The following Power BI screenshot shows the data present in the Employees table
The following screenshot shows 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 on the Home tongue
This drop-down list has two options:
- Merge queries: Merge the second table into this original table.
- Merge queries as new: Create 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 one option because we don't want to alter the original table
The following window will open. Use this window to select the second table, the join type, and the archived standard column in both tables.
We are selecting the Department table as the second table.
Now you can see the list of supported join types.
Left outer join in Power BI
For now, we are selecting the left outer join
Next, we select the DeptID from the Employees and Department table as common fields
Now you can see the new table called Merge1 with all the fields from the Employees table along with an additional file of the values table
Click on the table in any row to show you the information of the respective table
Click in the right corner of the Department column heading. 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 that the Department name is merged with the Employee table.
Right outer join in Power BI
Let me select the Employees table as the first table, the Department table as the second table. And the department ID is the common file and the right outer join as the join type.
Now you can see the result of the new Power BI right outer join table
Full outer join in Power BI
Let me select the Employees table as the first table, the Department table as the second table. Here, we select Dept Id is the common file and the full outer join as the join type.
You can now see the result of the Power BI full outer join in a new table
Internal join in Power BI
Let me select tables Employees and Departments, Department ID as common, an inner join as join type
You can now see the result of the Power BI inner join in a new table
