Left Join Between Two Tables in Power BI

Left Join Between Two Tables in Power BI, Power BI is a powerful tool for data analysis and visualization, allowing users to create dynamic reports from various data sources.

One of the essential skills in Power BI is understanding how to join tables efficiently.

If you want to combine data from two tables based on a common column, a left join is often the best approach.

Left Join Between Two Tables in Power BI

In this article, we’ll take an in-depth look at performing a left join using the Merge Queries function in the Power Query Editor. Let’s dive in!

What is a Left Join?

Before we proceed, let’s clarify what a left join entails. A left join will return all records from the left table (in this case, data1), and the matched records from the right table (data2).

If there’s no match, the result is NULL from the right side. This is particularly useful when you want to ensure that all data from your primary table is preserved, while still incorporating relevant information from related tables.

Practical Example: Performing a Left Join in Power BI

For this example, let’s assume we have two tables in Power BI:

1. data1: This table holds information about basketball players, specifically the team name and points scored:

Columns: Team, Points

Sample Data:
Team Points
Bulls 95
Lakers 100
Warriors 110

1. data2: This table contains data about the same basketball players, focusing on rebounds:

Columns: Team, Rebounds

Sample Data:
Team Rebounds
Bulls 50
Warriors 60 Step-by-Step Guide to Perform a Left Join in Power BI

Step 1: Open Power Query Editor First things first, you’ll need to open the Power Query Editor. To do this, click the Home tab in the Power BI interface, and then click on the Transform Data icon.

This action will launch the Power Query Editor, where you can manipulate your data.

Step 2: Merge Queries Once in the Power Query Editor, look for the Combine group in the Home tab. Click on the Merge Queries icon. From the dropdown menu, select Merge Queries as New.

This option is handy because it creates a new table from the merge operation, allowing you to preserve the original tables.

Step 3: Choose Tables and Set Join Type In the new Merge dialog that appears, you will configure your merge settings:

1. Select the First Table: Choose data1 from the dropdown menu.Select the Second Table: Choose data2.Join Kind: Select Left Outer from the options provided. This tells Power BI that you want to keep all records from data1, regardless of matches in data2.

Step 4: Select the Key Columns to Join On Next, you need to specify the columns that contain the corresponding values for the join: Click on the Team column header for data1.

Click on the Team column header for data2 as well.With these selections made, click OK to perform the left join.

Step 5: Choose Columns to Include in the Merged Table After clicking OK, you will see a new table created from the merged data.

You will notice a new column representing data2. To refine this and keep only the Rebounds column, click on the arrow in the header of the new column and a dropdown will appear.

Check the box next to Rebounds to include only this column in the final output. Uncheck any unwanted columns. When you’re ready, click OK.

Step 6: Review and Apply Changes Now, return to the main view of the Power Query Editor. You should see the merged result with columns from both tables.

Before exiting, a dialog box will prompt you to apply your changes. Click Yes to apply them.

Step 7: View Your Merged Table After exiting the Power Query Editor, navigate to the report view. You will now see a new table named Merge1.

In this table, all rows from data1 are retained while the corresponding Rebounds from data2 are shown.

If there are any teams from data1 without matching entries in data2, those rows will display a blank value for the Rebounds column.

Conclusion

You’ve just learned how to perform a left join in Power BI using the Power Query Editor!

This powerful feature makes it easy to combine data from different tables based on a common column, enriching your reports and analyses with added context while ensuring that no valuable data from your primary table is lost.

With practice, you can master the art of data manipulation in Power BI, creating insightful visualizations and dynamic reports that can drive your analyses forward. Happy reporting!

Power BI Archives »