cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

500 records limitation Excel

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 

18 REPLIES 18
Highlighted
Advocate II
Advocate II

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.
Highlighted

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

Highlighted
Advocate I
Advocate I

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.

Highlighted

Thanks, I'm looking into this now.

Highlighted

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

Highlighted
Advocate I
Advocate I

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.

Highlighted


@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?

Highlighted

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

 

 

 

 

Highlighted

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...

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (2,854)