Showing results for 
Search instead for 
Did you mean: 
Helper I
Helper I

Importing From Large Datasource Into Collection For Offline Use

I am trying to import a large datasource (over 75k records) from a SharePoint List into a Collection. We need to do this for offline capabilities.  While that sounds like alot of data - it really isn't - it's equivalent to a small audio book.


  1. I know PowerApps only allows the import of a maximum of 2000 records through various delegation methods.]
  2. I do not have access to a cloud SQL databse.
  3. I cannot install the PowerApps gateway in our environemnt (government) to use with an on-prem SQL database.
  4. I found a great article on how to bring in chunks of records from a SQL databse here:

Everything works fine with the 'code' detailed in the link from bullet 4 until I get to the point of actually bringing in the records from my SharePoint list.  The code I am about to post esentailly takes the records in 500 (can be eaily modified for the new 2000 record limit) record chucnks and adds these records to a collection. This will pass through the 500 record chunking a predetermined number of times based on the size of the source data.  The variables established in this code have been setup previously (see link):


ForAll(colNumbersTable, Collect(colMyBigTable1DataRaw, Filter('[datasource]', (datasourceID - 1) > ctxMinRecID + ((Number - 1) * 500) && datasourceID <= ctxMinRecID + (Number * 500)));


colNumbersTable: a collection with a sinlge column (named Number) containing numbers from 1 to x number of iterations (deteremined by the number of records in datasource / 500)

ctxMinRecID: the lowest record ID from the datasource



  1. Sharepoint/Powerapps integration does not allow for mathematical functions to be performed on the ID column, in this caase: (datasourceID - 1)
  2. Performing the ID >= value && ID <  value with OnSelect and a ForAll function produces the littel blue dot with the message: Part of this formula cannot be evlauted remotely due to service limiations. Highlighted operation is not supporeted by column 'ID'.
    1. I have gotten the Filter(datasource, ID >= variablemin && ID < variablemax) to work on a table. The blue dot still shows up but data is pulled into my table.
    2. I was able to have the same ForAll loop that builds the number of iterations table create a variablemin and varaiblemax column
  3. Probem Code: ForAll(colNumbersTable, Collect(colAssetDataRaw, Filter(Base_List, ID >= ImpRecMin && ID < ImpRecMax))


  1. To have the app pull in multipe chunks of records from the SharePoint datasource to end up with a collection that mirrors the dataspource.
Community Support
Community Support

Hi @mikearmour,


Could you please share a bit more about the ImpRecMin and the ImpRecMax within your Problem code?

Why do you perform mathematical function (datasourceID - 1) within the ID column of SharePoint list?


For your first question, if the mathematical functions are not suppoered to be performed on the ID column of your SharePoint list, please take a try with the following formula:


datasourceID> ctxMinRecID + ((Number - 1) * 500) + 1

In addition, I think it is not necessary to perform mathematical function (datasourceID - 1) within the ID column of SharePoint list, please modify your formula as below:

ForAll(colNumbersTable, Collect(colMyBigTable1DataRaw, Filter('[datasource]', datasourceID >= ctxMinRecID + ((Number - 1) * 500) && datasourceID < ctxMinRecID + (Number * 500)));

For your second question, I think this issue is related to the delegable operators the SharePoint data source supports. The ">=" delegabe operator and the "<" delegable operator are not supported in SharePoint data source currently, the SharePoint data source only supports "=", "And (&&)", "Or (||)" and "StartWith" delegable predicates.


More details about the delegable predicates the SharePoint data source supports, please check the following article:



For your third question, please take a reference to the formula that I provided above for your first question.


Best regards,



Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Kris - 


Please take a look at the link I put in my original post as it explains alot of this code.  I am not the person who origianlly wrote the code, but borrowed what I could.


To that end - the answer to your second wustion is - this code was originall written for a SQL database as the datasource - not SharePoint.  I have since tested the original code against CDS as a satasource and could not perfomr the mathematical functions on the PrimaryID column either.  It may be something that can only be done when useing a SQL database as the datasource.


To answer your first wuestion - in a nutshell - what the code does is:

  1. Creates a numbers table that dicteates the number of data passes the code should do to pull in records in 2000 record chuncks.  For example - if I wanted to bring in 10,000 records - then I want to do 5 passes of 2000 records.  The numbers table will then create a 5 row table with:
    1. the first column being the pass number (1, 2, 3, 4, 5)
    2. the second column is ImpRecMin - which is the low end record for the chunk of records to be imported.
    3. the thrid column is ImpRecMax - which is the upper record limiit for the records to be imported.
    4. For Example for pass 1, ImpRecMin is 1, ImpRecMax is 2000; for pass 2, impRecMin is 2001, ImpRecMax is 4000, etc. etc
  2. The code then uses a ForAll to read the data source and add the chunk of records to the collection.

I have given up on using SharePoint as the datasource for this app becuase of all the service limitations.  I personally find it silly that you cannot specify a chunk of records to be pulled in bu using a ID >= min && ID < max.


I have been able to get the code to work using the CDS as my data source.  I sucessfully imported over 75K records into my app on my mobile device in around 1 min and 43 sec.


If anyone is interested in seeing the final code - please let me know and I will post.

I know this is an old thread, but I would very much like to see the code, that worked on CDS.

I'm trying to pull in 50.000 records, and I get an error on the last step, where the records are actually supposed to be loaded from CDS to my collection.

Here's my code:

  1. Create collection "colNumbers", with one column, with numbers 1-200
  2. UpdateContext({varMin: First(Sort('Test Entities','Whole Number',Ascending))});
    UpdateContext({varMax: First(Sort('Test Entities', 'Whole Number', Descending))});
    UpdateContext({varIter: RoundUp((varMax.'Whole Number' - varMin.'Whole Number')/500,0)});
  3. ClearCollect(colIter, AddColumns(AddColumns(Filter(colNumbers, Value <= varIter), "minIter", (Value-1)*500), "maxIter", Value*500));
  4. Clear(colTemp);
    ForAll(colIter, Collect(colTemp, Filter('Test Entities', 'Whole Number' >= varMin.'Whole Number'+minIter && 'Whole Number' < varMin.'Whole Number'+maxIter)));

It's the ForAll() in step 4. that fails (see attached image for error message)

Helpful resources

PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,229)