Distinct() is case sensitive ?

(0) Share

Report

Posted on by Sharuk

202

I'm having a dropdown, where i'm passing the distinct values of list as shown below

Sort(Distinct(Cricket.TeamName,TeamName)),Result)

where i've two records with same name i.e. England and england, where both are same and when i try to pass it into distinct, it is returning both the records into the dropdown!

so does distinct do care about case sensitive ? if yes, how to deal with it and remove duplicate irrespective to case sensitive

Categories:

Building Power Apps

All responses (7)

Answers (3)

Sort by

Verified answer

rubin_boer 4,843 Super User 2024 Season 1 on at

Like
a
(2)

Report
Copy link

Link copied!

hi @Sharuk yes the value is case sensitive when distinct is applied. try Distinct(yourData,Proper(yourColumn))

hope it helps
Verified answer

Pstork1 69,665 Most Valuable Professional on at

Like
a
(2)

Report
Copy link

Link copied!
YOu can avoid the case sensitivity by processing the column in the Distinct().

Sort(Distinct(Cricket.TeamName,Upper(TeamName))),Result)
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(2)

Report
Copy link

Link copied!

@Sharuk

Also to add to @Pstork1 's suggestion...you can also change Upper to Proper. This might be more appropriate in the case of proper names like England.
Sharuk 202 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @rubin_boer , @Pstork1 , @RandyHayes, Thank you for the suggestions

if we do proper()/upper() it is giving the exact result but final result is also getting converted to proper / upper
something like --> SouthAmerica to SOUTHAMERICA / Southamerica

is there a way where we can have the exact name into the dropdown (i.e. SouthAmerica even after implementing upper()/proper())
Pstork1 69,665 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Proper() is the closest you will get to that result. The problem is you are combining alternate cases. One entry for both SouthAmerica and Southamerica. So how would you designate which one should show in the dropdown? Choosing Upper/lower/proper gives you a consistent response, but will combine some responses into one.
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@Sharuk

Well, there is a key statement made " where we can have the exact name into the dropdown"

If you want the exact name, then you are back at the beginning. From England and england, which is the exact name? I know you know which one as a person, but as a computer, how would it know which is which?

Why that is relevant to SouthAmerica is that, although you could "re-lookup" the correct value, how would you know what the correct value is?

So, if there was only one SouthAmerica in the list, you could re-look back at the list and see that the real value you want is not Southamerica but instead SouthAmerica, because there is only one letter case of that in the list.

BUT, now back to England...in that case there are two letter cases in the list - England and england. If you were to re-look back at the list to get the correct one, how would you know which one?
Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
@Sharuk

Actually, in thinking it through a little more, you can get kind of close...

With({_items: ["England","england","England","SouthAmerica","Southamerica","England","England"]}, With({_res:Distinct(_items, Value), _res2: Distinct(_items, Proper(Value)) }, ForAll(_res2 As _a, {Result: LookUp(_res, _a.Result in Result, Result)} ) ) )

Will return "England" and "SouthAmerica" from the list that is in that formula.

However, I say "close" because, if the first item in that list of _items is "england", then you will get "england" and "SouthAmerica" as results.

This is that re-looking up concept that I mentioned in last reply.

The reality is...whatever is storing those values in your datasource should be making and accounting for the case when it is stored.