How to overcome 500 items limit in PowerApps - CEO-Refleksje (2024)

(ESTIMATION TIME: 8 MINUTES)

I planned to publish another topic for today but I decided that this one will fits better to the post I’ve published a week ago.

So if you work with PowerApps connected to ANY data source you had to experienced or at least read about 500 items limitation. In this post I will show you 6 ways how you can exceed that limitation.

UPDATE (03-Feb-2023): Psst, please have in mind this article is from…2019? 2020? Something around that…Anyway – A LOT has changed over the past years. So please take a minute and look at what’s going on right now here or here, or here. You can also check out my new project: No Code Masters on YouTube 😁

Limit? What limit?

If you’re one of those happy devs that have never heard of such limit I’ll make quick reminder:

In PowerApps every data source (SharePoint, Dataverse, OneDrive) is under limitation of 500 items. It means you cannot get more that 500 items from a data source but even more than that – PowerApps won’t even “be aware” of any rows above 500.

Imagine simple case: On your OneDrive for Business you have an excel file with ~600 rows. Even though you need only last 10 rows you will get rows 490-500 instead of 590-600. As I told you in previous paragraph – it’s not the limit of total fetched items. It’s the limit of items that PowerApps knows about.

But before you think that’s a killer for PowerApps and “oh those architects, they’ve never use their tools so how they can design something that actually works” let’s imagine another example:

You have an excel with 2mln rows (yep, I saw over 2GB excel file in one company from financial sector). One of app users opens your app and…probably in an eyeblink you will get tons of emails that your app is not working (but of course it is working but the loading time is huge). That’s why this limitation make sense.

Anyway there are many occasions where you will need more than 500 items. Maybe not 2mln but a simple task list for a company with ~1000 employees can easily generate around 50000 rows in total. So how you can overcome this 500 items limitation in PowerApps? In following sections I describe 6 options in the order from easiest/fastest to the most powerful (but also time consuming to setup):

1. Increase the total limit items you can fetch
2. Use static data
3. OneDrive for Business connector specifics
4. Use delegation
5. Use delegation + iterative function
6. Combine PowerApps with Flow

HEY, I have launched 👉www.Developico.com👈
We have 20+ Power Platform consultants and architects.
Do you need help or competencies augmentation?
Let us know OR schedule a free consultation!

1. Increase the total limit items you can fetch

Okey so this method is the easiest and really quick to setup. Click file (top left corner) > App settings > Advanced settings > set value for non-delegable queries.

This method has one hard limit (limitation of the limitation) – 2000 is a maximum value you can set which means you can’t get more that 2000 items on the same rules as for 500 items (PowerApps won’t know about 2001 item). So if this method does not work for you let’s move to option 2.

2. Use static data

In some specific cases static data may be the best solution. Such data can be imported to your app and will be kept within assets of your app. You can have 10000 rows and still users will have access to all rows. However remember following notes:

1. Static data are static – you cannot modify it from within PowerApp. But for some scenarios it may still do the job i.e. you build company travel app and you want to have index of all countries in the world (192) along with bigger cities (~30 x 192=5760) so your users can search and select to which city they’re traveling to. Countries and cities typically don’t change dynamically so we can freely import them as static data instead of using SQL database (for which you have to spend extra cash).
2. Static data are attached to your app which means they’re enlarging total size of app which may affect app loading time.

If you still think static data is a best choice for you here is how you can add it:

1. View > Data Sources > Add data source
2. Click Import from Excel
3. Select excel file and a its table that you want to import

Ok, now, what if this option also doesn’t suit to your needs: Excel is good but you need to be able to modify it from within app so all app users have access to same data. So you googled and decided to use OneDrive for Business data source connector. It uses excel file as a container which data can be modified, sounds perfect, right? So here is the deal…

3. OneDrive for Business connector specifics

Accessing Excel files using OneDrive for Business connector does not support delegations (I’m explaining delegations in section 4). Long story short here are the implications:

