🔹 Handling Delegation in Power Apps – A Complete Guide
📌 Introduction
Delegation is one of the most critical concepts in Power Apps, affecting how data is retrieved and processed from a data source. If not handled properly, delegation issues can limit data retrieval and cause performance problems.
✅ What is Delegation?
Delegation means that Power Apps sends the data processing task to the data source, rather than retrieving all data and processing it within the app. This improves performance, especially for large datasets.
✅ Why is Delegation Important?
- Improves performance by reducing data load in Power Apps.
- Allows working with large datasets (over 500 or 2000 records).
- Prevents data truncation (without delegation, Power Apps only retrieves the first 500/2000 rows).
📌 Without delegation, Power Apps may not return all data, leading to incomplete results!
🔹 Step 1: Understanding Delegable and Non-Delegable Data Sources
Power Apps supports delegation for certain data sources and functions.
✅ Delegable Data Sources (Support Delegation)
These data sources process queries directly on the server:
- Dataverse
- SharePoint (Lists only, not Libraries)
- SQL Server
- Azure SQL Database
- Common Data Service (CDS)
❌ Non-Delegable Data Sources (Do Not Support Delegation)
These sources process data locally within Power Apps, causing performance issues for large datasets:
- Excel files in OneDrive
- Collections (Power Apps stored data)
- CSV files
- Local data sources (Power Apps tables, static data)
📌 Using non-delegable sources means Power Apps can only process the first 500/2000 records.
🔹 Step 2: Delegable vs. Non-Delegable Functions
Some Power Apps functions support delegation, while others don’t.
✅ Delegable Functions (Recommended for Large Data Sets)
| Function | Purpose | Supported Data Sources |
|---|---|---|
| Filter() | Retrieves filtered data | SharePoint, SQL, Dataverse |
| LookUp() | Finds a single record | SharePoint, SQL, Dataverse |
| Search() | Searches within a text column (SQL, Dataverse only) | SQL, Dataverse |
| Sort() | Sorts data (limited delegation) | SharePoint, SQL, Dataverse |
| SortByColumns() | Sorts data efficiently | SharePoint, SQL, Dataverse |
📌 These functions allow querying large data sets without performance issues!
❌ Non-Delegable Functions (May Cause Data Limits)
| Function | Issue |
|---|---|
| First() | Retrieves only the first record from the first 500/2000 records |
| Last() | Retrieves the last record (won’t work beyond first 500/2000 rows) |
| AddColumns() | Creates computed columns (not processed on the server) |
| LookUp() with non-delegable criteria | Only works within the first 500/2000 records |
| Sum(), Average(), CountRows() | Only works on small datasets |
| Collect() / ClearCollect() | Retrieves a non-delegable collection, limited to 500/2000 records |
📌 If these functions are used with large data sets, only the first 500/2000 records are processed.
🔹 Step 3: Changing the Data Row Limit for Delegation
Power Apps defaults to processing only 500 records from a non-delegable data source. You can increase this to 2000 records (maximum).
How to Change the Data Row Limit:
1️⃣ Click on File → Settings.
2️⃣ Navigate to Advanced Settings.
3️⃣ Locate “Data row limit for non-delegable queries”.
4️⃣ Change it from 500 to 2000 (maximum).
📌 Even with 2000 rows, non-delegable queries still won’t process more than 2000 records. Use delegable queries instead!
🔹 Step 4: Optimizing Queries for Delegation
A. Use Delegable Filters
❌ Non-Delegable Query (Incorrect)
Filter(Employees, Age + 2 > 30)
📌 This won’t delegate because of the computed value (Age + 2).
✅ Delegable Query (Correct)
Filter(Employees, Age > 28)
📌 This delegates the query and runs it efficiently!
B. Use Delegable Lookups
❌ Non-Delegable Query (Incorrect)
LookUp(Employees, Name & " " & LastName = "John Doe")
📌 String concatenation (Name & " " & LastName) prevents delegation!
✅ Delegable Query (Correct)
LookUp(Employees, Name = "John" && LastName = "Doe")
📌 This query runs efficiently on the server!
C. Avoid Non-Delegable Math Functions
❌ Non-Delegable Query (Incorrect)
Filter(Orders, Amount * 1.2 > 1000)
📌 Multiplication inside the filter prevents delegation.
✅ Delegable Query (Correct)
Filter(Orders, Amount > 833)
📌 Rearrange the logic to make it delegable.
🔹 Step 5: Handling Delegation Warnings
When Power Apps detects a non-delegable query, it shows a blue warning triangle ⚠️ in the formula bar.
How to Fix Delegation Warnings:
✅ Use a delegable data source (SharePoint, Dataverse, SQL).
✅ Rewrite queries using delegable functions (Filter, LookUp, Sort).
✅ Avoid non-delegable functions (AddColumns, First, Last).
🔹 Step 6: Using Workarounds for Non-Delegable Queries
A. Use Collections for Small Datasets
If your dataset is small (<2000 records), use ClearCollect() to load data into Power Apps.
ClearCollect(LocalEmployees, Employees)
📌 Now, you can use non-delegable functions locally on LocalEmployees.
B. Load Data in Batches for Large Datasets
Since Power Apps limits non-delegable queries to 2000 rows, split the data into smaller parts:
ClearCollect(AllEmployees, Filter(Employees, ID <= 2000));
Collect(AllEmployees, Filter(Employees, ID > 2000 && ID <= 4000))
📌 This workaround loads large datasets in multiple batches.
C. Use Power Automate for Large Queries
For extremely large datasets, use Power Automate to process data externally and return filtered results to Power Apps.
🔹 Conclusion
Handling Delegation Properly Ensures:
✅ Faster app performance
✅ Accurate data retrieval
✅ Better user experience
💡 Best Practices:
🔹 Always use delegable data sources (SharePoint, SQL, Dataverse).
🔹 Write queries with delegable functions (Filter, LookUp, Sort).
🔹 Avoid complex operations inside queries (concatenations, math functions).
🔹 Use collections only for small datasets (≤2000 records).
🔹 For large datasets, use Power Automate or batch processing.