Join me in merging the join kind

Power Bi is a Business Intelligence and Data Analytics Solution that helps us get insight into the data from different sources and allows us to prepare reports and dashboards.

What is Power BI used for?
Power BI is a set of software services, apps, and connectors that work together to transform disparate data sources into coherent, visually impressive, and interactive insights. Your data could be in the form of an Excel spreadsheet or a collection of hybrid cloud-based and on-premises data warehouses.

What is Merge in Power BI?
A merge queries operation connects two existing tables based on matching values in one or more columns.

What is the use of Merge in Power BI?
A merge query combines two existing queries to form a new one. All columns from a primary table are contained in a single query result, with one column serving as a single column containing a relationship to a secondary table. The related table contains all rows that have a common column value with each row from the primary table.

What is the join kind in Merge?
Combining two datasets in Power Bi using the merge query option. While performing merge queries you will be asked for a join kind ie the two datasets which you have decided to merge their rows should match the same. While performing a merge at least one column should be the same in both datasets.

When to use Join Kind?
In a certain situation, you require to merge the two datasets for appropriate or complete information for the preparation of an effective visualization

Let us try to understand the types of joins which is used for merging queries.

1. Outer Join:- In this type of join, we get the values that are present in both datasets, and missing values will not be shown.


a)Left Outer:- In this, the 1st table will be considered as the main table, and the 2nd table will be considered a secondary table. When we perform left outer join you will whatever values are present in the main table (1st table ) will be matched with the second table (Secondary table )

Let us try to understand this with an example

b)Right Outer Join:- In this, the secondary table will be considered as the primary table ie it keeps all the rows from the secondary table and brings the matching rows from the primary table.

Let us try to understand this with an example

c)Full Outer:- In this, you will get all the values present in both the data sets while doing merge operations ie which brings in all the rows from both the left and right tables

Let us try to understand this with an example
2. Anti join:- An anti join displays those values where there is no match found in the corresponding table a)Left Anti:– In this type of join, it brings in only rows from the primary table that don’t have any matching rows from the secondary table.

Let us try to understand this with an example
b)Right Anti:- It brings in only rows from the secondary table that don’t, have any matching rows from the primary table.
Let us try to understand this with an example

c)Inner Anti:- It brings only rows from both primary and secondary tables that have all the matching rows.
Let us try to understand this with an example

Conclusion: By looking into all these types of joins which can be used during the merge operations for effective visualization and for business analysis for the further decision-making process.