Perform a Left Join in Power BI
Perform a Left Join in Power BI, Are you looking to combine two tables in Power BI using a left join? If so, you’ve come to the right place.
Perform a Left Join in Power BI
In this article, we’ll walk you through the process of performing a left join using the Merge Queries function in the Power Query Editor.
What is a Left Join?
A left join is a type of join that retrieves all rows from the left table (data1) and the matching rows from the right table (data2).
If no match is found, the result will include blank values for columns from the right table.
This method is particularly useful when you want to keep all records from the primary table while enriching the data with information from a secondary table.
Example: Performing a Left Join in Power BI
Let’s dive into an example that showcases how to perform a left join in Power BI.
Step 1: Prepare Your Data
Assume you have two tables in Power BI:
- Table
data1: This table contains information about basketball players, including their team name and points scored. Team Player Points Lakers LeBron James 25 Celtics Kevin Durant 30 Lakers Anthony Davis 20 - Table
data2: This table contains data about team rebounds for various players. Team Player Rebounds Lakers LeBron James 10 Celtics Kevin Durant 12 Heat Jimmy Butler 8
Step 2: Open Power Query Editor
To get started with the left join, click on the Home tab located in the top ribbon.
Then, select the Transform Data icon, which will launch the Power Query Editor.
Step 3: Merge Queries
Once in the Power Query Editor, navigate to the Home tab and click the Merge Queries icon found in the Combine group.
From the dropdown menu, choose Merge Queries as New.
Step 4: Choose Tables and Join Type
In the Merge window that appears, select data1 as the first table and data2 as the second table. For the Join kind, select Left Outer.
This specifies that we want to keep all rows from data1 and match them with data2.
Step 5: Select Join Columns
Click on the header for the Team column in both tables; this tells Power BI which columns to use for the join.
Step 6: Complete the Join
After clicking OK, the left join will be executed. You will see a new column corresponding to table data2.
Click the left and right arrows on the header of the data2 column, then check the box next to Rebounds to include only this column in the final merged table.
Step 7: Apply Changes
Click OK to finalize your selection. A message box will prompt you to apply your changes; simply click Yes.
Viewing the Results
You will now see a new table named Merge1 in the Table view. In this table, all rows from data1 (the left table) are included, while the matching values from the data2 table (the right table) are displayed.
For any team records in data1 that do not have corresponding entries in data2, you will see blank values in the data2.Rebounds column.
Here’s how the final merged table looks:
| Team | Player | Points | Rebounds |
|---|---|---|---|
| Lakers | LeBron James | 25 | 10 |
| Celtics | Kevin Durant | 30 | 12 |
| Lakers | Anthony Davis | 20 |
Bonus Tip: You can right-click on the header data2.Rebounds and rename it simply to Rebounds for clarity.
Conclusion
Performing a left join in Power BI is straightforward with the Merge Queries function in the Power Query Editor.
By following these steps, you can easily combine data from two tables, ensuring that you keep all records from your primary table while enriching your analysis with relevant data from a secondary table.
