SQL / Power Fx comparison chart

[MikeStall]

Here's a chart to help show how to query with Power Fx and convert common SQL queries into Power Fx.

Functional

Power Fx is functional. See common functions:

• https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-filter-lookup
• https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-sort
• https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-first-last

Delegation

Power Fx will also try to compile the queries to an efficient SQL/Odata call as to avoid running the compute client-side. Client-side code is run first to fetch variables and do local computation, and then the server is invoked. This is called "Delegation". A warning is issued if the call can't be delegated. See https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/delegation-overview for details.

Strongly typed

SQL always returns a table. Power Fx is strongly typed and may return a:

• table (multiple homogenous records)
• record (mapping of field names to scalar types)
• single field (a scalar)

Examples:

SQL	Power Fx	Comment
select * from Accounts	Accounts	return whole table
select top 5 * from Accounts	FirstN(Accounts, 5)	return first 5 in Accounts
select top 5 name,score from Accounts	ShowColumns(FirstN(Accounts, 5), name, score)	return first 5 in Accounts
select * from Accounts where Age > 30	Filter(Accounts, Age > 30)	return just rows where Age > 30
select top 1 * from Accounts where Age > 30	LookUp(Accounts, Age > 30)	return just a single record (not a table).
select top 1 Name from Accounts where Age > 30	LookUp(Accounts, Age > 30).Name	return a scalar for the name field.
'select * from Accounts order by Age'	SortByColumns(Accounts, Age, SortOrder.Ascending)	return whole table, sorted ascending by Age