cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AoJueMing
Helper I
Helper I

Identical values counted as distinct. Why?

Hi guys

 

Here's a list of distinct values in a column I have:

bsrhr.PNG

I can't make head nor tail of it.  I am fascinated to find out why there are two distinct blanks, and two distinct versions of 'Very dissatisfied'.  Is there any way I can find out exactly what is it that makes these values distinct from eachother?  Excel is convinced they are distinct also - and the only thing that's making this very difficult is that I cannot find a way to find out how they are different.

 

Thanks

1 ACCEPTED SOLUTION

Hi Marcel

 

Thanks very much.  I should have stated in the initial post that I had already checked for trailing spaces - though I didn't know about impossible characters.

 

Just to complete the thread, what I have ended up doing is c+p the two distinct versions into Word, and switch on 'Show formatting Marks'.  This came back with:

vcfadvfd.PNG

So at least now I can sleep peacefully 🙂

View solution in original post

6 REPLIES 6
MarcelBeug
Community Champion
Community Champion

Typically this is due to trailing spaces and/or unprintable characters.

You can try and use trim and clean in the query editor, transform tab, format.

Specializing in Power Query Formula Language (M)

Hi Marcel,

 

I am having the same issue but have already checked for leading and trailing 0s and trimmed and cleaned the field. By looking at the report level filters, I can see that one of my identical values has only 5 items, which I can say are the 5 that I recently manually changed in my database. I don't see any differences in either edit queries or in my excel sheet. Do you know what else could be causing this?

Hello,

 

I have found my error! I created a sort by column that sadly was not connected to the column it was sorting, so it was placing my 5 manually edit entries in a different position. In case someone else runs across this issue again...

Hi Marcel

 

Thanks very much.  I should have stated in the initial post that I had already checked for trailing spaces - though I didn't know about impossible characters.

 

Just to complete the thread, what I have ended up doing is c+p the two distinct versions into Word, and switch on 'Show formatting Marks'.  This came back with:

vcfadvfd.PNG

So at least now I can sleep peacefully 🙂

View solution in original post

Maybe you can mark your previous post as answer first.

Specializing in Power Query Formula Language (M)

Pardon?

 

edit: Thanks for Kudo - feels like a bit of a cheat though (:

Will close thread.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!