duplicate entries after table query merge

[aleuer]

Hello!

I'm looking for a bit of help here. I'm newer to data merging and I'm running into a particular problem!  I'm importing the data from Excel (2 tables) and there was no unique identifier with no duplicates between them, so I created a join column so that they could be merged through a left outer. It worked! BUT- in each table there is a column for 'score'. Table 2 has multiple entries for errors (line items) related to a single data point in Table 1. So, the issue is post-merge is that the score is pulled in and counted for each error or line item where I only need 1 instance of the score so that I can average the values correctly. 

I have an example here.

The left visual shows an un-merged dataset and therefore what the actual average of score should be. The one on the right shows what I am dealing with post-merge. I've included COUNT of the score to show the issue. The data has to be sorted by Language, 

Any help is greatly appreciated!!

[v-yuezhe-msft]

@aleuer,

What is the join column? Could you please share the Excel file so that we can test? You can share Excel file via online service such as OneDrive.

Regards,
Lydia

[aleuer]

Hi Lydia, 

I've copied the truncated data here : https://sajan.box.com/shared/static/2rfhrkpm8ezmcsg4hfijadyky9yh1peg.xlsx

The troublesome columns are: 

Table 1:

Join = BA

Score = C

Table 2:

Join 2= L

Score = D

i've tried simply deleting the score column from table 2, but when the merge is complete it still duplicates for each line item listed from the JOIN. 

Thanks so much!

Amanda

[v-yuezhe-msft]

@aleuer,

I am unable to download your file as it is removed. Could you please re-upload it?

Regards,
Lydia