Power Apps on SharePoint past the row limit: when to use SQL

23 October 2026

Tags: Relational databasesScaleDesktopCommand line

The inspections app worked for two years. Engineers searched for a site, opened the last visit and added a new one, all from a phone. Then a supervisor typed in the name of a depot she had inspected in the spring and the gallery came back empty. The record is in the SharePoint list, and she can see it in the browser. In the app it does not exist.

Somewhere in the formula bar there is a blue wavy line that did not matter while the list was small. Search for the symptom and you find answers about a limit of 2,000 records, a 5,000-item limit and loops that load the list into a collection 2,000 rows at a time. Some of that was true once. This post sets out what the limits are today, from Microsoft’s own pages, what SharePoint will and will not do for a Power Apps formula, and how to tell when the app’s read screens should move to a SQL copy of the list. SPSync makes that SQL copy, so we say so where it comes up, along with what it does not do.

Why a Power Apps gallery misses rows on a SharePoint list

Power Apps tries to hand each query to the data source, so that SharePoint does the filtering and sends back only the matching rows. Microsoft calls that delegation. When a formula contains something SharePoint cannot process, Power Apps falls back to fetching rows itself and filtering them on the device. Microsoft Learn’s delegation overview says that when a query is nondelegable, Power Apps gets the first 500 records by default and then runs the query on those.

The app does not warn the person using it. Learn’s own example is a Filter that cannot be delegated over a large source: if the record you want is number 501, Filter does not find it. Aggregates fail in the same quiet way, so an average over a nondelegable expression averages the first 500 records and shows the result as if it covered the whole list. The only warning is for the maker, as that blue underline in the studio and a yellow triangle on the control.

Is the Power Apps limit 2,000 records?

It was. The setting is called the data row limit, and it sits under Settings, General in the app. Learn’s delegation overview, updated on 7 October 2026, says the default is 500 and that you can change it to a maximum of 10,000 records. The 2,000 figure that most forum answers quote was the older maximum, and our SharePoint list limits cheat sheet keeps both numbers side by side because people still search for the old one.

Raising the limit changes the number and leaves the behaviour alone. A nondelegable formula over a list of 40,000 inspections still sees only the first 10,000, and Microsoft sets the default low because larger result sets slow the app down on every device that runs it. Treat the limit as a safety margin for small data, and plan on the basis that a formula which cannot be delegated will be wrong on a large list.

What SharePoint will and will not delegate

The answer to “will this formula delegate” is in a table on Microsoft’s SharePoint connector page for Power Apps. Read it before you design a screen over a large list. The short version:

• Filter, LookUp, Sort and SortByColumns delegate, and equals works on number, text, yes/no and date columns.
• Greater-than and less-than delegate on number and date columns and do not delegate on text.
• StartsWith delegates on text columns, though not on the subfields of a choice or lookup column.
• Expressions joined with And or Or delegate, and Not does not.
• IsBlank does not delegate. Learn suggests comparing the column with Blank() instead, which behaves slightly differently for empty strings.
• The ID column delegates equals and nothing else, so a filter on a range of IDs runs on the device.
• On a person column only the email and display name delegate.

Search is missing from the SharePoint table altogether, and so are Sum, Average, Min and Max. That accounts for most of the empty galleries people report. A search box wired to Search(Inspections, txtSearch.Text, "Title") is the most natural thing to build and it cannot be delegated to SharePoint. A total at the bottom of a screen built with Sum has the same problem. Both work in testing on a list of a few hundred items and stop being right once the list passes the data row limit.

What happens past 5,000 items in a SharePoint list?

A list can hold up to 30 million items, so the 5,000 figure people meet is about queries. It comes from the list view threshold, which SharePoint applies to operations that would touch every item in a list. Microsoft’s advice for working with large lists, in its support guide to managing large lists and libraries, is to filter on indexed columns so each query returns a smaller set of rows. The cheat sheet covers how many columns you can index.

For a Power Apps maker, that means two jobs on a list past 5,000 items. Index the columns your delegated filters use, and keep every formula on the large list inside the delegable set above. An app built that way can run on a big list for years. The difficulty is that the screens people ask for next, such as a search box or a count of overdue items, are the ones that fall outside it.

The workarounds people try, and where they stop

Most of the advice in forum threads is a variation on one of these. Each has a place, and each has a point where it stops helping.

Raising the data row limit to 10,000 buys time on a list that is still growing towards that size. It also makes every nondelegable query slower, and when the list passes 10,000 the missing rows come back.

