LOADING

Type to search

Maps & Joins in Microsoft Power BI

To Know more about the Different Corporate Training & Consulting Visit our website www.Instrovate.com Or Email : info@instrovate.com or WhatsApp / Call at +91 74289 52788

Data Analytics PowerBI

Maps & Joins in Microsoft Power BI

Share

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

Maps & Joins in Microsoft Power BI 29

 

  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.

Maps & Joins in Microsoft Power BI 30

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

Maps & Joins in Microsoft Power BI 31

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

Maps & Joins in Microsoft Power BI 32

  1. 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

Maps & Joins in Microsoft Power BI 33

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:

Maps & Joins in Microsoft Power BI 34

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.

Tags:

You Might also Like

Leave a Comment

Your email address will not be published. Required fields are marked *