Forum Discussion
Table Merge creates duplicate records
The problem lay with the Data.
The LEFT JOIN functionality of PowerBI works perfectly.
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.
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.
Th steer here spot on - thanks - duplicate records in the right hand linked table caused duplicate rows from left hand linked table. Unlike Vlookup which only returns first match, the Access query returns a record for each match in the right table. Removing duplicates in right hand table solves problem.