Joins In Power BI
In this article, we will discuss how to create hierarchies in Power BI.
Topics Covered:
- What is Joins
- Categories of Joins
- Creating Joins:
- Inner Join
- Left Outer Join
- Right Outer Join
- Full Outer Join
- Left Anti Join
- Right Anti Join
Joins in Power BI
Joins in Power BI are used to combine data from multiple tables into a single view. Let’s take an example to understand, you might have one table that contains sales data and another table that contains customer information. By joining these two tables on a common field (such as customer ID) you can create a single view that includes both sales data and customer information.
Inner Join
An inner join returns only the records that have matching values in both tables being joined. It combines rows from both tables based on a common column known as the join key. The result of an inner join will only include rows where there is a match in both tables.
Creating Inner Join
Step 1: Select the “Sales” query and then select Merge queries.
Step 2: In the Merge dialog box under right table for merge, select “Country”.
Step 3: In the Sales table, select the “Customer ID” column.
Step 4: In the Countries table, select the “Order Id” column.
Step 5: In the Join kind section, select “Inner Join”.
Step 6: Select “OK”
Left Outer Join
This type of join returns all the rows from the left table and matching rows from the right table. If there are no matching rows in the right table, it returns NULL values for the right table columns.
Creating Left Outer Join
Step 1: Select the Sales query and then select Merge queries.
Step 2: In the Merge dialog box, under Right table for merge, select “Country”.
Step 3: In the Sales table, select the “Customer ID” column.
Step 4: In the Countries table, select the “Order ID” column.
Step 5: In the Join kind section, select “Left Outer Join”.
Step 6: Select “OK”.
Right Outer Join
This type of join returns all the rows from the right table and matching rows from the left table. If there are no matching rows in the left table, it returns NULL values for the left table columns.
Creating Right Outer Join
Step 1: Select the Sales query, and then select Merge queries.
Step 2: In the Merge dialog box, under Right table for merge, select “Country”.
Step 3: In the Sales table, select the “Customer ID” column.
Step 4: In the Countries table, select the “Order ID” column.
Step 5: In the Join kind section, select “Right Outer Join”.
Step 6: Select “OK”
Full Outer Join
This type of join returns all the rows from both tables with NULL values in the columns where there is no match.
Creating Full Outer Join
Step 1: Select the Sales query, and then select Merge queries.
Step 2: In the Merge dialog box, under Right table for merge, select “Country”.
Step 3: In the Sales table, select the “Customer ID” column.
Step 4: In the Countries table, select the “Order ID” column.
Step 5: In the Join kind section, select “Full Outer Join”.
Step 6: Select “OK”
Left Anti Join
Creating Left Anti Join
Step 1: Select the Sales query, and then select Merge queries.
Step 2: In the Merge dialog box, under Right table for merge, select “Country”.
Step 3: In the Sales table, select the “Customer ID” column.
Step 4: In the Countries table, select the “Order ID” column.
Step 5: In the Join kind section, select “Left Anti Join”.
Step 6: Select “OK”
Right Anti Join
Step 1: Select the Sales query, and then select Merge queries.
Step 2: In the Merge dialog box, under Right table for merge, select “Country”.
Step 3: In the Sales table, select the “Customer ID” column.
Step 4: In the Countries table, select the “Oder ID” column.
Step 5: In the Join kind section, select “Right Anti Join”.
Step 6: Select “OK”.
Conclusion
By following these steps, you can create joins between tables in Power BI and combine your data in powerful ways.