Splitting the formula so the delegable part runs first is the best of them. Filter on an indexed date or status column with a delegable condition, and only then apply Search to the smaller result. That works when the first filter reliably cuts the list below the data row limit, and it gives wrong answers, with no error, on the day it does not.

Loading the list into a collection in chunks is the recipe with the most answers. A ForAll or a timer loops over ranges of an ID column and calls Collect until the list is in memory. Learn says collections need no delegation because the data is already on the device, so the warning goes away. Because SharePoint will not delegate a range on the ID column, most versions first copy the ID into a separate number column. Every user pays for the load on every launch, often over a phone connection in the field, and it takes longer as the list grows. The collection is also a snapshot, so it does not see an item someone else saved a minute later.

Splitting the list by year or by region keeps each list small, at the price of the reports and flows that expected one list. It is a migration in all but name.

Is SQL Server better than a SharePoint list for Power Apps?

For a large table that the app reads, it usually is. The SQL Server delegation table covers what SharePoint delegates on ordinary columns and adds the parts that broke the inspections app:

The last two rows matter as much as the first five. SQL is not a cure for every formula, and the same Column = Blank() advice applies there too. Learn also notes that direct date filters do not work against SQL Server through an on-premises data gateway and suggests a computed number column instead, so an Azure SQL database is the simpler target for an app.

The cost is licensing. Microsoft’s SQL Server connector page lists it as a premium connector in Power Apps, while the SharePoint connector is standard. Everyone who runs an app with a SQL data source needs a Power Apps licence that covers premium connectors. Some organisations already have that, and for others it is the deciding cost.

The screens that save changes

Most “move it to SQL” answers skip this part. A replica gives the app somewhere fast to read, and the app still needs somewhere to write.

SPSync copies one way, from SharePoint into the database. SharePoint stays the system of record, and SPSync never writes back to it. Write-back with a person approving each change is designed and on the roadmap and has not shipped. So an app built on an SPSync replica keeps two data sources:

• Galleries, search screens, dashboards and lookups read the SQL table.
• Edit forms and Patch calls keep writing to the SharePoint list, with the list’s own validation, permissions and version history.

The join between the two is the SharePoint item ID, which the replica stores on every row. The gap between them is the sync interval. After an engineer saves an inspection, it appears in the SQL table on the next sync, a few minutes later on the default schedule. Show the saved record from the value Patch returns, rather than sending the user straight back to a gallery that has not caught up.

If the app needs a single store that it reads and writes at this scale, with no lag between the two, then Dataverse is that option. Delegation there is broad, the app writes and reads the same table, and model-driven apps come with it. The Dataverse connector is premium in Power Apps too, and moving to it means the data leaves the SharePoint list the rest of the organisation edits. Our comparison with Dataverse and virtual tables covers when that move is worth it.

How to point a Power Apps app at a SQL copy of the list

The Power Apps performance page describes the pattern. With SPSync it runs like this.

1. Pick the site, the large list and any lists the app’s screens look up to. SPSync shows the schema it will create before it writes anything.
2. Choose SQL Server or Azure SQL as the destination. The first run copies the whole list, reading through Graph’s paging, so the view threshold does not stop it.
3. Set a schedule. Every few minutes is the default, and later runs read only the items that changed. A reconcile, weekly by default and configurable, checks the two sides against each other and catches the copy up after an outage.
4. In Power Apps, add the SQL Server connection and the replicated table as a data source. A lookup column arrives as a key column with its display value beside it, so filters on a lookup delegate as ordinary text or number comparisons.
5. Switch the galleries and search screens to the SQL table, one screen at a time, and leave every form that saves on the SharePoint list.
6. Test with the data row limit set to 1 in a copy of the app. Any screen that still depends on local processing shows the wrong rows straight away.

Deleted items keep their row in the replica with a deletion timestamp, so filter those out in the gallery formula, or use them for a “recently removed” screen. Some things stay in SharePoint. Attachments and version history are not replicated, and SharePoint item permissions are not carried into the database, so if the list uses item-level permissions, the SQL login the app uses decides what the app can see.

When the app should stay on SharePoint

Plenty of apps never need any of this. If every formula on the large list delegates, the columns behind the filters are indexed, and no user has asked for a search box or a running total, the SharePoint connector is free with the Microsoft 365 licences people already have, and it shows each save the moment it happens. Keep it.

The signs that the read screens should move are specific. The business needs a search or a total that cannot be delegated, or a collection load has made the app slow to open. One of those on a list that keeps growing is enough to try the SQL route on a single screen. If you want to see your own list as a SQL table first, book a call and we will run it against your tenant with you.