Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DataGuy2016
Advocate II
Advocate II

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?

1 ACCEPTED SOLUTION
DataGuy2016
Advocate II
Advocate II

The problem lay with the Data.

 

The LEFT JOIN functionality of PowerBI works perfectly.

View solution in original post

46 REPLIES 46

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'.

SGLake
Frequent Visitor

I changed the privacy settings and still have the issue too. To work around I just used the remove duplicates feature, I know this is not ideal but I know my table only has one line for each record.

DataGuy2016
Advocate II
Advocate II

The problem lay with the Data.

 

The LEFT JOIN functionality of PowerBI works perfectly.

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.

Hi, you may try to match more than one field to obtain a unique set of data match.

LiongMei1_0-1689312298247.png

 

Anonymous
Not applicable

Sadly, I still have not been able to fix this problem with the "Merge" feature.  I have resorted to using "Append" instead - and in some cases it works and in others it doesn't.  It is a very frustrating situation.

I got the same problem.

Source table is an daily order table with duplicated rows, i.e. SKU ID and merged with another matching table with unique rows of SKU ID, and created randomly duplicated rows.

 

Whatever I tried to remove duplicates or aggregated by median still not get the correct final data.

 

My order data could get up to 20K rows and it's easily get wrong while I merged the tables.

 

That is so annoying.

 

Hope POWER BI experts can really solve this problem.

 

Thanks.

I know this post is old, but this may help others in the future:

Create an Index Column before the merging.

This will mark each row with a unique sequential number.

When the duplicate happens, it will duplicate the number as well. 

After the merging, select the Index Column and remove duplicates.

Remove Index column.

Thank you, this is the only thing that worked for me!  The index also makes it easy to spot where data has been deleted eronously.  I plan on added one to all of my datasets from now on.

Hi, is there anyone who can help with this? I have the same issue and there seems to be no solution for it apart from Table.First, but this function slows down the query...

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!

Thank you! I was going crazy, not understanding why I have these duplicates after merging. 
I really appreciate your help, you saved me a lot of time.

Thank you so much!  This same method fixed my issue too

Many Many thanks! I had the exact sa;e problem and I indeed found duplicated values in one of my tables!

I was joining on the the text field and it was creating duplicates. Once i unchecked the fuzzy option it worked for me. 

So, I am having the same issue and I just found your post and read it with hope. Although it has been helpful my issue is a little different just a tiny bit.

 

What I am doing is mergint two tables I am creating with M inside the advance query for one of my tables. In this operation I create two tables by doing a left anti join, that works fine.

 

I have table A that are all new leases.

I have table B with all terminated leases.

 

I want a single table C with both, so I need two columns, new lease, terminated lease. All this is fine so far.

I do then a nested join which is a left outer from the new leases to the terminated leases, so im keeping all that is new and just putting a false in those that are not.

 

The moment i merge is ok, but then when i expand the table is when booom, i get duplicates. The problem i just found is that while im using lease_id as my key to join, there are some that do not have that key because they are not leases, they are owned so it is a different number, and coincidentally those are the duplicated ones.

 

Any ideas on how to solve it ???

 

 

I am also facing the same issue.my both tables have duplicate rows. I want first column value to be picked only.
v-ljerr-msft
Employee
Employee

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.Smiley Happy

 

In addition, here is a similar thread for your reference.Smiley Happy

 

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.

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.