Working with large SharePoint list data set in PowerApps

PowerApps has a limitation of 2000 items in a data set. A PowerApps collection variable can contain more items than 2000.

Using the iteration function ‘forall’ it’s possible to collect a collection with more than 2000 items.

First initialize the variables:
UpdateContext({varFirstRecord: {}});
UpdateContext({varLastRecord: {}});
UpdateContext({varMaxIterations: 0});`

Populate the variables with the ID/Qualifier of the first and last item. Calculate the number of iterations.
UpdateContext({varFirstRecord: First(Sort('Counterparties Portfolio Roles',ID,Ascending))});
UpdateContext({varLastRecord: First(Sort('Counterparties Portfolio Roles',ID,Descending))});
UpdateContext({varMaxIterations: RoundUp((varLastRecord.ID-varFirstRecord.ID)/500,0)});

Create a collection with iterations and the start (minItt) and end (maxItt) number of that iteration.
ClearCollect(collIterations,AddColumns(AddColumns(Filter([1,2,3,4,5,6,7,8],Value<=varMaxIterations),"minItt",((Value-1)*500)+varFirstRecord.ID),"maxItt",(Value*500)+varFirstRecord.ID));` `Clear(colTarget);` Based on the iteration collection, query your original data source. `ForAll(collIterations, Collect(colTarget, Filter('Orignal DataSource', Qualifier >= minItt && Qualifier < maxItt)));

  • Ensure that the calculation of of minItt and maxItt is part of the iteration collection. SharePoint is not able to execute this calculation in the query.
  • Ensure that you have created a field on you SharePoint list with a copy of the ID field. The ID field not able to use in the collect query, due to the delegation limitation.
  • Be aware of longer load times!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s