cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RS
Level: Powered On

Re: 500 records limitation Excel

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.

Re: 500 records limitation Excel

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
Level: Powered On

Re: 500 records limitation Excel

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
RS
Level: Powered On

Re: 500 records limitation Excel

Thanks, I'm looking into this now.

RS
Level: Powered On

Re: 500 records limitation Excel

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
Level: Powered On

Re: 500 records limitation Excel

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
Level: Powered On

Re: 500 records limitation Excel


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

Re: 500 records limitation Excel

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
Level: Power Up

Re: 500 records limitation Excel

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 178 members 4,694 guests
Please welcome our newest community members: