Power BI offers powerful data transformation capabilities through Power Query Editor, and one of the most commonly used transformations is Merge Queries. Whether you're working with multiple data sources or structuring a relational model, knowing how and when to use different join types is essential.
In this blog, I will walk you through all types of joins available in Power BI Merge Queries, using a practical scenario of two tables – Employee and Department. While I’ve demonstrated this in my video tutorial, here’s a written summary to reinforce your understanding.

What Is Merge Queries in Power BI?

Merge Queries in Power BI is equivalent to performing a SQL join between two tables. You can join tables based on a common column or key, such as Department ID between the Employee and Department tables.
After merging, you can expand the desired columns from the second table into your main table.

Types of Joins in Merge Queries and When to Use Them

1. Left Outer Join (All from first, matching from second)

•What it does: Keeps all rows from the Employee table and brings matching rows from the Department
table.
•When to use: When you want a complete list of employees and their corresponding department
names (even if some employees are not assigned to any department).

2. Right Outer Join (All from second, matching from first)

• What it does: Keeps all rows from the Department table and brings matching rows from the Employee
table.
• When to use: When you want to list all departments and include employees wherever available (useful
for identifying departments with no staff).

3. Full Outer Join (All rows from both)

• What it does: Combines all rows from both tables, showing matches where available and nulls where
not.
• When to use: Ideal for comprehensive audits or reconciliation where you need to see all employees
and all departments, even if there's no link between them.

4. Inner Join (Only matching rows from both)

• What it does: Returns only the rows where there’s a match between Employee and Department.
• When to use: Best for focused analysis when you only want records with a valid relationship (e.g.,
employees assigned to departments).

5. Left Anti Join (Only non-matching rows from first)

• What it does: Returns only employees who don’t have a corresponding department.
• When to use: Useful for identifying data issues such as unassigned employees.

6. Right Anti Join (Only non-matching rows from second)

• What it does: Returns only departments that have no employees.
• When to use: Helpful for spotting under-utilized or inactive departments.

Understanding the behavior and use cases of each join type helps you clean, transform, and analyze data more effectively in Power BI. Whether you’re identifying mismatches, enriching your data, or conducting data audits, Merge Queries is a key tool in your Power BI toolkit.