Performing an Inner Join in Power BI
Performing an Inner Join in Power BI, Are you struggling to combine data from two tables in Power BI?
Do you want to perform an inner join to merge data based on a common column? Look no further!
Performing an Inner Join in Power BI
In this article, we will show you how to perform an inner join in Power BI using the Merge Queries function in the Power Query Editor.
What is an Inner Join?
An inner join is a type of join that combines rows from two tables where the join condition is met.
In other words, it returns only the rows that have matching values in the specified columns. This is useful when you want to analyze data from two tables that have a common column.
Example: Performing an Inner Join in Power BI
Let’s say we have two tables in Power BI: data1 and data2. data1 contains information about basketball players, including team name and points scored.
| Team | Player | Points |
|---|---|---|
| Lakers | LeBron James | 25 |
| Celtics | Kevin Durant | 30 |
| Lakers | Anthony Davis | 20 |
data2 contains information about the same basketball players, including team name and rebounds.
| Team | Player | Rebounds |
|---|---|---|
| Lakers | LeBron James | 10 |
| Celtics | Kevin Durant | 12 |
| Warriors | Stephen Curry | 8 |
We want to perform an inner join to merge these two tables based on the Team column. Here’s how:
Step 1: Open the Power Query Editor
Click the Home tab in the top ribbon, then click the Transform data icon. This will open the Power Query Editor.
Step 2: Merge Queries
In the Power Query Editor, click the Merge Queries icon in the Combine group of the Home tab. Then, click Merge Queries as New from the dropdown menu.
Step 3: Select Tables and Join Kind
In the new window that appears, select data1 as the first table and data2 as the second table. Choose Inner as the Join Kind.
Step 4: Select Join Columns
Click the header for the Team column in each table to select it as the join column.
Step 5: Select Columns to Include
Once you click OK, the inner join will be performed.
Click the left and right arrows on the header of the data2 column, then check the box next to Rebounds to include it in the final merged table.
Step 6: Apply Changes
Click OK to apply the changes. A message box will appear asking if you want to apply your changes.
Click Yes.
Results
The resulting table will be named Merge1 and will contain only the rows where the value from the Team column appears in both tables.
| Team | Player | Points | Rebounds |
|---|---|---|---|
| Lakers | LeBron James | 25 | 10 |
| Celtics | Kevin Durant | 30 | 12 |
Conclusion
Performing an inner join in Power BI is a straightforward process using the Merge Queries function in the Power Query Editor.
By following these steps, you can combine data from two tables based on a common column and analyze the results. Remember to select the correct join columns and choose the columns you want to include in the final merged table.
