Now we will learn about Joins in Power BI.

What are joins: Operations performed on tables on different platform where data is stored in structured format. We can perform join in SQL, Oracle, MYSQL etc.

Types of Joins:

1. Inner Join

2. Left Outer Join

3. Right Outer Join

4. Full Outer Join

To explain these joins I will give an example, let’s take two tables, Table A and Table B .Table A and Table B have one column common for customer name and other columns are different in both tables.

Let’s we will see this in form of circles, Blue circle is for Table A and Green circle is for Table B. They both have one intersection field i.e. customer name (same in both tables).

        Table A            Table B

 

1. Inner Join: In this Join the resultant table will be having its intersection fields from both tables. In above example the fields those are common in both table will be present as a result of Inner Join. So only matching rows will present for the result and It will discard the remaining rows from both tables.

Here the Yellow portion will be the result. We will take matching row from both tables and glued all information for these records from both table, i.e. we will include all column for these matching rows from both tables.

2.Left Out Join: In this Join we take all rows from primary table and add only matching rows (records) from second table in the primary table. In this case Primary table will be the one present on Left side, we cannot discard rows from left table, so in resulting table we will have matching rows from right table i.e. Table B and all rows from left table i.e. Table A. In this case all information is fetched for matching rows from Table B and combine results with respective rows from Table A. In below example the yellow portion is for Left Out Join:

    Table A             Table B

3.Right Join: In this case table on right side is the primary table hence rows from Table B cannot be discards. So, in resultant table we will have all rows from right table and matching rows from the left table i.e. From Table A and will discard remaining rows from the left table. In this case all information is fetched for matching rows from Table A and combine results with rows from Table B.

In below example yellow portion is showing Right Out Join:

 

Table A                                     Table B

4. Full Outer Join: In full outer join we will not discard any of rows from any tables. It is the combination of all rows with all information from both tables. In below example yellow portion is showing full outer join:

 

Table A            Table B

It was about different type of joins.

Now we will discuss to create few joins from above joins.

Joins with Duplicate values: Now I will discuss about Joins with Duplicate values.

When we have two tables and column that we are joining have duplicate values in the second table, is the example of joins with duplicate values.

If we have Table A and Table B. Table A has one column Order no. and that one is present in Table B as well but in Table B Order no. is duplicated and have more than one row for same order number.

Now if we want to make Inner join from these two tables, we need to pick only matching rows from both tables to make Inner Joins.

So, we will create duplicate rows form Table A to arrange all records from Table B

 

e.g.

Table A:

Order No.	Store	Paid
001	JK store	Yes
002	Dev store	No
003	RK store	Yes
004	Deep store	Yes

 

Table B:

Order No.	Item	Qty
001	Pen	2
001	Pencil	3
002	Bag	2
003	Eraser	5
003	Sharpener	5
003	Copy	4

 

Now we need to create an Inner Join from both tables for column Order No… We have two rows for order 001, one row for order 002 and three rows for order no. 003 in Table B.

As for inner joins we need to take all matching rows from both tables for column Order No.

In this case our resultant for Inner Join will be:

Here we can see we have two duplicate rows for order no. 001 and three duplicate rows for order 003

So this was about joining duplicate rows for any join.

 

Now we will learn about Joining on multiple fields:

Sometimes we don’t have one field as a unique identifier and we need to join more than one field to make it as a unique identifier for different records.

e.g.

Table A:

Order No.	Store	Paid
001	JK store	Yes
002	JK store	No
001	RK store	Yes
003	RK store	Yes

 

Table B:

Store	Order No.	Item	Qty
JK store	001	Pen	2
RK store	001	Pencil	3
Deep store	002	Bag	2
JK store	003	Eraser	5
RK store	003	Sharpener	5
RK store	003	Copy	4

Now if we want to make Left Outer join from these two above tables for field Order No… As we know in Left Outer field we need to take all rows from left table (Table A) And matching rows from Right table (Table B)

So, in our example we have two matching rows for 001 and one matching row for 002 and three matching rows for order no. 003. But if we create a table with these records it will give incorrect information about records.

To create correct data, we need to combine one more record with Order No. to uniquely identified records.

We will take Store and will check result for combination of both fields, by this way we will get two unique fields for Ordre No. 001, one record for 001/JK store and one 001/RK store, And for Order No. 002 (002/JK store) we don’t have any result for this combination in Table B and for Order No. 003 we have two matching rows (003/RK store) matching rows.

Hench our Left Outer Join will be:

Order No.	Store	Paid	Store	Order No.	Item	Qty
001	JK store	Yes	JK store	001	Pen	2
001	RK store	Yes	RK store	001	Pencil	3
002	JK store	No
003	RK store	Yes	RK store	003	Sharpener	5
003	RK store	Yes	RK store	003	Copy	4

 

It was all about Joins in Power BI.