cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sharuk
Helper III
Helper III

Distinct() is case sensitive ?

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 

3 ACCEPTED SOLUTIONS

Accepted Solutions
rubin_boer
Super User
Super User

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

 

hope it helps

View solution in original post

Pstork1
Dual Super User III
Dual Super User III

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

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

 

 



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

RandyHayes
Super User III
Super User III

@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. 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

View solution in original post

7 REPLIES 7
rubin_boer
Super User
Super User

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

 

hope it helps

View solution in original post

Pstork1
Dual Super User III
Dual Super User III

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

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

 

 



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

RandyHayes
Super User III
Super User III

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes
Sharuk
Helper III
Helper III

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
Dual Super User III
Dual Super User III

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
RandyHayes
Super User III
Super User III

@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?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes
RandyHayes
Super User III
Super User III

@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. 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (68,801)