cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
RS
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.

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

Thanks, I'm looking into this now.

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


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

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

 

 

 

 

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (92,051)