Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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 anywh...
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    9 years ago

    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.

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

    I ll be so thankful to you.

    MohanV

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
Anonymous
Not applicable
9 years ago

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.

 

 

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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