Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Merge two tables without missing any rows and without duplicating values from both tables

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.1.PNG

 

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 image2.PNG

 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 image3.PNGI 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.4.PNG

Any suggestions will be soo appreciable.

Thanks,

Mohan V

 

 

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

1.PNG

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.

https://community.powerbi.com/t5/Desktop/Need-Help-to-get-resolve-on-multiple-queries-of-Custom-KPI/...

I ll be so thankful to you.

MohanV

View solution in original post

10 REPLIES 10
v-jiascu-msft
Employee
Employee

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] ) )0SUM ( ActualTable[Actual] ) )

SumPlanned =
IF (
    ISBLANK ( MIN ( 'PlanTable'[Planned Rev] ) ),
    0,
    SUM ( PlanTable[Planned Rev] )
)Merge two tables without missing any rows and without duplicating values from both tables02.jpg

 

 

 

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

Thanks for the suggestion @v-jiascu-msft.

But still getting the strange values as below.5.PNG

 

 

 

 

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.

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

@Anonymous

 

Hi Mohan V,

 

Did you solve your problem? What else can I do for you?

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

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.

1.PNG

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.

https://community.powerbi.com/t5/Desktop/Need-Help-to-get-resolve-on-multiple-queries-of-Custom-KPI/...

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

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

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.

Anonymous
Not applicable

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.

 

Merge two tables without missing any rows and without duplicating values from both tables01.jpg

 

 

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

@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.

 
 

4.PNG

 

 

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.