cancel
Showing results for 
Search instead for 
Did you mean: 
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

32 REPLIES 32

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!

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. 

Betho24
Frequent Visitor

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

 

 

BIWORKS
Frequent Visitor

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

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.

 

 

 

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.

 

 

 

 

 

I am having the same problem with my data.  And no, I do not understand your answer to "expand as an aggregate," will you please clarify?

 

I know this is an old thread, but if someone else is still reading this thread and would like to offer help, I appreciate it!

 

 

HI JR,

 

Thank you for the response.  Could you send me an email at trossgreene@stormsmart.com.  I want to talk with you more and share a sample file, but I can't figure out how to upload an excel file here.

 

Thanks,

 

Tera

Hi @tera12

 

I have created a quick sample that I hope helps explain my method. It may not work with your data, but it did fix the issue with mine!

 

I have two tables, I would like to merge 'Customer Type' with 'Customer Name'.

 

Customer Name TableCustomer Name Table

 

Customer Type TableCustomer Type Table

 

I would like to merge using the 'ID' column in both, to bring in the 'Cust Type' field from table 'Customer Type' into table 'Customer Name'.

 

 

 Merge OptionsMerge Options

 

 

Once successfully merged, I see the below:

6.JPG

 I now have the option to expand that column of the merged table to bring in data from the 'Customer Type' table. But this is where I choose to Aggregate a field rather than expand. You may require a different aggregate measure other than median, dependant on data type:

 

Expand as AggregateExpand as Aggregate

 

The result from my data is below:

 

ResultResult

 

Please also see sample.pbix file which I hope can help you with your issue.

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 JOINSSQL JOINS

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors