Table of Contents
A Left Join in Power BI is a data manipulation technique that combines two tables based on a common column, with all rows from the first table and matching rows from the second table. This is achieved by using the Merge Queries function in Power BI, where the first table is selected as the primary table and the second table is added as a related table. The result is a new table with columns from both tables, where the rows from the primary table are displayed along with any matching rows from the related table. This allows for the comparison of data from two tables, providing a comprehensive view of the data.
Perform a Left Join in Power BI (With Example)
The easiest way to perform a left join between two tables in Power BI is to use the Merge Queries function in the Power Query Editor.
The following example shows how to do so in practice.
Example: How to Perform a Left Join in Power BI
Suppose we have the following table in Power BI named data1 that contains information about the team name and points scored for various basketball players:
And suppose that we have another table named data2 that contains information about the team name and rebounds for various basketball players:
Suppose that we would like to perform a left join in which we keep all rows from the data1 table and join them with matching values from the Team column in the data2 table.
To do so, click the Home tab along the top ribbon, then click the Transform data icon:
This will bring up the Power Query Editor.
Next, click the Merge Queries icon in the Combine group of the Home tab.
Then click Merge Queries as New from the dropdown menu:
In the new window that appears, choose data1 as the first table, choose data2 as the second table, and choose Left Outer as the Join Kind.
Then click the header for the Team column in each table so that Power BI knows to use those columns for the join:
Once you click OK, the left join will be performed:
Next, click the left and right arrows on the header of the data2 column. Then check the box next to Rebounds to indicate that this column should be the only one included from data2 in the final merged table:
Once you click OK, the Rebounds column will be shown from the data2 table:
Once you exit out of the Power Query Editor, a message box will appear that asks if you’d like to apply your changes.
Click Yes.
You will then be able to see the new table named Merge1 in the Table view:
Notice that all of the rows from the left table (data1) are kept in the merged table while only the rows with matching values in the Team column from the right table (data2) are included in the merged table.
Any row in the Team column from data1 that did not have a matching value in the Team column from data2 simply have a blank value in the data2.Rebounds column.
Note: If you’d like, you can right click on the header named data2.Rebounds and rename the column to just Rebounds.
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: