Maps & Joins in Microsoft Power BI

 

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

  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

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.

 

Subscribe to our mailing list

* indicates required

Looking for Corporate Training ? Reach out to us at Akriti.Lal@instrovate.com

Reach out to us if you are looking for Corporate Training to Build The Next Generation Analytical Workforce with an in-depth understanding of  Exploratory Data Analysis , Data Visualisation, Data Analytics , AI First , Machine Learning & Deep Learning Training & Consulting helping them to take Data Informed Decision at each stage of the business. 

We understand that At the present times , the Entire Industry is in a Tranformation stage with the Softwares  being rebuilt with Artificial Intelligence Capabilities .

We need SMART WORKFORCE for the SMART SOFTWARES to reap the maximum return . 

 

Whatsapp at +91-9953805788 or email at - akriti.lal@instrovate.com if you would like to know more . 

 

  • Corporate Tableau Training in Gurgaon
  • Corporate Data Analytics Training in Gurgaon
  • Corporate Microsoft Power BI Training in Gurgaon
  • Corporate Microstrategy Training in Gurgaon
  • Corporate Google Data Studio Training in Gurgaon
  • Corporate Python Training in Gurgaon
  • Corporate Advance Analytics in R Programming Training in Gurgaon
  • Corporate Machine Learning Training in Gurgaon
  • Corporate Deep Learning Training in Gurgaon
  • Corporate Data Visualization Training in Gurgaon

 

Address

Instrovate Technologies

Noida, Gurgaon

 

akriti.lal@instrovate.com 

Hit Your Refresh Button To Rise Higher
Print Print | Sitemap
All rights reserved @ Instrovate Technologies
GSTIN : 09AAECI6862K1Z1

Terms Of Services    Privacy Policy    Disclaimer     Refund Policy    


Call

E-mail