• You can’t get more than 2000 rows
• OneDriveForBusiness connector treats each table in excel file as separate connection. The 2000 limit is related only to a connection – not the source.

So if you know you may need more than 2000 rows stored in one data source you should switch from OneDrive for Business to any other like SharePoint, Common Data Service, SQL etc. and take use from delegable queries. What are these? Let’s see.

4. Use delegation

Delegation is a mechanism to access all data from a data source in a performance friendly manner.

Speaking a bit more clearly it’s a situation when your PowerApp app says to a data source:
“Ok, you know what, I need items that match these conditions but hey, can you do all the computations by yourself? I need to use my network bandwidth, memory and CPU power for something else…I just need results.”

Now, there are 3 catches:

1. All results are fetched in a maximum of 100 items bundles. The next bundle is being fetched once a user scroll to the end of a gallery/table list (check out delegation demo gif down below)…
2. …which means if you need to fetch more than 100 items (! not process but fetch. You can process 10000 of items but fetch only 15 of them as result) you can only use gallery or table. Collections are not supported and works under non-delegable queries limitation (Collect() or ClearCollect() functions breaks delegation!)
3. Your query need to be supported by the data source. Figuratively speaking the data source must understand what PowerApps app is saying to it (check out example queries on the image down below). To complicate it a bit more not every data source supports all functions and operations – each data source connector documentation outlines delegable support (i.e. here you can find SharePoint Online delegation support). For better understanding of delegations in PowerApps I recommend you to read delegation documentation.

Learn by reading is great. Learn by practicing is even better. Download my Delegation Learning App and start practice delegation now!

Pssst…Have you heard about the 👉www.Developico.com👈?
These Power Platform experts can help you out!

Ok but what if need more than 2000 items here and now? There are 2 options for doing that. But before you read them please consider below:

Following approaches should be treated as potentially bad practices and should be used only under specific circ*mstances and with proper caution since they may have bad impact on your application performance or other O365 tenant services. It’s like with medicines – they can solve your problems but harm you in wrong dosage or used inadequate to needs. So in 95% of cases delegation will perfectly do the job. It may requires read & learn a bit but trust me – it’s easier than struggling with performance issues that may appeared if you misuse options 5 and 6.

Ok, so now we can safely move to options for those 5% of cases 🙂

5. Use delegation + iterative function

General idea is like this: for a delegable query build a loop and in each iteration filter data chunks. I.e. in 1st iteration you get rows 1-500, in 2nd 501-1000 and so on.

I won’t explain it in detail or show you code snippets because this method is not mine. Its author is MS employee Brian a.k.a Mr.Dang() and I would feel bad if I get his credits for this workaround. Read it and just in case it won’t meet your needs…

There is also another way of which I’m the author. Option 6. Here it comes.

6. Combine PowerApps with Flow

The last option is to use Flow as a middle man that was asked this:
“Dear Flow, since your data source connectors don’t care about this whole delegation stuff, can you please do me a favor, get all rows, join them in 1 big string and send me back please?”

A simple demo with excel and outlook email may look like this:

To make this work for more than 256 results I had to make few configuration tweaks of the “List rows present in a table” action:

Fetching 682 rows took 3 seconds.

Fetching 2101 rows took 11 seconds so that may be an issue.

However if you use SharePoint as a data source I have a good news for you – I’ve made a demo that fetches 1000 items in 2 seconds. In this blog post I describe step by step how to:

• Build a PowerApp from scratch
  • Add input fields
  • Pass input fields values to Flow
  • Parse results from flow
  • Display all results in a gallery
• Build a Flow from scratch
  • Get parameters from PowerApps
  • Integrate Flow with SharePoint
  • Send all results back to PowerApps

And that’s it! I hope you enjoy this blog post. If so please let me know in the comments down below. If not or maybe you know a better solution – let me know as well!