This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Just been figuring out why all results did not match when i first short unique and then sample data in power query. I took me hours to find out that short unique does not short all uniques. I had same text at beginning and then end of cell there where values 2,5 bar, 2,5 bar and
2,5 Bar. Unique values shows 2,5 bar and 2,5 Bar not 2,5 bar. When I change 2,5 bar to 2,5 bar. unique shows only 2,5 bar and 2,5 Bar was wanish. But exsample also same beginning text and value 1,2 bar and 1,2 bar the unigue function returned both values correct. So there must be some kind of bug of that function. I have not checked all but those came up from column witch has 60.000 rows.
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Please let us know what platform you are using (Mac or Windows), and what version and build of Office you have installed.
-Jesse
Note that UNIQUE is not case sensitive, but Power Query is. So a table that looks like this:
| Data |
|---|
| 1 bar |
| 2 bar |
| 3 bar |
| 2 BAR |
UNIQUE will return:
1 bar
2 bar
3 bar
but Power Query pulling in that same table, then running a DISTINCT over it will return the following by default:
1 bar
2 bar
3 bar
2 BAR
You can tell PQ to be case insensitive by either adding "Comparer.OrdinalIgnoreCase" to your Table.Distinct function, or using Text.Lower (or Text.Upper) to get everything in the same case before running Table.Distinct.
It isn't a bug, it is just how PQ vs Excel works.
You could probably get UNIQUE() to be case sensitive by doing some dynamic array magic using the EXACT() function, which is case sensitive. Most Excel functions are not though.
Hi thank you for your reply.
I knew that I can solve it in power query, but the problem is that I have hundreds of unique values that I it not possible to short easily in different categories in PQ. I need to short those values before I send them to PQ that is vital. Your great advise using exact is very interesting. I just don´t know how to use it. I need to google it but if you could advise bit more that I wont have to spend hours try to learn it. My main desire is that I could use it with new array unique function or do I need to study and start write long and complicated function for my purpose using that exact?
Hello.
Windows and 365 2016.
I forgot the reason why I tough it was bug because if I had values 1,2 before that bar and exact same end and then 2,5 at beginning those 1,2 values return with bar correct but those 2,5 did not.