SharePoint lists vs Dataverse for Power Apps: delegation, limits, licensing

Use SharePoint lists for small, simple canvas apps whose users have only Microsoft 365 licences and whose formulas stay within the narrow set of operations SharePoint can delegate; use Dataverse when the data is relational, will grow beyond a few thousand rows that users must search and aggregate, needs role-based security, or must move through a managed dev-test-production process. The deciding factors are delegation (whether Power Apps can push a query to the data source or silently processes only the first 500 to 2,000 rows), SharePoint's list view threshold, and licensing, because Dataverse makes a standalone app premium.

Who this is for and what you will have

This guide is for makers, architects and Power Platform administrators choosing a data source for a new Power Apps solution, or deciding whether an existing SharePoint-based app should move to Dataverse. At the end you will have a side-by-side comparison of delegation support, a summary of the hard limits on each platform, the licensing consequences of each choice, a decision checklist, and a short procedure to test a design for delegation problems before users find them.

Why delegation decides most of this

When a Power Fx query can be translated into a query the data source understands, Power Apps delegates it: the source filters and sorts the data and returns only the matching rows. If any part of the query uses a function or operator the data source doesn't support, Power Apps delegates none of it. It retrieves the first 500 records and evaluates the formula on the device.

You can raise that local limit under Settings > General > Data row limit to a maximum of 2,000. Above that there is no setting to change. A non-delegable Filter over a list of 50,000 items scans only the first 500 or 2,000; if the record you need is number 2,001, the app doesn't find it and doesn't report an error. Aggregates behave the same way: a non-delegable Average over a large source averages only the first rows.

Power Apps flags these formulas with a yellow warning triangle and a blue underline, but only when the data source supports delegation at all. Microsoft suggests setting the data row limit to 1 during testing, so any non-delegated query returns a single record and is obvious.

Some functions never delegate on any source, including FirstN, Last, LastN, Choices, Concat, Collect, ClearCollect, GroupBy and Ungroup. Collections, and anything created with With, Set or UpdateContext, are in memory and don't participate in delegation.

Delegation compared: SharePoint vs Dataverse

Both are delegable data sources, but the supported operations differ substantially. This table summarises Microsoft's per-connector delegation lists.

SharePoint caveats that catch people out

• The ID column. SharePoint list IDs appear as numbers in Power Apps but are text underneath. Only = delegates on ID; ID > 100 doesn't.
• Person columns. Only the Email and DisplayName subfields are delegable.
• System fields. Fields such as Name, Path, FullPath, ContentType, IsFolder and VersionNumber generally don't delegate.
• IsBlank. Filter(Orders, IsBlank(CustomerId)) doesn't delegate, but Filter(Orders, CustomerId = Blank()) does. They aren't strictly equivalent, because the second form doesn't treat an empty string as blank, and the trick works only for =, not <>.
• Bulk changes. UpdateIf and RemoveIf run locally and only simulate delegation up to the 500 or 2,000 record limit.

A typical search screen shows the difference. On Dataverse, this delegates:

Search(Tickets, txtSearch.Text, title, description)

On a SharePoint list, Search isn't delegable, so the closest delegable alternative is a prefix match on a single text column:

Filter(Tickets, StartsWith(Title, txtSearch.Text))

That finds titles that start with the search text, not titles that contain it. If users expect "contains" search across a large list, SharePoint can't deliver it through delegation.

Dataverse caveats

Dataverse isn't unlimited either. Numeric arithmetic inside a filter (Filter(table, field + 10 > 100)) isn't delegable, Len and TrimEnds aren't supported on text, DateTime delegation excludes the Now() and Today() functions, and aggregates are limited to 50,000 rows. A query can include at most two levels of lookup expansion. These limits are far less restrictive than SharePoint's for typical line-of-business screens.

Platform limits that matter for apps

The list view threshold is the limit most apps hit first. Microsoft describes it as the maximum number of items a database operation can process at one time; operations that exceed it are blocked, and you may see a warning on the list settings page once a list passes 3,000 items. Indexing the columns you filter on, and keeping views filtered, is how you work within it.

Security and ALM

Security. A SharePoint list inherits site permissions, and item-level permissions are possible but limited by the unique-permissions numbers above. Anyone with read access to the list can open it directly in SharePoint or Microsoft Lists, bypassing the app's screens. Dataverse controls access with security roles and business units, so access is enforced in the data platform rather than by which screens an app happens to show.

ALM. Dataverse tables are solution components. They deploy with the app as part of a managed solution, and a canvas app connected to Dataverse automatically looks for the same table in each environment it moves to. SharePoint lists aren't solution components. To move a SharePoint-based app between environments you need data source environment variables for the site and the list, and Microsoft documents that the list's display names and internal column names must match between source and target. A list recreated with the same name and columns in another site won't have matching internal identifiers. For the full deployment process, see Power Platform ALM with managed solutions and pipelines.

