Forum Discussion
Table Merge creates duplicate records
Hi Folks,
I am stumped.
I am trying to merge two tables into a new table using a LEFT JOIN. Both tables have unique records on each row. When I join the tables, BI creates duplicate rows on some records for no apparent reason. The duplicates are identical in every way.
Can anybody explain this? Is this a bug?
The problem lay with the Data.
The LEFT JOIN functionality of PowerBI works perfectly.
46 Replies
Hi people.
I've been looking for this issue with no luck as well until I founded a valid work around.
the work around goes like this ::
-Merge your sheets as you like (you don't have to merge on more than one column, one is fine).
-Add index column
-Expand your merged column (this will create duplicate values, the main issue we're looking into).
-since the values in the "index column" were purely unique, now Remove duplicates using index column.
-and now your data is back to original as it was inteded to be.
othmanalazzam wrote:Hi people.
I've been looking for this issue with no luck as well until I founded a valid work around.
the work around goes like this ::
-Merge your sheets as you like (you don't have to merge on more than one column, one is fine).
-Add index column
-Expand your merged column (this will create duplicate values, the main issue we're looking into).
-since the values in the "index column" were purely unique, now Remove duplicates using index column.
-and now your data is back to original as it was inteded to be.
My duplicate issue is solved by applying this method. Thank you very much.
I've been working with a sales table and trying to create dimension tables from the table with all of the fields. I've been frustrated, because I could not tell where the duplicate rows of data were coming from; however, could tell they happened after one of the two merge query steps.
I can affirm that having a unique index prior to the merge, and then removing duplicates from this field works.
Does anyone know why this occurs?
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.
I just ran into the same problem and fixed it by joining the two tables on multiple fields. For example, let's say a transaction number is the unique field you are joining on, expand the join to include other fields if possible. So your joins would be on transaction number, time, and amount.
- Yes, that's what I've done too. However, even that solution created some duplicates as we don't have a one to one mappings in some cases. The only solution I could come up with (and one that would've slow down the query significantly) was to remove duplicates against the columns which then were used in merge. To make sure there's only one line per particular combination of columns. It's similar to how vlookup works. It brings up only the first occurrence for duplicates. However, since I'm new to PQ there might be things which I am not aware of. Thus waiting for some more experienced users to chip in.
I've had the same problem for some time now. It looks like it's 'fixed' after changing the privacy settings to 'Always ignore Privacy Level settings'.
I'm facing the same trouble here. Not sure why merging 2 tables create duplicates. Have anyone got a work around this?
Update 9/10 : Problem Solved. My original first table already have got unique rows hence I should have merged it as aggregated values otherwise it will further expand the table, causing duplicates. Also, it helps to use and select more than one field as the key to look up if you need that level of granularity.
Hi DataGuy2016,
According to your description, I just tested it in my environment. The Merge Queries option works all fine for me without the issue.
I assume the issue is with your data. Could you post your table structure with some sample data which can reproduce the issue? It's better to share a sample pbix file. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.:smileyhappy:
In addition, here is a similar thread for your reference.:smileyhappy:
Regards
Hi there,
I am running the merge again and it is still creating duplicate records from the LHS table. The LHS table has unique rows of data. So I have 183,000 records merged into 187,000 records post join.
I cannot understand why BI would create an exact duplicate of the LHS row in the merged table? Could it possibly be because the primary key is duplicated? I know that this is not scientifically viable however this is the closest I can get to a primary key.
Hi DataGuy2016,
If you haven't already found a solution...
I had a similar issue where LEFT JOIN was creating duplicates, resulting over 1000 rows of data where I should have just 85.
I solved the issue by expanding the relative columns as an aggregate, instead of just an expansion.
Hope that this makes sense.
Hi,
The LEFT JOIN table Merge is creating duplicate records from the LHS table.
I understand what a LEFT JOIN is.
My LHS table only has unique rows. Why is PowerBI creating duplicate records? These are 100% identical in every respect.
SQL JOINS
The problem lay with the Data.
The LEFT JOIN functionality of PowerBI works perfectly.
I'm having the same issue thanks. What did you do to fix your data set?
Hi, I was having the same issue, and then I noticed that my second file, from where I was getting the data, had also duplicated figures. That was the problem. After I deleted those duplicates, I clicked on the refresh button, and the problem was solved!
- Hi I’m having the exact same issue when merging two tables, I see you posted that the issue lays with the data and not the merge, can I ask how you solved it? I’m pretty sure I have unique values so I believe that is not the issue, actually the merge is working on someone else’s computer but not on mine. Appreciate any hint you may provide.
Had the same issue, where I was merging queries.
The issue in my instance was that the base query was expanded which resulted in duplicates in the base query.
Once the expansion was removed in the base query the merged query was fine.
So if data is okay, the issue may be caused by duplicates in the base query.
he meant the other query that you are merging your data with
so if you are in Table A and trying to merge data with Table B, if Table B has duplicates it will result in a duplicates in your Table A after you expand the results, so it's better to have Table B cleaned from duplicates first.
Hi All,
I having similar issue, Below are the tables and the output after using full outer join
Table1
Table 2
Merging query screenshot
Merged table with duplicate entries- actually it copies table 2 for each entry of table 1
Any solution
I had same problem. I was joining Table A with Table B on 2 columns, that were numbers but marked as Text type. I changed the type of these columns I was joining on in both tables to Whole number type. Then the Left Join worked and the duplicates went away!
