Power Apps Delegation: Fix Warnings for SharePoint, Dataverse and SQL

Your gallery shows the right items in testing, then a user swears VPN access #1505 exists and the app can’t find it. That’s almost always a delegation problem, and it gets worse as the list grows.

Quick answer: Power Apps delegation means the data source (SharePoint, Dataverse or SQL Server) runs your Filter, Search or Sort and sends back only matching rows. When a formula can’t be delegated, Power Apps shows a yellow triangle, downloads only the first 500 rows (up to 2,000) and works on those. Rewrite the formula with functions your source delegates.

What You Need Before You Start

A data source is where the app’s data lives, such as a SharePoint list or a Dataverse table. Each row in it is a record. Here’s what you’ll need:

• A Microsoft 365 account that can create canvas apps in Power Apps
• A delegable data source: SharePoint, Dataverse, SQL Server or Salesforce
• A list or table with more than 500 rows, so problems actually show up

Our demo list is Service Requests on the /sites/SPGuides site. It has 620 items (IDs 1 to 620) with titles like “VPN access #1505”, plus Status, Priority, Requester, DueDate, RequestedOnText and MeetingStart columns. I tested every formula in this post on that list:

If you’re still choosing a back end, our Dataverse vs SharePoint list comparison covers how each one handles large data.

Build the Demo App Step by Step

If you’ve never built a canvas app, follow these steps first. They match our guide to create a canvas app from a SharePoint list, but start from a blank screen.

1. Sign in to make.powerapps.com. The Home page shows the Start with design options:

2. Select Create in the left pane, then Create from blank (Canvas app):

3. In Start with a blank canvas, pick Tablet. Power Apps Studio opens with an empty screen:

4. Select the Data icon in the left pane, then Add data. Search for SharePoint and pick the SharePoint connector:

5. Under Connect to a SharePoint site, paste your site URL and select Connect:

6. Under Choose a list, check Service Requests and select Connect. The list now shows under Data:

7. Open the Insert menu. That’s where you’ll find the Text input, Vertical gallery, Text label and Button controls used below:

Two more basics. The formula bar (marked fx) sits above the screen, and the property dropdown on its left picks which property you’re editing, such as Items or Default. Press Ctrl+S to save, and select the Play button at the top right (or press F5) to run the app.

Step 1: Spot the Delegation Warning in Power Apps Studio

Insert a Text input, rename it txtSearch in the Tree view, pick Default in the property dropdown and type “VPN access #15”:

Now insert a Vertical gallery, name it galSearch, pick Items and enter the formula below. Search isn’t delegable on SharePoint, so this triggers the warning. We compare the three lookup functions in Filter vs Search vs LookUp in Power Apps:

Search('Service Requests', txtSearch.Text, Title)

Power Apps flags it in two places: a yellow triangle next to the gallery and a blue wavy line under the part of the formula it can’t delegate. The gallery is empty, because the matching items sit past row 500:

Each row in a gallery shows fields of one record through ThisItem. Select the title label inside the gallery and you’ll see its Text property is ThisItem.Title:

Hover over the yellow triangle and Power Apps names the culprit. Ours read: “Delegation warning. The “Search” part of this formula might not work correctly on large data sets.”

Step 2: Test the App and the Data Row Limit

It’s a warning, not an error, which makes it dangerous. Press Play and type in the search box. On my 620-item list, the Search gallery stayed empty while a StartsWith gallery (built in Step 4) found VPN access #1505, #1517 and #1529. Our guide to filter a Power Apps gallery from a search box builds the same pattern:

The cap comes from Settings > General > Data row limit, described as how many rows are retrieved “where delegation is not supported.” It defaults to 500 and accepts 1 to 2,000. A separate guide covers this setting in depth:

Here’s the testing trick Microsoft recommends: set it to 1. Any formula that isn’t delegated now returns a single record. With the limit at 1, ClearCollect of the whole list returned exactly 1 row for me:

Broken screens jump out immediately. Put the limit back to 500 before you publish.

Step 3: Check What Your Data Source Delegates

Delegation depends on both the function and the data source. Filter, LookUp, Sort and SortByColumns delegate on all three sources below, but the operators inside them differ. Our Power Apps Filter SharePoint list examples show more patterns on real columns.

SharePoint

SharePoint delegates =, And and Or, plus <, >, <=, >= on number and date columns. StartsWith works on text. Search, the in operator, Not and IsBlank don’t delegate. The ID column only delegates with =, and person columns only on Email and DisplayName.

I built a test screen with CountRows over seven filters, data row limit 500. The list has 52 VPN items:

Filter on Service Requests	Result	Delegated?
“VPN” in Title	42 (wrong)	No, only rows 1-500 checked
StartsWith(Title, “VPN”)	52	Yes
Status.Value = “Closed”	206	Yes
DueDate >= Date(2026, 9, 1)	82	Yes
Not(IsBlank(Requester))	500 (capped)	No
Requester <> Blank()	500 (capped)	No
Len(Title) > 0	500 (capped)	No

