500 records limitation Excel

[Anonymous]

I have an excel sheet with 2000+ rows and approximately 10 columns. I can only access information in the first 500 rows? 

I can read that there is a 500 row limit, when will this be solved, and is there a work around until then? it is impossible to work with in excel given this limit.. 

Best regards 

Mikkel 

[RS]

Me too. I get that you want to push people into passing the CDS instead, but that isn't suitable for ever use case, but to mention it jacks the cost up considerably.

[Nagaviswanath]

Hello @RS

You can opt for Azure database to solve the limit issue. It is really lifesaver for this kinds of limitation to delegables like Filter, sort. 

Kind regards,

Naga

[CurranD]

I've had to workaround this issue and use multiple Collect() functions.

We have like 5k+ records, so I've done:

• ClearCollect(Collection, Filter(Data, StartsWith(Name --- then done a, b, c, d;
• Collect(Collection, Filter(Data, StartsWith(Name --- e, f, g, h

Really long winded but only way I could get it to collect all records.

If you use this put the collect on page visible as it does take a good 5-10 seconds to collect everything.

[RS]

Thanks, I'm looking into this now.

[RS]

Thanks for th reply - That's a good idea and I'll have a play with it, but I've got users with a very low tolerance for loading times and filtering alphebetically might not suit our purposes.

Cheers

[CurranD]

Know exactly what you mean.

I've currently put it on HomeScreen OnVisible, that way by the time a user gets to where they need to be most of the records have already loaded in.

Just a matter of adapting it & trying to get each load close(ish) to say 400 record mark, that way it doesn't take too long to load (as you have less Collect()'s running) & you wont have to change your code if you get more records.

I've only put the Name as an example, if you have a standard ID field or something you could load ID 1-400, 401-800 etc... or whatever means you can use to break down your records.

Just a suggestion until they implement delegation for this, or another way to load in more than 500 records.

[RS]

---

@Nagaviswanath wrote:

Hello @RS

You can opt for Azure database to solve the limit issue. It is really lifesaver for this kinds of limitation to delegables like Filter, sort. 

Kind regards,

Naga

---

OK, So I've spent a bit of time learning the basics of SQL for Azure and set up a data table only to discover that it's not a solution at all, since Distinct still doesn't work as a delegable function. I am seriously at the point of giving up. I need to be able to filter a column of 5k+ lines down to unique values only and, as far as I can see, thre is literally no way to do that here?

[Nagaviswanath]

Hello @RS

I would suggest you to use 'Filter' to narrow down to required records and then implement Distinct function on the filtered results.

And someone has already posted an idea to get Distinct function delegable:

https://powerusers.microsoft.com/t5/PowerApps-Ideas/Please-Extend-delegation-to-Distinct/idi-p/23207

Share me your formula/requirement to understand the problem better to find solution.

Kind regards,

Naga

[czajnikowiec]

You can change the 500 limit in app settings.

1. On the File tab, select App settings.
2. Under Experimental features, change the Data row limit for non-delegable queries setting from 1 to 2000.

It's a shame that it can be changed only up to 2000. I think in some cases Delegation is impossible. Here is more info: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview#delegable-data-sour...