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
|Item Number||Serial Number||date||Name|
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.
|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|
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.
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.
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.
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?
Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.
Click here to read more about the December 2021 Updates!
Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.
Mark your calendars and join us for our next Power BI Dev Camp!