You have bug in Excel new dynamic unique function

Question

You have bug in Excel new dynamic unique function

Anonymous

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.

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.

0 comments

6 answers

Answer 1

This really isn't the forum for how to use Power Query, but there is no technical reason not to use Power Query to provide the distinct values, sort them however you like, then continue processing on them. Dynamic Array functions are not good ways to get data into Power Query. In fact, it is impossible. To get data into Power Query, it has to be a table, and DA functions return #SPILL! errors in tables. They are not compatible with them.

I'd need to see your data to understand why you think it (Excel/Power Query) isn't properly elminating or overeliminting entries. Your data might need to be trimmed or cleaned (both can be done in PQ) before you use Table.Distinct on it. You may also have spaces that are really char code 160 and not 32, which needs to be replaced, also done in PQ.

The best place for that type of support if the above still doesn't get you want you need is the Power BI community in the Power Query forum. Excel Power Query is also discussed there.

If you post data there for me to look at, you can tag me @edhans in that forum.

Answer 2

Anonymous

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.

0 comments

Answer 3

Anonymous

Hello.

Windows and 365 2016.

0 comments

Answer 4

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?

Answer 5

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.