Every row shows a yellow triangle, because CountRows itself doesn’t delegate on SharePoint. The count is right only when the inner Filter delegates and returns fewer rows than the limit.

Dataverse

Dataverse delegates the most. Search, StartsWith, in (membership and substring), IsBlank and the aggregates Sum, Min, Max and Average all delegate. Aggregates are capped at 50,000 rows, and CountRows returns a cached count.

SQL Server

SQL Server delegates Search, StartsWith, EndsWith, “text” in Column, arithmetic on numbers and Sum, Average, Min and Max. IsBlank doesn’t delegate; use Column <> Blank() instead. For heavy logic, a SQL view does the work on the server.

On every source, If, Len, Left, Mid, Lower, Upper, Text(), Value() and the & operator inside a Filter break delegation. So do FirstN, Last, Concat, GroupBy and Collect.

Step 4: Rewrite the Formula So It Delegates

Swap Search for StartsWith on SharePoint. It only matches the start of the text, but it checks every item, not just the first 500. Our StartsWith and EndsWith functions guide has more variations. Add a second gallery, galStarts, with this Items formula:

Filter('Service Requests', StartsWith(Title, txtSearch.Text))

There’s no yellow triangle this time, and the gallery lists VPN access #1505 and up:

Replace in with Or. Each part is a plain = test on the choice value, which SharePoint delegates:

Filter('Service Requests',
    Status.Value = "New" || Status.Value = "In Progress")

Date comparisons delegate too, as the 82 in the test table showed. Microsoft documents = Blank() as a delegable stand-in for IsBlank on SharePoint, but <> Blank() isn’t, and it capped at 500 in my test.

Filter first, then do the rest locally

When you need a non-delegable step, wrap it around a delegable Filter that cuts the rows down first. The outer step still sees only the first 500 to 2,000 results, so keep the inner filter tight:

AddColumns(
    Filter('Service Requests', Status.Value = "New", DueDate >= Date(2026, 10, 1)),
    DaysLeft, DateDiff(Today(), DueDate))

Index the SharePoint columns you filter on

Lists over 5,000 items hit the SharePoint list view threshold even when the formula delegates. Index Status, DueDate and other filter columns, as shown in our SharePoint indexed columns guide.

Step 5: Use Collections and Power Automate With Care

A collection is a table the app keeps in memory. ClearCollect isn’t delegable: at a limit of 500, ClearCollect(colRequests, 'Service Requests') gave me 500 rows, with Max(ID) = 500. A Power Apps collection is fine for small lookup tables.

For a mid-size list, collect it in chunks that each stay under the limit. Each status has fewer than 500 items, so this loaded all 620 rows at a limit of 500:

Clear(colAllRequests);
ForAll(["New", "In Progress", "Closed"] As s,
    Collect(colAllRequests, Filter('Service Requests', Status.Value = s.Value)))

For a full-list search or export, let a flow do it. A cloud flow’s Get items action with a filter query and pagination can return thousands of rows, as in our guide to retrieve SharePoint list data using Power Automate.

Power Apps Delegation Cheat Sheet

Here’s what we check first when a formula shows the yellow triangle:

Function / operator	SharePoint	Dataverse	SQL Server
Filter, LookUp, Sort	Yes	Yes	Yes
=, And, Or	Yes	Yes	Yes
<, > on text	No	Yes	No
StartsWith	Yes (text)	Yes	Yes
Search	No	Yes	Yes
in	No	Yes	Substring only
IsBlank	No, use = Blank()	Yes	No, use <> Blank()
Sum, Average, Min, Max	No	Yes (50,000 rows)	Yes
Len, Left, Lower, If, &	No	No	Len only

Does CountRows Delegate on a SharePoint List?

No. CountRows always shows the warning on a SharePoint list, and it counts only rows Power Apps downloaded. In my test it gave 52 when the inner StartsWith filter delegated, but 42 for “VPN” in Title, which only checked rows 1-500. Our CountRows function guide with the delegation fix shows how to get the true total.

Common Mistakes to Avoid

• Raising the limit to 2,000 and calling it fixed. It only hides the problem until the list passes 2,000 items.
• Wrapping a column in Text(), Lower() or Len(). One non-delegable function makes the whole query local.
• Using Not or <> Blank() on SharePoint. Neither delegates there; filter for the positive value instead.
• Comparing the SharePoint ID with > or <. Only = delegates on ID, so use a separate number column for ranges.
• Assuming no warning means it’s delegated. Formulas over collections, With() or variables never show a warning.

If you build apps like this every week, our Power Apps training course walks through delegation on real lists.

Next Steps

Open your busiest app, set the data row limit to 1, and fix every screen that breaks. Then build a search box that scales with our walkthrough to search a SharePoint list in Power Apps, or browse more in our Power Apps tutorials.

We’d pick one of these next if you’re tuning galleries for large lists:

• Filter Power Apps Gallery by Current User
• How to Use Power Apps Search Function + 6 Various Examples
• Display SharePoint List in Power Apps – 5 Various Ways
• 12 Useful Power Apps Sort Gallery Examples

Frequently Asked Questions