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

Re: Table Merge creates duplicate records

I had the same problem and found my solution so thought I'd share it, for what its worth.

 

I had a very large table of data and another table of account details that I was merging to my first data set, but was getting duplicate rows.

 

There was a unique key to link between the 2 tables but hadn't created a relationship on the model window. As it turns out it was a many to many relationship and this is what caused the dulpicates. Since the account table was only there to extract additional info to my first data set, in the account table in the Power Query editor window I simply clicked on my key column and selected remove duplicates to force a many to one relationship and this removed the duplication on merge.

Highlighted
Frequent Visitor

Re: Table Merge creates duplicate records

I don't really get why the people commenting after my comment are still looking for an answer!!!!

 

my comment was litterally a valid solution, will I actually didn't really understand how merge works back then when I looked for this post, people are still missing merge with the regular vlookup function.

 

vlookup looks only for the first value in a column.

merge brings all the instances of this value no matter how many there are, and it duplicates the value in the first table to match that count, it works this way and there is no problem in this, the logic is different (it simply doesn't know which instance you wish to have so it brings you all of them).

 

all you have to do is to remove duplicates, you have to do it in the second table you're merging with (before you do the merge), or you have to do it the way I described in my last "solution" comment (you may look here in the previous comments).

 

please mark my comment as a solution so people don't keep getting lost everytime they enter this thread.

 

Regards for all of you.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors