Answered

Distinct working on one Table|column but not on another one

(1) Share

Report

Posted on by CU08081433-3

Hi,

I'm currently building my first Power app which connects to SharePoint lists. I'm trying to use the Distinct function to get only the distinct values for a drowdown in the app from a specific column in the "Project risks" table.

I can perform a Distinct on column for example from the "Projects" table but cannot perform a Distinct on columns from the "Project risks" table. Both tables are SharePoint lists connected to the app.

The "Project risks" table is shown within single quotes ( 'Project risks') while the "Project" table isn't.

The category column in the Project risks SharePoint list is a choice column.

Thanks in advance

Best regards

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Suggested answer

Michael E. Gernaey 53,969 Moderator on at

Like (2)

Report
Copy link

Link copied!

Hi

This is specifically because its a Choice Column.

For the Items property put

Choices([@'Project risks'].Category)

and it will load the Choices options, not the Choice selected by peoples records. SO its unique

however, if you are trying to load the Unique List of CHoices, that have been selected by people in their rows, thats a whole other issue

Was this reply helpful? Yes No
EddieE 4,641 Moderator on at

Like (1)

Report
Copy link

Link copied!

Also, be mindful that the Distinct function only works on the first 2,000 records. You won't get a Delegation warning to notify you of this though so you end up thinking it is working correctly - when it's not.

Was this reply helpful? Yes No
Verified answer

WarrenBelz 154,797 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
You should just need
Distinct( 'Project risks', Category.Value )

Was this reply helpful? Yes No
CU08081433-3 8 on at

Like (1)

Report
Copy link

Link copied!

Thank you very much, it was a syntax error. :-)

Was this reply helpful? Yes No
WarrenBelz 154,797 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

What type of field is 'Project RIsks' ? I was assuming it was a Choice field.

Was this reply helpful? Yes No
Suggested answer

Kellboy2243 51 on at

Like (1)

Report
Copy link

Link copied!
Hi

The issue you're facing is related to the use of the Distinct function on a choice column from a SharePoint list. When you try to apply Distinct to the "Category" column in the "Project risks" list, it results in an error. This often happens because choice columns in SharePoint are more complex than regular text or number columns; they are stored as records with multiple properties, not just simple values.

Steps to Resolve the Issue:

Accessing the Correct Field from the Choice Column:

The Category column in the "Project risks" table is a choice column, which means it contains records with properties such as Value. When working with such columns, you need to specify the property you want to work with.

Modify your formula to explicitly reference the Value property of the choice column:

Distinct('Project risks', Category.Value)

This tells PowerApps that you're interested in the actual text value of the choice, not the entire record.

Ensure the List Name is Correct:

Ensure that the list name 'Project risks' is correctly referenced. Since you already enclosed the list name in single quotes (due to the space in the name), this part seems fine.

Check for Data Types:

Sometimes, PowerApps might have issues with data type recognition. Make sure that the Category field is indeed a choice column and that it's correctly recognized in PowerApps.

Example Formula:

Distinct('Project risks', Category.Value)

This formula should now work without errors, displaying only the distinct values from the Category column in the "Project risks" SharePoint list.

Additional Considerations:

If you're still encountering issues, try resetting the data source connection or reloading the app.

Ensure that the "Project risks" list is correctly connected to your PowerApp, and that the column names and data types are properly recognized.

This should help you successfully get distinct values from the choice column in your SharePoint list for use in a dropdown or other control.

Was this reply helpful? Yes No