Finding Every Row: 4 Ways to Search Past the 2000-Row Limit in Power Apps

If you've ever built a canvas app that lets users search a SharePoint list, you've probably run into the red squiggly line under your Search() function and the warning: “Delegation warning: The function 'Search' is not delegable.” That means your search will only ever look at the first 2,000 rows (500 by default) of your data source—no matter how many records you actually have. For any real‑world business app, that’s a deal‑breaker.

The good news? There are several proven ways to work around this limitation. In this article, I'll show four practical methods that let you deliver a true full‑data search experience in Power Apps, even when working with SharePoint or other non‑premium connectors.

A Real‑World Scenario: IT Asset Inventory

To make the techniques concrete, I’ll use an IT Asset Inventory app. The underlying data is a SharePoint list called ITAssets with these columns:

The list currently holds more than 5,000 assets, so a plain Search() would miss most of them.

The main screen of the app has a search text input SearchInput and a gallery that should display every matching asset—not just the first 2,000.

Let’s walk through each workaround.

1. The Easiest Trade‑Off: Replace Search with Filter and StartsWith

The Search function is not delegable for SharePoint, but StartsWith is. You can often satisfy user needs by combining Filter with StartsWith on each text column you want to search. The obvious catch: it finds only results where the search term appears at the start of the column value, not anywhere in the middle.

Gallery Items property:

Filter(
  ITAssets,
  StartsWith(AssignedTo, SearchInput.Text) || StartsWith(AssetTag, SearchInput.Text)
)

Because StartsWith is delegable, this query will return matching records from the entire SharePoint list, not just the first chunk. Users searching for an employee’s name will find that employee as long as the name starts with the typed characters.

When to use this: The use case works for “starts with” searches—e.g., looking up an asset tag prefix or a person’s last name. If users need to find a substring in the middle of a word (like searching for “moni” to find “Monitor”), this won’t work.

2. Pre‑filter Data to Bring the Result Set Under 2,000 Rows

Even though Search itself is not delegable, you can use a delegable Filter first to reduce the data to a size that makes the delegation warning safe to ignore. This is especially useful when you have a natural high‑level filter that cuts data significantly.

In my IT asset app, each asset is assigned to a Location (HQ, Branch1, etc.). No single location holds more than 1,500 assets. So I can let the user pick a location from a dropdown, then Search only within that filtered subset.

Dropdown (LocationDropdown) Items:

["HQ", "Branch1", "Branch2", "Depot"]

Gallery Items property:

Search(
  Filter(ITAssets, Location = LocationDropdown.Selected.Value),
  SearchInput.Text,
  "AssignedTo",
  "AssetTag",
  "Category"
)

You will still see the delegation warning on Search, but because the outer Filter already limits the result set to fewer than the data‑row limit (2,000 in advanced settings), you can safely dismiss the warning.

Use this when your data can be split by a high‑cardinality column that guarantees each partition is under the limit. Good alternatives include year, department, country, or status.

3. Power Automate to the Rescue: Full Substring Search Without a Premium License

If you need a true “contains” search that can look anywhere in a text column—not just at the start—and you’re still on SharePoint, you can offload the search to Power Automate. A flow calls SharePoint Get Items with a filter query like substringof('searchterm', FieldName) and sends back the matching records. Power Apps then parses the JSON with ParseJSON() and displays the results.

No premium license required—standard Power Automate with SharePoint works fine.

Flow overview:

• Trigger: Power Apps (when the user clicks “Search” or types, you call the flow).
• Action: Get Items from SharePoint with an OData filter using substringof.
• Action: Compose the result into a JSON array.
• Response: Return the JSON array to Power Apps.

In Power Apps, you call the flow:

SearchResults.Run(SearchInput.Text).SearchResults

Inside the app you convert the flow response to a collection (usually in OnSelect of a search button):

ClearCollect(
  colSearchResults,
  ParseJSON(SearchResults.Run(SearchInput.Text).SearchResults)
)

Then bind your gallery’s Items to colSearchResults.

Pros: Full substring search; works on any column type the flow can filter. Cons: Extra latency for the flow execution; must handle flow failures gracefully.

4. The Future‑Proof Option: Switch to Dataverse and Use the in Operator

If you can move your data to Microsoft Dataverse for Teams (no premium license needed), you gain access to a delegable in operator. The in operator checks whether the search text exists anywhere in the column value—exactly what Search does, but fully delegable.

To enable this, go to File → Settings → Upcoming features → Preview, and enable the delegation of in for Dataverse.

Gallery Items property after switching to Dataverse (table ITAssets):

Filter(
  ITAssets,
  SearchInput.Text in AssetTag || SearchInput.Text in AssignedTo || SearchInput.Text in Category
)

This gives you the flexibility of Search without any delegation warning and without the route of Power Automate.

When to use: If you are starting a new project or have the opportunity to migrate data, Dataverse for Teams is the recommended data source for searching. It provides a much better delegation story out‑of‑the‑box.

Important Considerations Across All Methods

• Data Row Limit: In File → Settings → Advanced Settings you can raise the data row limit from 500 to 2,000, but that only pushes the problem further. If your filtered dataset exceeds 2,000 rows, the first method (pre‑filtering) or the flow is still needed.
• Performance: StartsWith and Filter combined with other delegable functions are performant. Power Automate adds latency—usually a few seconds—so use it for explicit “Search” buttons rather than real‑time typing.
• Testing: Always test your searches with a dataset that has more than 2,000 matching rows to confirm you’re getting all records. Add a label that shows CountRows() of the gallery to verify.
• User Experience: For real‑time search (as the user types), Stick to StartsWith or Filter + in with Dataverse. If you need full substring matching and can accept a small delay, the flow approach works as a separate search action.

Common Mistakes and Troubleshooting

Which Workaround Should You Choose?

Your final recommendation depends on three factors: data size, search pattern, and app latency tolerance.

• Data ≤ 2,000 rows: You can safely use Search as is (maybe ignore the warning). Still, it’s wise to pre‑filter or apply a delegable fallback.
• Data > 2,000 rows and “starts with” is acceptable: Use StartsWith or Filter with delegable operators.
• Data > 2,000 rows, but you have a column that naturally divides data into small groups (location, year, department, etc.): Use Pre‑filter + Search (method 2). It’s simple and fast.
• Need full substring search and no premium license: Build a Power Automate flow (method 3). Expect a few seconds of waiting.
• You can (or plan to) move to Dataverse: The in operator is the cleanest solution. Even Dataverse for Teams gives you this feature without an extra license.

No single approach fits every scenario, but by using one of these four patterns you can finally provide a search experience that covers your entire dataset—whether your users need asset tags, names, or categories.

References

• Original source article: Power Apps Search Function Delegation Warning Workarounds by Matthew Devaney
• Microsoft Learn: Understand delegation in a canvas app
• Microsoft Learn: Filter, Search, and LookUp functions
• Microsoft Learn: Delegable functions for SharePoint