populate gallery from Excel table with over 10000 registers

[dvalenzuela]

Hi all,

I hope you can help me. I'm trying to populate a gallery from a Excel table with over 10,000 registers.

With 2,000 registers I don´t have problem.

I trying to use a collections with column called "PrimaryKey", but I have a problem 

In OnSelect property Button, I have this:

Concurrent(
Collect(CollectionA;Filter(ExcelTable;PrimaryKey<2000));
ClearCollect(CollectionB;Filter(ExcelTable;PrimaryKey>=2000 && PrimaryKey<4000));
ClearCollect(CollectionC;Filter(ExcelTable;PrimaryKey>=4000 && PrimaryKey<6000));
ClearCollect(CollectionD;Filter(ExcelTable;PrimaryKey>=6000 && PrimaryKey<8000))
)

When run the app, and check in File -> Collections, I have:

CollectionA with 1999 registers

CollectionB with 1 registers

CollectionC with 0 registers

CollectionD with 0 registers

I dont know why starting second collection I dont have any data or what can I do.

I change File --> Advanced configuration (500 --> 2000). Save the app and restart the app

Thanks in advanced

[WarrenBelz]

@dvalenzuela ,

A bit too quick for me - went out for a little while and did not see your earlier post. The SharePoint reference was for a different reason in that a heap of Excel issues simply go away, however the ID field is not Delegable as a query source. There is an easy work-around - two parts to it in your case.

This one is is a once-only job.

1. Create a numeric field in SharePoint
2. Create a DataSheet view with a filter (this is OK in SharePoint) ID <5000 is the maximum you can have in a view with two fields - the ID and the new field - make the number of items no more than 200. 
3. Copy and paste the ID (you can select the whole column) to the new number.
4. When you have finished that lot, make the filter >4999 and repeat.

Going forward, on the OnSuccess of your new item screen, you can set a Variable

Set(
  vID,
  YourFormName.LastSubmit.ID
)

and make the Default of the item vID and ensure it is on the next screen.
However this raises another question - why do you want to Collect it all rather than simply working off the list?

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[v-xida-msft]

Hi @dvalenzuela ,

Based on the issue that you mentioned, I think you have some misunderstanding with Delegation issue in PowerApps.

Currently, within PowerApps, the Excel table is not a Delegable data source, so you could only process data locally in your app. When you add Excel table data source into your app, it could only load first 2000 records (at most) from your Excel table into your app for processing.

Note: When you add Non-delegable data source in your app, it would only load first 2000 records (at most) into your app memory. The Filter function is a Delegable function, which could not be delegated for processing data from Non-delegable data source.

More details about the Delegation in PowerApps, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview#non-delegable-limit...

In other words, the records from your Excel table has already been stored into your app's memory before executing the Filter function. So the Filter function is actually applied to these records data (2000 records) stored in your app's memory rather than in your Excel table itself.

So the Collection B could only contain one record, the Collection C, Collection D both contain 0 record.

As an alternative solution, please consider use a Delegable data source (e.g. CDS Entity, SP List, ...etc) to store your data instead of Excel table. You could consider import your Excel table records into a New CDS Entity in your PowerApps Environment, then you could use the CDS Entity as data source in your app.

More details about importing data from Excel to CDS Entity, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/data-platform-import-export

https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/data-platform-cds-newentity-pq

Note: When you loading data into CDS Entity, please specify Whole Number type column to store the PrimaryKey column value in your Excel Table.

After that, you could achieve your needs based on the formula you provided above.

Best regards,

[WarrenBelz]

Thanks @v-xida-msft for the insight on Excel - that is useful.

One thing that has to be mentioned is that CDS and SQL are premium data sources requiring a per-user licence whereas SharePoint does not.

[dvalenzuela]

Hello
I have updated to list in sharepoint and I present the same problem.
In the sharepoint list I have a column called ID that has the incremental value with each record.
What should I do to view more than 2000 items?
In the same way, if I want to work with collections, only 1999 items are saved in the first collection, and in the second collection I only have 1 record.

What else can I do?

Thanks

[Pstork1]

The list already has an ID column.  You need to make sure you are using the column you added to the list, so call that column something other than 'ID'.  AS was mentioned, the regular ID column can only be delegated using '=' not '<', or '>'. That's why you need to create a second column.  

[dvalenzuela · Accepted solution]

Hello everyone, if any of you had the same problem, I will tell you the solution:

First I was playing with non-delegable functions under a list in sharepoint, what I did was to debug the table in excel before loading to the list including a PrimaryKey autonumber with number form in excel. this has worked for me. then increase the range to 2000 data limit. now even though my list has 7000 if I search or order I can get to all the records.

It´s working fine when I create a new register too.

Thanks all for your support