Assistance with using Distinct with Choices function on list lookup columns

(1) Share

Report

Posted on by TheWizardHunter

Hello All,

I'm new here and would appreciate assistance with the following Power Apps formula.

I have a SharePoint list with lookup columns from other SharePoint lists. In Power Apps I have a lookup dropdown column and I am trying to get it to display unique values as the column contains many duplicate values.

I see that the District function can be used to resolve this e.g. Distinct('ListName',Column). However, my column is a lookup and this formula does not work.

The default Item formula for the lookup dropdown is Choices([@'James Strategic Plan'].Division). I've tried using Distinct(Choices([@'James Strategic Plan'].Division), Value) but this produces a dropdown with but you cannot see the text. Do I need to replace the word Value with specific text? Below is an image of what happens when I use the formula Distinct(Choices([@'James Strategic Plan'].Division), Value). Many thanks in advance for any help to resolve this.

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

yashag2255 24,769 Super User 2024 Season 1 on at

Like (0)

Report

Hi @TheWizardHunter

The Choices() function displays all the distinct options that are available in the lookup columns. Are you seeing any duplicates while using Choices([@'James Strategic Plan'].Division)? If this is the case, then duplicates are present in the lookup data.

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Was this reply helpful? Yes No
TheWizardHunter 64 on at

Like (0)

Report

Hello @yashag2255 ,

Thank you for your quick reply.

Yes there are duplicates in the lookup column, but I would like a formulae that would onlu display unique values when the dropdwon is selected/used.

This formula does it Distinct('LineName',Column) but I don't know how to apply it the the default Item property of the lookup dropdown default formula Choices([@'James Strategic Plan'].Division).

The formula Distinct('LineName',Column) works on dropdowns with multiple duplicate values that aren't a lookup dropdown columns.

Hope this makes sense.

Thanks.

Was this reply helpful? Yes No
yashag2255 24,769 Super User 2024 Season 1 on at

Like (0)

Report

Hi @TheWizardHunter

Is there any way you can remove the duplicate data from the lookup entity because that would be an ideal solution?

In case, that is not doable, you can add the lookup entity as a datasource in the app and update the expression to:

Distinct(LookUpEntity, ColumnName)

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Was this reply helpful? Yes No
TheWizardHunter 64 on at

Like (0)

Report

hello @yashag2255,

Thank you for the information. Unfortunately, as I was trying to explain before the formula Distinct(ListName, ColumnName) does now work on lookup/choice columns.

I am trying to create two cascading dropdown menus based on a lookup list containing two columns. One column is division and the other department. As such the division column will have duplicates.

The default Item property for both lookup/choice columns is Choices([@'James Strategic Plan'].Division) and Choices([@'James Strategic Plan'].Department). I have tried using Distinct(Choices([@'James Strategic Plan'].Division), Value) but this returns a dropdown list with unique but blank fields to select from.

Hope this makes what I want to achieve clearer.

Thanks.

Was this reply helpful? Yes No
Verified answer

TheWizardHunter 64 on at

Like (1)

Report

Thank you for your assistance. I managed to resolve the issue by using a solution from Mike Dockray, section Part 2 – Cascading drop-downs https://www.breadcrumbdigital.com.au/customising-sharepoint-list-forms-with-powerapps-part-two/

Was this reply helpful? Yes No
ronn_zw 26 on at

Like (0)

Report

For lookUp columns you need to add ["Result"] in the displayFields

Was this reply helpful? Yes No
hamdan_89 16 on at

Like (0)

Report

Can you please share the formula on how it worked for you?

Was this reply helpful? Yes No