Skip to content
Tutorials Center
My learning

Topics

Lesson 15 of 38 ยท Data and Dataverse

Data Sources and Delegation

Learn how Power Apps connects to data sources, what delegation means, the 500 and 2000 row data row limit, delegation warnings, and how to design queries that scale.

Power Apps does not keep your data. It asks a data source for rows when needed. How it asks, and who does the filtering, decides whether your app handles ten rows or ten million. That idea is called delegation, and it is the most important performance concept for canvas apps.

Kinds of data sources

  • Tables in a service: Dataverse, SQL Server, SharePoint lists.
  • Files as tables: Excel in OneDrive or SharePoint (a formatted table).
  • Services via connectors: Office 365 Users, Salesforce, custom APIs.
  • Local data: collections (in memory).

Add them from the Data pane in Studio. After adding, you reference them by name in formulas, such as Tasks.

What delegation means

When you write Filter(Orders, Status = "Open"), one of two things happens:

  1. Delegated: Power Apps sends the filter to the data source, which returns only matching rows. It works on very large tables.
  2. Not delegated: Power Apps downloads a limited number of rows first (the data row limit) and filters locally. The result is incomplete if the table has more rows than the limit.

The data row limit defaults to 500 and can be raised to 2,000 in Settings, General. It applies only to non-delegable operations. There is no row limit on the table itself.

The blue underline and yellow triangle in the formula bar show a delegation warning: "This part of your formula might not work correctly on large data sets".

What is delegable

It depends on the data source. A rough guide:

Function or operator Usually delegable (Dataverse, SQL)
Filter, Sort, SortByColumns Yes
LookUp, First Yes
=, <>, <, >, And, Or, Not Yes
StartsWith Yes (many sources)
in (substring) Often no
Search Depends on source
CountRows, Sum, Average, Min, Max Support varies (Dataverse often yes)
Functions applied to the column, like Lower(Name) = "x" No
Distinct, GroupBy, AddColumns Usually no

SharePoint and Excel support fewer delegable operations than Dataverse and SQL Server. Always read the delegation warnings and the connector's documentation.

Designing for delegation

  1. Filter on the server with simple comparisons: Filter(Orders, CustomerId = varCustomer, Status = "Open").
  2. Avoid wrapping a column in a function (Lower(Title) = ...). Store a normalized column instead.
  3. Prefer StartsWith to in for search boxes on large tables.
  4. For counts on big tables, use a rollup column in Dataverse or a view/stored procedure in SQL.
  5. Keep non-delegable steps for small result sets: first delegate a filter that narrows to hundreds of rows, then use non-delegable functions on that result.

The stand-in below shows why non-delegable filtering is dangerous: when only the first N rows are fetched, matching rows later in the table are missed.

JS Stand-in: delegated versus non-delegated filtering

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Strategies when something is not delegable

  • Change the formula: use a delegable alternative.
  • Pre-filter on the server, then use the non-delegable function on a small set.
  • Server-side logic: a Dataverse view, a SQL view, or a Power Automate flow returning exactly what you need.
  • Cache reference data (small, rarely changing tables like countries) in a collection at startup. Never cache large tables.
  • Paginate: load pages by key (Filter(Orders, ID > varLastID), sorted by ID, take 50).

Reading and refreshing

  • Data is fetched when a formula needs it, and cached for the session. Refresh(Tasks) forces a reload.
  • Writes (Patch, Collect into a source, SubmitForm) go straight to the source and update the cache.
  • Use Monitor to see requests, row counts and durations. It reveals unexpected full table loads.

The next stand-in implements simple keyset pagination, a robust way to move through large data without non-delegable functions.

JS Stand-in: keyset pagination

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Choosing a data source

  • Dataverse: best delegation support, security, relationships; premium license.
  • SQL Server: excellent for existing databases; premium connector.
  • SharePoint: convenient, standard connector; weaker delegation and a 5,000 item list view threshold. Fine for small apps.
  • Excel: for tiny prototypes only; no real concurrency.

Common mistakes

  • Ignoring delegation warnings in a development table of 30 rows, then failing in production with 30,000.
  • Raising the row limit to 2,000 "to fix it". That only hides the problem for a bit.
  • Loading entire tables into collections at start.
  • Filtering with Lower()/Left() on a column and expecting scalability.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is delegation?

    Delegation means Power Apps passes the work to the data source so it can handle large tables.
  2. 2. What is the default data row limit for non-delegable queries?

    The default is 500 and it can be raised to a maximum of 2,000.
  3. 3. Which approach is safer for a large table?

    A plain comparison on a column is delegable; wrapping the column in a function usually is not.
  4. 4. How can you see requests and row counts while the app runs?

    Monitor traces network calls, including data requests and their timings.

Summary

  • Data sources hold the data; formulas ask for rows through connectors.
  • Delegable operations run on the server; non-delegable ones see only the first 500 to 2,000 rows.
  • Read delegation warnings, avoid functions on columns, narrow data on the server and paginate.
  • Dataverse and SQL delegate best.

Next: Dataverse tables and columns