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

[aktripathi2506]

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 Number	Serial Number	date	Name
IN 12	111		AJK
IN 21	222		RIM
IN 25	123		TIM
IN 32	124		JAK
IN 40	137		PAUL
IN 45	146		KEN

Table B

Serial Number	date	Name	HOURS
111		AJK	3
222		RIM	5
123		TIM	7
124		JAK	2
137		PAUL	70
146		KEN	10

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 Number	Deadline	Deadline date	Name
IN 12	2 days		AJK
IN 91	5 days		RIM
IN 25	9 days		TIM
IN 32	6 days		JAK
IN 70	1 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.

[aktripathi2506]

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

@jahida @Habib @KGrice @Vvelarde

[jahida]

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.

[aktripathi2506]

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.

[v-qiuyu-msft]

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.

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

Best Regards,
Qiuyun Yu

[aktripathi2506]

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?