cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TeeTreeThree Regular Visitor
Regular Visitor

"Remove Duplicate" doesn't remove all duplicate

Dear all,

 

I have a table with just one column, I tried to remove the duplicate in the column via power query. However, once I loaded to the dashboard I use count and count(distinct) both give me different number as the same number is expected.

 

 

Best regards,

Eric

15 REPLIES 15
Super User
Super User

Re: "Remove Duplicate" doesn't remove all duplicate

How many rows do you have? I have seen one other user reporting this and that user had millions of rows. I would report this as an Issue. https://ideas.powerbi.com/forums/360879-issues

 

Any chance you can post a link to the data so that this issue can be recreated?


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Super User
Super User

Re: "Remove Duplicate" doesn't remove all duplicate

This will happen when the terms have different case profiles. Pls check out if this article helps: http://www.thebiccountant.com/2015/08/17/create-a-dimension-table-with-power-query-avoid-the-bug/

 

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User
Super User

Re: "Remove Duplicate" doesn't remove all duplicate

Well, if your table just consists of one column, you can actually use this formula:

 

Table.ExpandListColumn(#table({"ColumnName"}, {{List.Distinct(Source[ColumnName], Comparer.OrdinalIgnoreCase)}}), "ColumnName")

 

It's a bit of a bugger, because the only way I found to use Comparer.OrdinalIgnoreCase (which will ignore case sensitivity) was to use it in list. So if anyone has an idea how to make this a bit smarter, you're more than welcome 🙂

 

http://www.thebiccountant.com/2016/10/27/tame-case-sensitivity-power-query-powerbi/

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User
Super User

Re: "Remove Duplicate" doesn't remove all duplicate

So if you want a distinct of all columns in the table, it's pretty easy:

 

Table.Distinct(Table, Comparer.OrdinalIgnoreCase)

 

 

Still need to figure out how to handle column-selection in it.

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




KHorseman Super Contributor
Super Contributor

Re: "Remove Duplicate" doesn't remove all duplicate

@ImkeF try:

 

Table.Distinct(Table, {"ColumnName", Comparer.OrdinalIgnoreCase})

Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!
Super User
Super User

Re: "Remove Duplicate" doesn't remove all duplicate

@KHorseman

Totally awe!! Thank you so much!

 

& so it looks with multiple columns:

 

= Table.Distinct(Table.FromRecords({[A="one", B=1, C=2], [A="ONe", B=1, C=3]}), {{"A", Comparer.OrdinalIgnoreCase}, {"B", Comparer.OrdinalIgnoreCase}} )

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




KHorseman Super Contributor
Super Contributor

Re: "Remove Duplicate" doesn't remove all duplicate

@ImkeF that's cool, so you could potentially mix-and-match case sensitivity? Like Column1 ignores case, Column2 doesn't? I didn't test far enough to try anything like that. I just noticed that the second argument in Table.Distinct is a list by default if you let the query editor generate the code for you, so I tried adding Comparer.OrdinalIgnoreCase to the list.


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!
Highlighted
Super User
Super User

Re: "Remove Duplicate" doesn't remove all duplicate

@KHorseman Haven't even thought of that, but computer says "yes"  🙂

 

Table.Distinct(Table.FromRecords({[A="one", B=1, C=2], [A="ONe", B=1, C=3]}), {{"A", Comparer.Ordinal}, {"B", Comparer.OrdinalIgnoreCase}} )

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




KHorseman Super Contributor
Super Contributor

Re: "Remove Duplicate" doesn't remove all duplicate

@ImkeF nice. Thanks for sharing this. I never would have even noticed this comparer function otherwise.


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,695)