Showing results for 
Search instead for 
Did you mean: 
Super User
Super User

Re: ClearCollect for more than 2000 records





Level: Power Up

Re: ClearCollect for more than 2000 records

Hi @dinusc ,


I'd like to use Option 2 you mention but have no idea how to implement it. I've used the Collect(dsfinal,ds1,ds2) as shown below by Drrickryp, however my collections only retrieve the first 2000 rows for each collection ... my data is over 6000 rows.


How would you collect 1-500; 501-1000; etc. , what would the formula look like, as I'm not sure how that would work and cannot find any examples.


I've also tried to Collect(dsfinal(If(CustomersList.Custgrp = GalleryAgencies.Selected.Custgrp)2000) but without success. I was hoping the Agency would be the factor in collecting <2000 items at a time, but it's just not working.


Thanks in advance.

Level 10

Re: ClearCollect for more than 2000 records

@Bren0074  I built a solution for this last week because I was having the exact same issues. A warning though, it's not elegant - but it does work 🙂


The solution is to use SharePoint ID numbers.

1. First, sort by ID Descending, collect that into collection1 (col1)

2. Then, sort by ID Ascending, collect that into collection2 (col2)

3. Build an array of 'Missing ID numbers' as a collection - call it colTarget. Calculated by (MinID col1) - (MaxID col2)

4. Use ForAll() with Collect() to get the missing ID rows one by one into collection3 (col3)

5. [optional] Collect col1, col2 & col3 into finalCol


Step 4 can take awhile if 'the gap' between Min/Max is large so just be mindful of that and maybe think about if you need all the data in your App? I put a label in my Dev App and set it to CountRows(col3) and you can see it tallying up as data gets collected - gives you an idea of data collection speed.


You could always build a smaller array (Step 3) of missing IDs that gets tacked onto col1 if you don't need all of the data Eg. (MinID col1) - 500 instead of (MinID col1) - (MaxID col2)?


Apologies for all the blah above, here's my code solution:

Note: my Step 3 is adjusted from @Prem_ddsl's cool code here Dynamic Loops


Step 1:



Step 2:



Step 3:

//specify target rowcount



//define a base collection from which you will generate your target collection. A colBase of 60 will be able to collect up to 60x60 = 3600 items. Set colBase's size to meet your needs.

//setup collections to be used as inner and outer counters

//generate array of missing ID numbers 


RemoveIf(colTarget,RowId>=Min(col1,ID));  // remove unwanted ID numbers


Step 4:




Step 5:

ClearCollect(finalCol, col1, col2, col3)


As  I say, not elegant but effective. Hope this helps 🙂




Level 10

Re: ClearCollect for more than 2000 records

Ooops, the 'sad face' emoji should be:

: ( 



ForAll(colOuter,ForAll(colInner,Collect(colTarget,{RowId : ( Max(colSiteInduct2,ID)+colInner[@Value])+(vOuterMax*(colOuter[@Value]-1))})))

Level: Powered On

Re: ClearCollect for more than 2000 records

@Eelman   Thanks for this - really excellent work. One thing I wondered about is whether it's possible to modify this to include a filter on the data source to remove unecessary items? e.g. I have a list with 40,000 items, but I only need to pull in ~7000 items, based on a status text field. Therefore I'd like to filter out the 33,000 items which are in a "Completed" status. Is this achievable using this same method?

Helpful resources


Power Apps Super User Class of 2020

Check it out!


Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!


Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020


Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (5,031)