Skip to main content

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

  1. Inner Join: Returns the rows present in both the left and right table only if there is a match. Otherwise, it returns zero records.
  2. Full outer join: returns all rows present in the left and right table.
  3. Left outer join - Returns all rows present in the left table and matching rows in the right table (if any).
  4. 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.

joins-in-power-bi-1-3974668

The following Power BI screenshot shows the data present in the Employees table

joins-in-power-bi-2-1937040

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

joins-in-power-bi-3-6668174

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.
joins-in-power-bi-4-3514335

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

joins-in-power-bi-5-5810264

The following window will open. Use this window to select the second table, the join type, and the archived standard column in both tables.

joins-in-power-bi-6-2491038

We are selecting the Department table as the second table.

joins-in-power-bi-7-1578265

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

joins-in-power-bi-8-9965058

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

joins-in-power-bi-9-5048694

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

joins-in-power-bi-10-9039746

Click on the table in any row to show you the information of the respective table

joins-in-power-bi-11-7901567

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.

joins-in-power-bi-12-4538219

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

joins-in-power-bi-13-4506339

Now you can see that the Department name is merged with the Employee table.

joins-in-power-bi-14-6134242

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.

joins-in-power-bi-15-4156651

Now you can see the result of the new Power BI right outer join table

joins-in-power-bi-16-6712831

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.

joins-in-power-bi-17-9792915

You can now see the result of the Power BI full outer join in a new table

joins-in-power-bi-18-4511537

Internal join in Power BI

Let me select tables Employees and Departments, Department ID as common, an inner join as join type

joins-in-power-bi-19-7406899

You can now see the result of the Power BI inner join in a new table

joins-in-power-bi-20-9503405

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

  1. Inner Join: Returns the rows present in both the left and right table only if there is a match. Otherwise, it returns zero records.
  2. Full outer join: returns all rows present in the left and right table.
  3. Left outer join - Returns all rows present in the left table and matching rows in the right table (if any).
  4. 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.

joins-in-power-bi-1-3974668

The following Power BI screenshot shows the data present in the Employees table

joins-in-power-bi-2-1937040

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

joins-in-power-bi-3-6668174

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.
joins-in-power-bi-4-3514335

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

joins-in-power-bi-5-5810264

The following window will open. Use this window to select the second table, the join type, and the archived standard column in both tables.

joins-in-power-bi-6-2491038

We are selecting the Department table as the second table.

joins-in-power-bi-7-1082190

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

joins-in-power-bi-8-9965058

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

joins-in-power-bi-9-5048694

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

joins-in-power-bi-10-8356239

Click on the table in any row to show you the information of the respective table

joins-in-power-bi-11-2451044

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.

joins-in-power-bi-12-2478116

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

joins-in-power-bi-13-3941694

Now you can see that the Department name is merged with the Employee table.

joins-in-power-bi-14-2600573

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.

joins-in-power-bi-15-8476606

Now you can see the result of the new Power BI right outer join table

joins-in-power-bi-16-9809371

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.

joins-in-power-bi-17-6850516

You can now see the result of the Power BI full outer join in a new table

joins-in-power-bi-18-3028097

Internal join in Power BI

Let me select tables Employees and Departments, Department ID as common, an inner join as join type

joins-in-power-bi-19-3162664

You can now see the result of the Power BI inner join in a new table

joins-in-power-bi-20-1847691
R Marketing Digital