Power Apps: Delegable Functions Reference

By default, PowerApps tries to deal with the information in the client. For a small number of records, the client can quickly deal with operations like Sort or Filter without issues, but as the data increases, we could end up with crashes and slow apps, especially for slow networks. To improve performance, PowerApps enables the delegation of some operations to compatible data source called delegable functions. PowerApps only fetches from the data source the data that it needs to show. Instead of the full data set. You can find the full Microsoft’s reference explaining this concept.

I’ll go into further detail explaining workarounds for non-delegable functions in Delegation Issue and Workarounds and teach how to take advantage of delegable functions fully. Be aware that, for non-delegable functions, there is a limit of records fetched of 500 that you can increase up to 2000.

Delegable Data Sources

There are a lot of connectors available, but only a minimal subset of them enable delegation. These are:

Common Data Services
Dynamics 365
Salesforce
SharePoint
SQL Server
Exceptions

Let’s explore each one.

Common Data Services

	Number	Text	Option Set	Date Time	GUID
–	NO	NA	NA	NA	NA
+	NO	NA	NA	NA	NA
<	NO	YES	NO	YES	NA
<=	NO	YES	NO	YES	NA
<>	YES	YES	YES	YES	YES
=	YES	YES	YES	YES	YES
>	NO	YES	NO	YES	NA
>=	NO	YES	NO	YES	NA
And (&&)	YES	YES	YES	YES	YES
Average	YES	NA	NA	NA	NA
EndsWith	NA	YES	NA	NA	NA
Filter	YES	NA	YES	YES	YES
In	NO	NO	NO	NO	NO
IsBlank	YES	YES	YES	YES	YES
Left	NA	YES	NA	NA	NA
Len	NA	NO	NA	NA	NA
LookUp	YES	NA	YES	YES	YES
Lower	NA	YES	NA	NA	NA
Max	YES	NA	NA	NO	NA
Mid	NA	YES	NA	NA	NA
Min	YES	NA	NA	NO	NA
Not	YES	YES	YES	YES	YES
Now()	NA	NA	NA	NO	NA
Or (\|\|)	YES	YES	YES	NA	NA
Replace	NA	YES	NA	NA	NA
Right	NA	YES	NA	NA	NA
Search	NA	NA	YES	NA	NA
Sort	YES	YES	YES	YES	NA
SortByColumns	YES	YES	YES	YES	NA
StartsWith	NA	YES	NA	NA	NA
Substitute	NA	NO	NA	NA	NA
Sum	YES	NA	NA	NA	NA
Today()	NA	NA	NA	NO	NA
Trim	NA	NO	NA	NA	NA
TrimEnds	NA	NO	NA	NA	NA
Upper	NA	YES	NA	NA	NA

Limitations:
1. The number cannot contain arithmetic expressions. To work your way around this just check Delegation Issue and Workarounds.
2. Sum, Min, Max, Avg are limited to 50 000 records.

Dynamics 365

	Number	Text	Date Time
–	NO	NA	NA
+	NO	NA	NA
<	YES	YES	YES
<=	YES	YES	YES
<>	YES	YES	YES
=	YES	YES	YES
>	YES	YES	YES
>=	YES	YES	YES
And (&&)	YES	YES	YES
Average	NO	NA	NA
EndsWith	NA	NO	NA
Filter	YES	NA	YES
In	NO	NO	NO
IsBlank	YES	YES	YES
Left	NA	NO	NA
Len	NA	NO	NA
LookUp	YES	NA	YES
Lower	NA	NO	NA
Max	NO	NA	NO
Mid	NA	NO	NA
Min	NO	NA	NO
Not	YES	YES	YES
Now()	NA	NA	NO
Or (\|\|)	YES	YES	NA
Replace	NA	NO	NA
Right	NA	NO	NA
Search	NA	NA	NA
Sort	YES	YES	YES
SortByColumns	YES	YES	YES
StartsWith	NA	NO	NA
Substitute	NA	NO	NA
Sum	NO	NA	NA
Today()	NA	NA	NO
Trim	NA	NO	NA
TrimEnds	NA	NO	NA
Upper	NA	NO	NA

Salesforce

	Number	Text	Date Time
–	NO	NA	NA
+	NO	NA	NA
<	YES	YES	YES
<=	YES	YES	YES
<>	YES	YES	YES
=	YES	YES	YES
>	YES	YES	YES
>=	YES	YES	YES
And (&&)	YES	YES	YES
Average	NO	NA	NA
EndsWith	NA	NO	NA
Filter	YES	NA	YES
In	YES	YES	YES
IsBlank	NO	NO	NO
Left	NA	NO	NA
Len	NA	NO	NA
LookUp	YES	NA	YES
Lower	NA	NO	NA
Max	NO	NA	NO
Mid	NA	NO	NA
Min	NO	NA	NO
Not	YES	YES	YES
Now()	NA	NA	NO
Or (\|\|)	YES	YES	NA
Replace	NA	NO	NA
Right	NA	NO	NA
Search	NA	NA	NA
Sort	YES	YES	YES
SortByColumns	YES	YES	YES
StartsWith	NA	NO	NA
Substitute	NA	NO	NA
Sum	NO	NA	NA
Today()	NA	NA	NO
Trim	NA	NO	NA
TrimEnds	NA	NO	NA
Upper	NA	NO	NA

