Power Apps delegation is the processing of a query within the data source itself rather than by Power Apps. Delegable queries are far more efficient because the query is executed quickly on the server and the full set of results are sent to Power Apps
Delegable Queries
In the example below. Power Apps sends a delegable query to the data source requesting all the blue records. Because the query is delegable, the data source processes the query and returns the results that are displayed in the app
Non-delegable Queries
A non-delegable query cannot be processed by the data source. The data source simply sends the first 500 queries to Power Apps. Power Apps then executes the query against the data it receives and displays the results, which is only a subset of the requested data
The default Canvas apps delegation limit is 500 records. This can be increased up to 2000, but non-delegable queries should be avoided for the following reasons:
- An apps’ performance can be impacted by the delay waiting for the data to be passed over the network
- When the data is received, executing the query will take longer on a relatively low spec user device such as a mobile phone or tablet, rather than a high-end cloud server
- Most importantly, for non-delegable queries, Power Apps sets a limit to the number records that will be transferred from the data source. This is defined in the settings section of each Canvas App. The setting is set to 500 by default and may be raised to a maximum of 2000. If there are a greater number of records in the data source than the defined delegation limit, then the number of records transferred will be truncated. Hence the data processed and the results returned may well be incomplete
When a query isn’t delegable, blue lines are sometimes displayed against the non-delegable section in the formula bar, however this isn’t always the case. Commonly used formulas such a Collect, ClearCollect and ForAll are not delegable but beware, the blue warning lines aren’t displayed for these functions
Power Apps Delegation
An added complication is that some formulas can be partially delegable. Structuring formulas properly is important to ensure a query returns the correct results
To look at some examples, I’ve created an app connected to a Dataverse table containing the 92 counties in Great Britain and Northern Ireland. My aim is to display the 7 counties that start with the letter B. For demonstration purposes, the delegation limit of the app is set to 10
I have conducted 5 queries and each of the results are shown below
1) Directly From the Dataverse Table
To list all the counties in Great Britain and Northern Ireland sorted alphabetically directly from the table, set the items property of the gallery to the following:
Sort(Counties,Name)
All the 92 counties are displayed and there are no delegation issues. The query is processed in its entirety on the Dataverse server and the results returned to Power Apps
2) Directly from the Dataverse Table Via a Filter
StartsWith and Filter are fully delegable with Dataverse. Sort may be delegable depending on its parameters, which is the case with this query
Sort(Filter(Counties,StartsWith(Name,”B”)))
This formula produces the desired results and the 7 counties starting with B are displayed
3) Add the Data to a Collection and then Display
The third test is to pre-load the county data into a collection. This can be done with the OnSelect property of a button set to the first formula below. Then display the results in a gallery using the second formula as the galleries Items property
ClearCollect(colCounties,Counties)Sort(colCounties,Name)
Only 10 records are displayed! This is because the ClearCollect (and Collect) function are not delegable so only the first 10 records out of the full set of 92 are sent to Power Apps. The blue lines in the formula bar that warn when a formula isn’t delegable aren’t displayed, but don’t be misled, a collection created by loading a data set that is larger than the delegation limit will be incomplete
4) Add the Data Into a Collection and then filter
This is done using the same ClearCollect formula above and then the formula below is used in the Items property of a gallery to display the results
Sort(Filter(colCounties,StartWith(Name,”B”),Name)
As you’d expect, In this case only 3 records are returned. This is because the source I am using is a collection of only 10 records, and of those records only 3 records start with B so the result is incomplete
5) Filter the Dataverse Table and Add to a Collection
The formula below is added to the OnSelect property of a button. The results is displayed in a gallery with the Items property set to the second formula
ClearCollect(Filter(colFilteredCounties,StartWith(Name,”B”))
Sort(colFilteredCounties,Name)
This displays all the 7 counties that begin with the letter B
The reason for this is that this query is partially delegable. Dataverse formulas are calculated from the inside out. The Filter section is executed first and because it is delegable, all the 7 counties that start with B are returned. Those 7 records are then added to the collection and because the record count is below the delegation limit of 10 the returned data is complete. If the delegation limit was reduced to 5 then the data would have only 5 records and not be incomplete
Summary
Thinking through both the structure of formulas and the data on which they will be used is important
For a small set of data, such as the Counties table with 92 records, if the app delegation limit is set to 500, even a non-delegable query is not going to be a problem. For a much larger data set, for example incidents being raised at a corporate helpdesk, a non-delegable query could well return incomplete data. Making the query partially delegable (for example filtering for open cases being managed by a specific helpdesk advisor) reduces the risk of incomplete data
There are advantages to loading data into collections and working on it locally rather than continually interrogating and updating the data source direct. However, care does need to be taken with this approach. The above examples demonstrate how this may create problems with delegation unless formulas are structured correctly
Whilst I have used Dataverse in my examples, the same principles apply to other data sources such as SharePoint. The Power Apps functions that are delegable can vary by data source. Full details can be found here
Related Posts
- Hiredgun.tech: Sorting a Power Apps Gallery