Assistance with using Distinct with Choices function on list lookup columns

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

[yashag2255]

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!

[TheWizardHunter]

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.

[yashag2255]

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!

[TheWizardHunter]

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.

[TheWizardHunter · Accepted solution]

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/

[ronn_zw]

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