You have bug in Excel new dynamic unique function

Anonymous
2019-11-04T02:09:18+00:00

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.

Microsoft 365 Insider | Excel | Other

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

Sort by: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2019-11-05T01:14:25+00:00

    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.

    Was this answer helpful?

    0 comments