Using Delegable Queries for Large Data Sets in Power Apps

Handling large data sets efficiently in Power Apps is crucial for performance and user experience. When working with large amounts of data, delegation plays a key role in ensuring that data processing is handled by the data source instead of Power Apps.

This guide provides detailed step-by-step explanations on how to use delegable queries, best practices, and techniques to optimize performance when working with large data sets in Power Apps.

1. Understanding Delegation in Power Apps

1.1 What is Delegation?

Delegation is the process where Power Apps sends data processing tasks to the data source instead of retrieving all records and processing them locally.

This is critical for large data sets because Power Apps has a non-delegable limit of 500 or 2000 records (depending on settings). Without delegation, only a small portion of data is retrieved, causing incomplete results.

1.2 Benefits of Delegation

✅ Handles millions of records efficiently without hitting the data limit.
✅ Improves performance by reducing the load on Power Apps.
✅ Reduces network traffic by processing data at the source.
✅ Ensures accurate results instead of retrieving partial data.

2. Data Sources That Support Delegation

Delegation works only with specific data sources that support query execution. Some delegable data sources include:

✅ Microsoft Dataverse
✅ SharePoint (Lists & Libraries)
✅ SQL Server
✅ Azure SQL Database
✅ Dynamics 365
✅ Salesforce
✅ Common Data Service (CDS)

🚨 Non-delegable data sources (Local collections, Excel, and JSON data) require full data retrieval, making delegation impossible.

3. Configuring Delegation Settings in Power Apps

Before implementing delegation, update the Data Row Limit in Power Apps to allow larger data retrieval.

Step 1: Change Delegation Settings

1️⃣ Open Power Apps Studio.
2️⃣ Go to File → Settings → Upcoming Features.
3️⃣ Scroll down to “Data Row Limit for Non-Delegable Queries”.
4️⃣ Set the value to 2000 (Maximum allowed).
5️⃣ Click Apply and save the app.

🚀 This increases the maximum number of non-delegable rows, but delegation should still be used for efficiency.

4. Delegable Queries in Power Apps

To ensure queries are delegable, use supported functions and operators when retrieving data.

4.1 Delegable Filter Queries

Use Filter() with delegable conditions to retrieve only relevant records.

✅ Delegable Query (Processed at the Source)

Filter(Orders, Status = "Pending")

Retrieves only “Pending” records from the source instead of all records.

❌ Non-Delegable Query (Processed Locally)

Filter(Orders, Left(Status,3) = "Pen")

Power Apps fetches all records first, then applies the Left() function locally.

🚀 Solution: Avoid using Left(), Right(), or Mid() in filters. Use direct comparisons instead.

4.2 Delegable Search Queries

Use Search() for efficient text filtering.

✅ Delegable Search Query (Processed at Source)

Search(Orders, "Laptop", "ProductName")

Filters records where "Laptop" is present in ProductName.

❌ Non-Delegable Search Query

Filter(Orders, StartsWith(ProductName, "Lap"))

🚨 Solution: Use Search() instead of StartsWith() or EndsWith().

4.3 Delegable Sorting Queries

Use Sort() to sort large data sets without fetching all records.

✅ Delegable Sorting Query

Sort(Orders, OrderDate, Descending)

The sorting happens at the data source instead of retrieving all records.

❌ Non-Delegable Sorting Query

SortByColumns(Orders, "OrderDate", Ascending)

🚨 Solution: Use Sort() instead of SortByColumns().

5. Using Delegable Queries in Galleries

To display large data sets efficiently in Galleries, use delegable queries.

Step 1: Set the Items Property with Delegation

Items = Filter(Orders, Status = "Shipped")

🚀 Only “Shipped” records are loaded from the database instead of all orders.

Step 2: Implement Lazy Loading for Large Data

Instead of loading thousands of records at once, load data in small chunks dynamically.

Lazy Loading with Pagination

ClearCollect(OrdersCollection, FirstN(Orders, 100))

Loads only 100 records initially.

Collect(OrdersCollection, FirstN(Skip(Orders, CountRows(OrdersCollection)), 100))

Loads next 100 records on demand.

✅ Prevents API overload and improves app speed.

6. Handling Non-Delegable Scenarios

If a function is not delegable, use alternative methods to ensure efficiency.

6.1 Use Collections to Store Small Data Sets Locally

If data is below 2000 records, load it once into a collection.

ClearCollect(LocalOrders, Orders)

🚀 This prevents multiple API calls while ensuring fast access.

6.2 Use SQL Views or SharePoint Indexed Columns

For SharePoint and SQL, create indexed views to handle large queries.

✅ SQL View Example:

CREATE VIEW ActiveOrders AS
SELECT * FROM Orders WHERE Status = 'Active'

Instead of filtering in Power Apps, query is executed at the database level.

6.3 Use Concurrent() to Speed Up Multiple Queries

If you need to fetch data from multiple sources, run queries in parallel using Concurrent().

Concurrent(
    ClearCollect(Customers, Filter(CustomersData, IsActive = true)),
    ClearCollect(Orders, Filter(OrdersData, OrderDate >= Today()))
)

🚀 Both queries run simultaneously, improving performance.

7. Best Practices for Using Delegation in Power Apps

✅ Use Supported Data Sources → Dataverse, SQL Server, SharePoint.
✅ Filter Data Before Fetching → Use Filter() with direct conditions.
✅ Avoid Non-Delegable Functions → Left(), Mid(), Len(), SortByColumns().
✅ Use Lazy Loading → Fetch records in small chunks dynamically.
✅ Index Key Columns in SharePoint & SQL → Improves query speed.
✅ Cache Small Data Sets Locally → Use ClearCollect() for fast access.
✅ Use Concurrent() for Multiple Queries → Runs API calls in parallel.

8. Conclusion

Using delegable queries ensures that Power Apps can efficiently handle large data sets without performance issues. By optimizing queries, filtering data at the source, and implementing lazy loading, you can create fast, scalable, and user-friendly applications.

Would you like specific optimizations for SharePoint, SQL, or Dataverse?