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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.