Either way, solutions and pipelines require a Dataverse database in each environment, so choosing SharePoint for the data doesn't remove Dataverse from a governed ALM process.

Licensing

This is often the deciding factor, so get it right before you design.

• SharePoint. Microsoft 365 licences include limited Power Apps use rights intended for Microsoft 365 data and standard connectors. A canvas app over SharePoint lists, with no premium connectors or other premium features, runs under those rights.
• Dataverse. Microsoft lists Dataverse tables, premium connectors, on-premises data gateways and custom APIs among the capabilities that make an app premium. Every user then needs premium use rights, such as Power Apps Premium or the Power Apps per app pay-as-you-go meter. Model-driven apps use Dataverse and are premium by default.
• Dataverse for Teams. An app built on Dataverse for Teams can be run by users with only an Office-seeded licence, but only when it is embedded in Teams. Played standalone, it prompts for a licence.
• Managed environments. When an environment is enabled as a managed environment, all active usage requires a qualifying licence, including standard apps over SharePoint. Microsoft states that from February 2027 these requirements are enforced strictly: users without an appropriate licence can't open the app.
• Per app plans. Microsoft notes that the Power Apps per app licence is no longer available to new customers through some purchasing channels from January 2, 2026.

Check the App Details page in the maker portal to see an app's licence designation; after removing premium connectors you must publish the app before the designation updates. For exact use rights, the Power Platform Licensing Guide is the authoritative source.

Step-by-step: test a design before you commit

1. List the screens and their queries. For each gallery and lookup, write down the filter, search and sort the user needs, and the expected row count after a year of use.
2. Check every query against the table above. Any query that needs Search, in, Not, IsBlank on text, or aggregates over more than 2,000 rows will not delegate to SharePoint.
3. Build a throwaway prototype. Connect to a test list or table with representative volume. On SharePoint, index the columns you filter on.
4. Set the data row limit to 1. In Power Apps Studio go to Settings > General > Data row limit and enter 1. Any gallery that now shows a single row is running a non-delegated query.
5. Fix or redesign. Rewrite formulas with delegable equivalents, such as StartsWith instead of Search or = Blank() instead of IsBlank. If you can't, that requirement points to Dataverse.
6. Confirm licensing. Check the app's licence designation in the maker portal, and confirm whether the target environment is, or will become, a managed environment.
7. Restore the data row limit to 500 or a deliberate value before publishing.

Verification

• No delegation warnings (yellow triangle or blue underline) remain on galleries and lookups over large sources.
• With the data row limit at 1 in a test copy, every screen still returns complete results.
• On SharePoint, filtered views and app queries work on a list with more than 5,000 items.
• The app's licence designation matches what users are licensed for.

Troubleshooting

Gallery shows only 500 items. The Items formula isn't delegated. Hover over the warning to see which part breaks delegation, then rewrite it or move the source.

Search finds nothing past the first rows on SharePoint. Search isn't delegable to SharePoint. Use StartsWith on an indexed text column, or move the data to Dataverse.

List view threshold error in SharePoint. A view or query processes more than 5,000 items. Index the columns the view or query filters on, and keep views filtered.

Users prompted to start a trial when opening the app. The app is premium (Dataverse, a premium connector, a gateway) or runs in a managed environment, and the user lacks a qualifying licence.

Dataverse for Teams app prompts for a licence in the browser. Office-seeded users can only run it embedded in Teams.

SharePoint-based app breaks after moving environments. Environment variables for site and list exist, but the target list's internal column names differ. Microsoft notes that the metadata always matches when you duplicate a SharePoint site and copy it to the target, so copy rather than recreate lists by hand.

Decision checklist

Choose SharePoint lists when all of these are true:

• Fewer than a few thousand rows per list, or queries that always delegate with indexed columns.
• No requirement for "contains" search, counts or totals over the full data set.
• Simple permissions at site or list level.
• Users have only Microsoft 365 licences and the environment isn't a managed environment.

Choose Dataverse when any of these is true:

• Relational data with lookups across several tables.
• Search, counts or aggregates over large data sets.
• Access control enforced in the data platform with security roles and business units.
• A managed dev-test-production process with solutions and pipelines.
• Users already have premium licences, or the app will run in a managed environment anyway.

References

• Understand delegation in a canvas app
• Connect to SharePoint from a canvas app
• Connect to Microsoft Dataverse from a canvas app
• SharePoint limits
• Manage large lists and libraries
• Use environment variables in Power Platform solutions
• Licensing overview for Microsoft Power Platform
• Power Apps licensing FAQs