cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rynoh17 Member
Member

Remove duplicates not working.. Can't create relationship

Hello,

 

I have a dataset with customer and a discount structure for that customer per part. I created a concatenate in the edit query screen of the customer, and there are a couple duplicates. Highlighted the Concatenate column, and clicked remove duplicates.

 

Tried creating a relationship and it didn't work. Created a summary table of the concatenate and COUNTROWS, and lo and behold there are a handful that have a count of two. Why is this happening?

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
rynoh17 Member
Member

Re: Remove duplicates not working.. Can't create relationship

@v-sihou-msft

 

Yes, I double and triple checked. Everything was done the same way as in my example. Must be an error due to such a large data set.

View solution in original post

21 REPLIES 21
Super User IV
Super User IV

Re: Remove duplicates not working.. Can't create relationship

Would have to see some sample data where the problem could be reproduced.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

rynoh17 Member
Member

Re: Remove duplicates not working.. Can't create relationship

Sample

 

The same exact steps in the edit query in this situation worked. They do not with my large data set (10 Million lines & about 20 duplicates).

Super User IV
Super User IV

Re: Remove duplicates not working.. Can't create relationship

I would report this as an Issue: https://ideas.powerbi.com/forums/360879-issues

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

rynoh17 Member
Member

Re: Remove duplicates not working.. Can't create relationship

Bummer.. Well I just ended up making another table to mirror the first, linked the summary table, and filtered all the ones. it worked, but with this many lines I'd rather have it be more efficient. Thanks for your help @Greg_Deckler

Moderator v-sihou-msft
Moderator

Re: Remove duplicates not working.. Can't create relationship

@rynoh17

 

As I tested, the relationship can be created properly between two tables in your sample.

 

66.PNG

Have you get any error when creating relationships? Did you select correct columns?

 

Regards,

rynoh17 Member
Member

Re: Remove duplicates not working.. Can't create relationship

@v-sihou-msft

 

Yes, I double and triple checked. Everything was done the same way as in my example. Must be an error due to such a large data set.

View solution in original post

yaelwass Visitor
Visitor

Re: Remove duplicates not working.. Can't create relationship

I'm having the same issue but the data set is only around 2000 rows.

 

Note that there seems to be an inconsisencty in how case sensetivity is handled: the Remove Duplicates feature is case sensetive but the uniquemenss check done when created creating realationshiops between queries is not. To work around this I just format the key column in the query to UPPERCASE. But this is an aside, I'm getting the issue even though cases are the same

 

 

Re: Remove duplicates not working.. Can't create relationship

Thank you so much for mentioning this issue with cases in remove duplicates vs joins! I had been struggling with this all day!

SteveDorr Frequent Visitor
Frequent Visitor

Re: Remove duplicates not working.. Can't create relationship

I just had the same problem with joining two tables after I made sure to remove duplicates on the key column in one of the tables and getting the error message about duplicate values. 

 

The issue actually was a NULL value in a single row in the table in my key column.  Power BI was complaining about duplicates but in reality it was the null value that was preventing it from joining the two tables.  I did a row filter and removed the NULL value and the two tables connected together just fine.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors