Getting duplicates when merging a table

[Anonymous]

Hi all,

My teammate @JPY  and I are trying to merge two tables which are from different data sources. There are a couple of columns/variables that overlap. However, each of the report is a breakdown of the products being sold but because they are from different data sources, the exact variable which represents the line item does not coincide. For example, the unique value for each line item for report A would be WBS Element whereas the unique value for each line item for report B would be Count Distinct(Units) . Of course, at an aggregate level, they coincide in terms of sales order number and quote number, but at a line item level, they are unable to perfectly match each other and this creates duplicates when we merge these two tables in the PowerBI query editor. Our revenue also ends up getting double counted. 

Is there any way to resolve this?

Before the merge:

After the merge:

We've been scratching our heads over this for a couple of weeks so any input will be greatly appreciated 🙂 

[amitchandak]

To me, it seems like the key is repeating. Can you share some sample data with the sample output?

[Anonymous]

Hi @amitchandak ,

Here's the link for the sample.

https://drive.google.com/file/d/1nt_6H2O2aEMayKn6NdtqXMCY_FjmmaWo/view?usp=sharing

Thank you!