SharePoint

	Number	Text	Boolean	Date Time
–	–	NA	NO	NA
+	–	NA	NO	NA
<	YES	NO	NO	NO
<=	YES	NO	NO	NO
<>	YES	NO	NO	NO
=	YES	YES	YES	NO
>	YES	NO	NO	NO
>=	YES	NO	NO	NO
And (&&)	–	–	–	–
Average	–	NA	NA	NA
EndsWith	NA	–	NA	NA
Filter	YES	YES	YES	NO
In	–	–	NA	–
IsBlank	–	NO	NA	–
Left	NA	–	NA	NA
Len	NA	–	NA	NA
LookUp	YES	YES	YES	NO
Lower	NA	–	NA	NA
Max	–	NA	NA	–
Mid	NA	–	NA	NA
Min	–	NA	NA	–
Not	–	–	–	–
Now()	NA	NA	NA	–
Or (\|\|)	–	–	–	NA
Replace	NA	–	NA	NA
Right	NA	–	NA	NA
Search	NA	NA	NA	NA
Sort	YES	YES	YES	YES
SortByColumns	YES	YES	YES	YES
StartsWith	NA	–	NA	NA
Substitute	NA	–	NA	NA
Sum	–	NA	NA	NA
Today()	NA	NA	NA	–
Trim	NA	–	NA	NA
TrimEnds	NA	–	NA	NA
Upper	NA	–	NA	NA

SQL Server

	Number	Text	Boolean	Date Time	GUID
–	YES	NA	NO	NA	NA
+	YES	NA	NO	NA	NA
<	YES	NO	NO	NO	NA
<=	YES	NO	NO	NO	NA
<>	YES	YES	YES	NO	YES
=	YES	YES	YES	NO	YES
>	YES	NO	NO	NO	NA
>=	YES	NO	NO	NO	NA
And (&&)	–	–	–	–	–
Average	YES	NA	NA	NA	NA
EndsWith	NA	–	NA	NA	NA
Filter	YES	YES	YES	NO	YES
In	NA	YES	NA	NA	NA
IsBlank	NO	NO	NO	NO	NO
Left	NA	–	NA	NA	NA
Len	NA	YES	NA	NA	NA
LookUp	YES	YES	YES	YES	YES
Lower	NA	–	NA	NA	NA
Max	YES	NA	NA	NO	NA
Mid	NA	–	NA	NA	NA
Min	YES	NA	NA	NO	NA
Not	–	–	–	–	–
Now()	NA	NA	NA	–	NA
Or (\|\|)	–	–	–	NA	NA
Replace	NA	–	NA	NA	NA
Right	NA	–	NA	NA	NA
Search	NO	YES	NO	NO	NA
Sort	YES	YES	YES	YES	NA
SortByColumns	YES	YES	YES	YES	NA
StartsWith	NA	YES	NA	NA	NA
Substitute	NA	YES	NA	NA	NA
Sum	YES	NA	NA	NA	NA
Today()	NA	NA	NA	–	NA
Trim	NA	–	NA	NA	NA
TrimEnds	NA	–	NA	NA	NA
Upper	NA	–	NA	NA	NA

Limitations:

Direct data filters don’t work on SQL Server To work your way around this just check Delegation Issue and Workarounds.
In will only work if string value" in <column>
StartsWith will only work if StartsWith(<column>, "string value")
EndsWith will only work if EndsWith(<column>, "string value")
Beware of char and nchar tables. Len for example, will always return 10 in a char(10) column. A column with a value “hello” will return false for EndsWith(<column>, "llo")

Exceptions

Finally, let’s go over some cases where PowerApps will fetch the information and store it directly in memory. In other words, these are not delegable data sources, but we can take full advantage of PowerApp’s language:

Excel workbooks
Collections
Tables stored in context variables.

Feature Photo by Todd Quackenbush on Unsplash

Kitty - Power Automate: How to round a number?

To explain how the formulas work, you have to know that the mod() function returns the remainder of the division.…

Don Phillips - Power Automate: SharePoint – When a file is created in a folder Trigger

Looks like "When a file is created in a folder" is deprecated. I don't see a way to do this…

David - Power Automate: Save multi-choice Microsoft Forms

This was a huge help- other solutions I saw were using Parse JSON and it couldn't get the '\"' out…

Tim - How to get all document libraries or lists using Power Automate?

Thank you. I had to use body('Parse_JSON')?['d']?['results'] in the Apply to each and items('Apply_to_each')?['EntityTypeName'] for the EntityTypeName. Also, it looks…

Power Apps: Delegable Functions Reference