Answered

Distinct values on SharePoint list

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi,

I am using the Distinct function to make a Dropdown menu of all the values used in a column in SharePoint list. The size of the list is over 1000 rows, but as I just figured out the Distinct method only reads the first 500 rows (?). So I am missing some values from the last 500 rows I have..

Is there a way to fix this problem so that PowerApp imports all the 1000 rows from SharePoint?

Categories:

Building Power Apps

All responses (3)

Answers (1)

Sort by

Verified answer

piyush 32 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Anonymous

PowerApps have a default limitation of fetching 500 items only. We can increase this value upto 2000 max. Here's a link that talks about the steps, https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview#changing-the-limit

However, fetching so many items can have performance impact on mobiles.
v-monli-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Anonymous,

This shouldn't be a delegation issue, as I do not get any warning messages while using Distinct function for SharePoint fields. So changing the Data row limit won't make any difference. This seems to be the items limitation of the dropdown control. The drop down control won't show more than 500 items.

The workaround so far is that using a ComboBox control instead of a DropDown. There is no limit for a ComboBox. You need to first create a collection for the value using a formula like below:

ClearCollect(C,Distinct('List A',Title))

The collection would be like:

Then set the Items property of the Combo box to:

C.Result

An alternative would be to add a TextInputBox control and set the Items of your DropDown property to

Search(C, TexIinputBox.Text, "Result")

Regards,

Mona
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

The datarow limit actually was the problem. When I changed it to 2000 the distinct function read the whole SharePoint list and I got all my distinct values. I only had like 6 different values in that column, so it was defiently not the Dropdown that reaced its limit.