Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
I have been searching and trying to findout a perfect solution for merging two tables without missing any data from both tables and without duplicating the values but i could'nt get anywhere.
I have two tables, one is Planned Revenue and another one is Actual Revenue.I would like to merge these two tables based on PM(For ex) using Full outer join.
But I have the tables data like Some of the PM's having Planned Rev but not Actual Rev, And some of the PM's having Actual Rev,but not Planned Rev. And Some of the PM's Exists in planned rev,not in actual rev. vise versa.
So when i am using full outer join it gets all rows from both tables.
But it giving soo wrong values like as in below image
But actual no is $1.47. I dont understand why i am getting these values.
When i tried inner join i get only the matching rows from both tables, that means if a PM exist in Planned rev table but not in Actual rev table then i dont get that PM in merged table.And i got the same values as above imageI would like to get a merged table like, there will be no data loss, and there will be no data duplicating.
If a PM having only Planned rev not actual rev then show actual rev as 0, without duplicating the values of planned rev, vise versa.
And also if a PM exist in planned rev but not in actual rev then those PM's also should be in list, and show the values of actual and planned rev accordingly what they have.
Any suggestions will be soo appreciable.
Thanks,
Mohan V
Solved! Go to Solution.
Thanks for your valuable time to spent.
I did every join to solve this, but i am unable to find out the exact solution that i need.
But i did solved it by creating another table(MonthYear) which is having the month and date and Mon-Year columns.
Then i created relationship among these three tables using Date column.
And when i made a bar cart with plan and actual rev, It is showing the perfect values by keeping Mon-Year of MonthYear table in X-Axis.
Thanks for spending your valuable time and giving suggestions.
If you dont mine, can you please look in this issue please.
I ll be so thankful to you.
MohanV
Hi, @Anonymous,
I guess you certainly have a table of all PMs. So add to the model, and create relationships with actual table and plan table. Then create two measures and a visual table. You can export the data from the visual table.
SumActual =
IF ( ISBLANK ( MIN ( 'ActualTable'[Actual] ) ), 0, SUM ( ActualTable[Actual] ) )
SumPlanned =
IF (
ISBLANK ( MIN ( 'PlanTable'[Planned Rev] ) ),
0,
SUM ( PlanTable[Planned Rev] )
)
Hi @Anonymous,
Please check these things below.
1. What are your formulas of "SumActual" and "SumPlanned"?
2. What are the values of "SumActual" and "SumPlanned" when you select other PM?
3. Please check the relationship of those tables.
3. Please post a little sample in text type if it's convenient with you.
@Anonymous
Hi Mohan V,
Did you solve your problem? What else can I do for you?
Thanks for your valuable time to spent.
I did every join to solve this, but i am unable to find out the exact solution that i need.
But i did solved it by creating another table(MonthYear) which is having the month and date and Mon-Year columns.
Then i created relationship among these three tables using Date column.
And when i made a bar cart with plan and actual rev, It is showing the perfect values by keeping Mon-Year of MonthYear table in X-Axis.
Thanks for spending your valuable time and giving suggestions.
If you dont mine, can you please look in this issue please.
I ll be so thankful to you.
MohanV
@Anonymous
Hi,
It's my pleasure. I am so glad you have solved your problem. You can mark your answer as solution. It will be helpful to others. Since you asked, I will try my best to help in a proper way.
Best Regards!
Dale
Create the full outer join as you have in the original post but then remove all of the value columns. Remove all of the duplications so you are simply left with a PM table, which will become your Dim - PM table.
Link your Planned Rev and Actual Rev table to this Dim - TM table. Now create a matrix with the rows being based on Dim - PM and its PM column. Bring your Planned Rev and Actual Rev into the "Values" column of your matrix.
Thanks for the reply @Anonymous.
I tried to understand what you have suggested but still not able to get what exactly that you have suggested.
Can you please give an example.
It will be so helpful.
Hi @Anonymous,
It seems that you have duplicate rows in your tables. Merge Queries doesn't delete duplicate rows (you can see in the image part 1), so you get the strange result. You can delete duplicate rows after merge. Click "Remove Duplicate", delete ",{PM}" from the function.
@Anonymous @v-jiascu-msft
Actually i forgot to mention that i have muliple values for the single pm as below image.
Then after Full outer merge if i remove the duplicate rows for the PM then i may lose the actual values also.
How can i merge the two tables as below image.
This is just the dummy data but the actual data table will be around 10000 rows, and PM's are around 5000, and each PM with multiple values.
Please suggest me.
Mohan V
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
99 | |
88 | |
73 | |
60 | |
58 |