cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aktripathi2506
Helper IV
Helper IV

Problem with Left outer join when to create the relationship between tables

Hi,

 

I have two tables (Table A and Table B), they both are having millions of data.

In both the table I have a column name Serial number.

 

And when I create relation between these two tables based on the column Serial number I am able to do so very easily without any transformation.

but when I merge one table (Table A) with the third one (Table C) by left outer join then I am not able to create the relation, why?

 

With left outer join still all the item numbers should be same in Table A, only few more columns should be added with either the relevant values or by null.

 

Please correct me if I am wrong.

Also please let me know a possible way to perform this action. Because I need to connect all these three tables as I need their column information.

 

 

Data set example

Table A 

 

Item NumberSerial NumberdateName
IN 12111 AJK
IN 21222 RIM
IN 25123 TIM
IN 32124 JAK
IN 40137 PAUL
IN 45146 KEN

  

Table B

 

Serial NumberdateNameHOURS
111 AJK3
222 RIM5
123 TIM7
124 JAK2
137 PAUL70
146 KEN10

 

I am successfully able to create relation between thee two table based on column Serial Number.

 

Now I have table C from which I want to use the column Deadline date and number of days for the deadline.

 

Table C

 

Item NumberDeadlineDeadline dateName
IN 122 days AJK
IN 915 days RIM
IN 259 days TIM
IN 326 days JAK
IN 701 days 

KEN

 

For this I create a left join to get the deadline of all the items which are present in table A. But when I do merge query with left outer join I am not able to create the relation between Table A and Table B.

 

Please advise,

Thanks.

5 REPLIES 5
aktripathi2506
Helper IV
Helper IV

Guys please have a look into this problem if you have any idea

 

@jahida @Habib @KGrice @Vvelarde

jahida
Impactful Individual
Impactful Individual

Initial thought is that A used to have unique values in the column you're doing the relationship on, but no longer has unique values after the outer join.

thanks @jahida,

 

Yes to create the relation one of the table sould have unique value in that column.

 

What I did is I exported the data into excel and check for any duplicate but I did not get any.

 

And according to the defination of left join also it should have all the row from table A (first table). From 2nd table we only get additional column not row..right?

 

If it was able to make relationship before the join than why not after join.

 

Hi @aktripathi2506,

 

Based on my test, when you retrieve data from those three tables, then you can merge Table A with Table B use Left Outer join, it will return all of the checked column values from the Table A and matched records from the Table C. If there is no matched records in Table C, it will display as NULL in the merged table A. After apply this merge step, we can also build relationship between Table A and Table B. Please check attached .PBIX file.

 

z1.PNGz3.PNG


If you have any question, please feel free to ask.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ank you @v-qiuyu-msft.

 

I first merged two tables and then I tried creating the relation but it gives me error: one column should have unique value.

When I export the data in excel and check there is no duplicate row in both the table , I dont know why it is still saying that after merging the queries.

 

 

Now what I am thinking is: rather than creating any relation I am merging all three table.

 

Inner join table A and Table B and then I will do the left out join of this result with the table C.

 

but this process is very slow...it is taking a lot of time as my data is in millions of rows...is there a way to fasten the process? like by writing code in M rather than simiply merging by UI option.. or any thing else?

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors