cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aleuer Frequent Visitor
Frequent Visitor

duplicate entries after table query merge

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!!

image.png

3 REPLIES 3
Moderator v-yuezhe-msft
Moderator

Re: duplicate entries after table query merge

@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

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

Re: duplicate entries after table query merge

 

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

Moderator v-yuezhe-msft
Moderator

Re: duplicate entries after table query merge

@aleuer,

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

Regards,
Lydia

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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors