Import from Excel to a Collection - 500 limit confusion

[Anonymous]

Hi Guys,

So after doing loads of research on here i am still completely confused with the whole 500 limit.  Some posts on here say there is no 500 limit when using the filter function if the data source is a Sp list.  Others are saying there is and my test also shows there is a limit with SP unless i am doing something wrong.

The 500 limit just seems completely ridiculous. Of course you are going to want to filter more than 500 records.  My current table is 18000 rows so essentially powerapps is uselss to me.  I appreciate the limit is now 2000 but thats still very low.

My questions are:

1.  if a Collection can hold unlimited rows, then is there a way to import data from an excel spreadsheet into a collection and how is this done?

2.  Can you then use a filter to bring back all entries regardless if they are over 500 rows?

Many thanks

[JRaasumaa]

---

@Anonymous wrote:

Hi Guys,

So after doing loads of research on here i am still completely confused with the whole 500 limit.  Some posts on here say there is no 500 limit when using the filter function if the data source is a Sp list.  Others are saying there is and my test also shows there is a limit with SP unless i am doing something wrong.

The 500 limit just seems completely ridiculous. Of course you are going to want to filter more than 500 records.  My current table is 18000 rows so essentially powerapps is uselss to me.  I appreciate the limit is now 2000 but thats still very low.

My questions are:

1.  if a Collection can hold unlimited rows, then is there a way to import data from an excel spreadsheet into a collection and how is this done?

2.  Can you then use a filter to bring back all entries regardless if they are over 500 rows?

Many thanks

---

I think the answers about what can and can't be used to filter is here: 

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-list

and here:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

But to answer your questions:

1. You should be able to use your excel file as a data source

2. Yes, you can filter that collection down to what you want to see, in fact it's by far the best method I've found when dealing with lots of data, filter/collect what you want and use that instead of the actual source.

Hope that helps a bit!

[Anonymous]

Thanks for coming back to me.  So does this mean i can use something like the below to filter from an excel file over 2000 rows as it does not appear to be the case for me. It will only bring back anything within 2000 records and not above.

SortByColumns(Filter(Static, Email=User().Email), "Ref", Ascending)

Many thanks

[JRaasumaa]

---

@Anonymous wrote:

Thanks for coming back to me.  So does this mean i can use something like the below to filter from an excel file over 2000 rows as it does not appear to be the case for me. It will only bring back anything within 2000 records and not above.

SortByColumns(Filter(Static, Email=User().Email), "Ref", Ascending)

Many thanks

---

If you are importing a static excel sheet using a data connection you can filter the list any way you want, the sheet is loaded into memory in the app and can be used however you want. 

If the data changes and you are connecting to an excel sheet as storage of those changes (in your OneDrive or similar) you will be making a live connection to the excel sheet and stuck with delegation issues meaning filters and returned records will be lower than 2000.

Are you using Excel as a live data source or just a static sheet with information in it?

Is there any opportunity to use another data source for you?

Excel is notoriously horrible at data storage. Sharepoint or even a SQL connection to an on-premise server would be the easiest for entry level storing of info.

[Anonymous]

Thank you for this.  Can i just check if i put the list in Sharepoint, will the following query work even if the limit is more than 5000 items?

SortByColumns(Filter(Static, Email=User().Email), "Ref", Ascending)

Many thanks

[Anonymous]

Lets see if the following link answers all of your question:

https://blog.coeo.com/davidmorrison/2017/06/21/powerapps-500-record-limit-delegation-and-how-to-work...

[Anonymous]

Thanks for coming back to me.  That does make sense from an 500 limit however i thought SP had a limit of 500.  I have a list of 4995 records in SP so i did not think this was effected.  In powerapps i am using the below on a gallary but still gettting the delegation warning:

Filter('5000 Test', Email="Craig.Jenkins@thisisatest.com")

[Anonymous]

Thanks for coming back to me.  This does make sense from an excel point of view.  however, i thought Sharepoint would be ok if the list was below 5000 however i am still receiving a delegation error using the below:

Filter('5000 Test', Email="Craig.Jenkins@thisisatest.com")

[leyburn19]

I know this is an old post but in relation delegation and teh filter on your email I find the best way to do this is to put the email in a variable like Set(varemail,User().Email) then in the filter use startswith

Filter(Source,Startswith(Email,varEmail))

I too have an issue where I need to create  a collection of up to 27000 records that is stored in an SP list.

I export teh list to excel - ensure you have the SP I as well

I import list to App

I build Collections based on that import

When working on each record I have a view with unbound fields that match the collection columns.  When the row is selected it populates each one of the fields.  

When a change is made a save button appears.  When clicked this create a new collection based on the changes and non changes

I then Patch the changes back to the SP list

The above allows for good offline activity as well

My issue is the excel import is not updated so I have to export the SP list and import again on a regular basis

The key reason for creating 27000 Collection is teh data is so bad there are very few ways to drill down and make the list smaller for users at this time.  The intent would be to over time as data integrity improves this will be achieved and teh need to impor will get replaced

[Anonymous]

@leyburn19 

Are you saying that by exporting your SP List to Excel you can then import the 27,000 records from Excel to your app? Is this a relatively simple call/process? 

Id be interested in seeing how this I see done and also how you are working in an offline state. I'm making slow progress through setting up offline functionality in an app I'm building so seeing solutions from others is always welcome.