Power Fx Cheat Sheet

A working reference for Power Fx — the low-code formula language behind Power Apps, cards, and Copilot Studio. Text, logic, tables, error handling, people, choices, theming, offline storage, reusable components, and the delegation rules that decide whether a formula scales.

Power Fx30 min readUpdated 2026-06-16

Power AppsPower FxCanvas AppsVariablesPatternsDelegationError HandlingOffice 365 UsersThemingComponentsFormulas

Fundamentals

Power Fx is declarative and functional: you describe *what* a value should be, and the runtime recalculates it when dependencies change — like a spreadsheet cell. There are no statements that run top-to-bottom; behaviour formulas (on OnSelect, OnChange) chain side effects with ;.

Where it runs: canvas apps, model-driven custom pages, cards for Teams/Outlook, and Copilot Studio topics.
Named formulas (App.Formulas) define reusable values that recompute automatically — prefer them over OnStart variable soup.
`=` vs `:=` — comparisons use =; record/scope assignment in functions like Patch/With uses :.
Operators: & concatenates, &&/And, ||/Or, !/Not, in / exactin for membership.

Use named formulas, not OnStart

Values defined in App.Formulas are lazy and always current, so the app opens faster and you avoid stale globals. Reserve OnStart for one-time navigation or Set calls that genuinely must run once.

Text functions

12/12

Function	What it does	Example → result
Concatenate / &	Join strings	"Hi " & First.Name → "Hi Ada"
Left / Right / Mid	Substring by position	Mid("FlowLibs",1,4) → "Flow"
Len	Length of a string	Len("abc") → 3
Lower / Upper / Proper	Change case	Proper("ada lovelace") → "Ada Lovelace"
Trim / TrimEnds	Remove extra spaces	Trim(" a b ") → "a b"
Substitute	Replace by match (all)	Substitute("a-b-c","-","/") → "a/b/c"
Replace	Replace by position	Replace("12345",2,2,"X") → "1X45"
Find	Position of substring (1-based)	Find("@","a@b") → 2
StartsWith / EndsWith	Prefix / suffix test	EndsWith("report.pdf",".pdf") → true
Text	Format number/date to string	Text(1234.5,"$#,##0.00") → "$1,234.50"
Value	Parse string to number	Value("42") → 42
Split / Concat	String ↔ table of values	Split("a,b,c",",") → 3-row table

Logic & conditionals

7/7

Function	What it does	Example
If	Branch on condition(s)	If(Score >= 50, "Pass", "Fail")
Switch	Match a value to results	Switch(Status,"New",1,"Done",3,0)
Coalesce	First non-blank value	Coalesce(Nickname, FullName, "Guest")
IsBlank / IsEmpty	Null / empty-table test	If(IsBlank(Email), Notify("Required"))
IsError / IfError	Catch + replace errors	IfError(1/Qty, 0)
And / Or / Not	Boolean composition	And(IsToday(Due), !Done)
in / exactin	Membership (case-insen / -sen)	"sql" in "Azure SQL" → true

IfError beats IsError

Wrap risky expressions with IfError(value, fallback) so a single bad row or divide-by-zero does not blank the whole control. Use IfError(value, fallback, afterError) when you also need to log.

Error handling & signals

With formula-level error management on (default for new apps), errors flow through your formulas and surface to the user unless you catch them. Catch them with IfError in a formula, report them centrally in App.OnError, and raise your own with Error().

7/7

Function	What it does	Example
IfError / IsError	Catch + replace, or test	IfError(Patch(DS,…), Notify("failed"))
IsBlankOrError	Blank or error in one test	IsBlankOrError(LookUp(U, Id = x))
Error	Raise or rethrow a custom error	Error({Kind: ErrorKind.Validation, Message: "Bad input"})
Errors(source)	Errors from the last data op	Errors(Tasks)
FirstError / AllErrors	Error context in a handler	Notify(FirstError.Message)
Notify	Banner message (≤ 500 chars)	Notify("Saved", NotificationType.Success)
Trace	Telemetry to Monitor / App Insights	Trace("checkout", TraceSeverity.Information)

Try / catch a save

powerfx

IfError(
    Patch(Orders, Defaults(Orders), { Total: Value(txtTotal.Text) }),
    Notify($"Could not save: {FirstError.Message}", NotificationType.Error);
    Trace("save failed", TraceSeverity.Error, { detail: FirstError.Message })
)

IfError stops the chain on failure and gives you FirstError for the message + log.

Form OnSuccess / OnFailure

powerfx

// Submit button — OnSelect
SubmitForm(frmEdit)

// Form — OnSuccess
Notify("Saved", NotificationType.Success); Back()

// Form — OnFailure
Notify(frmEdit.Error, NotificationType.Error)

Let the form raise the events — don’t hand-roll success/failure in the button.

App.OnError is report-only

By the time App.OnError runs the error already happened — you can log it via FirstError/AllErrors but you can’t replace it; only IfError can do that. To pass an error up the chain, rethrow with Error(AllErrors). If you invent your own error codes, use values above 1,000 to avoid clashing with system errors.

Tables & collections

11/11

Function	What it does	Example
Filter	Rows matching a condition	Filter(Tasks, Status = "Open")
Search	Substring search across columns	Search(Tasks, txtFind.Text, "Title")
LookUp	First matching row (or a field)	LookUp(Users, Id = 7).Email
Sort / SortByColumns	Order a table	Sort(Tasks, Due, Ascending)
FirstN / LastN / First / Last	Take rows	FirstN(Sort(T, Date, Descending), 5)
AddColumns / DropColumns	Reshape a table	AddColumns(T, "Year", Year(Date))
CountRows / CountIf	Count	CountIf(Tasks, Status = "Open")
Distinct / GroupBy	Unique values / aggregate	Distinct(Tasks, Owner)
ForAll	Map a formula over rows	ForAll(Cart, Patch(...))
Collect / ClearCollect / Patch	Write to a collection / source	ClearCollect(colCache, Source)
With	Local named scope	With({r: LookUp(U, Id=x)}, r.Name)

Patch a record (create or update)

powerfx

Patch(
    Tasks,
    Coalesce(LookUp(Tasks, Id = selectedId), Defaults(Tasks)),
    {
        Title: txtTitle.Text,
        Status: ddStatus.Selected.Value,
        DueDate: dpDue.SelectedDate
    }
)

Create when ID is blank, otherwise update — the defaulted-record pattern.

Collection shaping

These functions return a *new* shaped table — they never mutate the source. Reach for them to trim columns before caching, build lookup tables, or roll child rows up onto a parent.

8/8

Function	What it does	Example
AddColumns	Append computed columns	AddColumns(Cart, "Line", Qty * Price)
DropColumns	Remove columns	DropColumns(Cart, "Internal")
RenameColumns	Rename columns	RenameColumns(Data, "old", "new")
ShowColumns	Keep only these columns	ShowColumns(Users, "Id", "Mail")
GroupBy	Group rows into a nested table	GroupBy(Sales, "Region", "ByRegion")
Ungroup	Flatten a grouped table	Ungroup(grp, "ByRegion")
ForAll	Project a new table per row	ForAll(Sequence(5), { n: Value })
Sequence	Generate N rows (1..N)	Sequence(7) → 1..7

Roll a child total onto each group

powerfx

AddColumns(
    GroupBy(LineItems, "OrderId", "Lines"),
    "OrderTotal", Sum(Lines, Amount)
)

Build a table from a delimited string

powerfx

ForAll(
    Split(txtCsv.Text, ",") As line,
    { Value: Trim(line.Value) }
)

ForAll returns a table — it is not a loop

ForAll evaluates its formula for every row and returns the resulting table; it has no guaranteed order. Don’t use it purely for side effects when a set-based function (UpdateIf, RemoveIf, or Patch(Source, ForAll(...))) will do the same write in one delegated call.

Records, scope & references

6/6

Keyword	What it refers to	Example
ThisItem	Current row in a gallery / form	ThisItem.Title
ThisRecord	Current record in ForAll / Filter / Sum	ForAll(Cart, ThisRecord.Qty * ThisRecord.Price)
Self	The control the formula is on	Self.Fill
Parent	The containing control / screen	Parent.TemplateHeight
As	Name a record scope to avoid ambiguity	ForAll(Orders As o, o.Total)
With	Local named values for one formula	With({r: LookUp(U, Id = x)}, r.Name)

Use As to disambiguate nested scopes

When you nest ForAll/Filter/Sum, the inner ThisRecord shadows the outer one. Name each scope with As (e.g. ForAll(Orders As o, ForAll(o.Lines As l, ...))) so you can reach both.

Variables & state

7/7

Function	Scope	Example
Set	Global variable (whole app)	Set(gblUser, User().Email)
UpdateContext	Context variable (one screen)	UpdateContext({locIsOpen: true})
Collect / ClearCollect	Collection (in-memory table)	ClearCollect(colCart, Source)
Clear	Empty a collection	Clear(colCart)
Patch	Create / update a record	Patch(Tasks, Defaults(Tasks), {Title: "New"})
Remove / RemoveIf	Delete rows	RemoveIf(Tasks, Status = "Done")
UpdateIf	Bulk-update rows	UpdateIf(Tasks, Status = "New", {Status: "Open"})

Set vs UpdateContext vs Collect

Use Set for values shared across screens, UpdateContext (or Navigate(Screen, t, {param})) for values local to one screen, and Collect/ClearCollect for tables of rows. Prefer named formulas for derived values that never need to be set imperatively.

Choices & option sets

Bind dropdowns and combo boxes to Choices() so they stay in sync with the column metadata instead of a hand-typed Table(). SharePoint choices return {Value}; Dataverse choices return the localized label set.

7/7

Function / property	What it does	Example
Choices(source.Column)	Valid values for a choice / lookup column	Choices(Tasks.Status)
Defaults(source)	Blank record with column defaults	Defaults(Tasks)
ddl.Selected.Value	Picked value (single-select)	ddStatus.Selected.Value
cmb.SelectedItems	Picked rows (multi-select)	cmbTags.SelectedItems
DefaultSelectedItems	Preselect a combo box	Filter(Choices(Projects.Tags), …)
IsBlank(.Selected)	Nothing picked yet?	IsBlank(ddStatus.Selected)
Distinct(source, Col)	Unique values when there is no choice column	Distinct(Tasks, Owner)

Patch single + multi choices

powerfx

Patch(
    Tasks,
    Coalesce(LookUp(Tasks, Task = selectedId), Defaults(Tasks)),
    {
        Status: ddStatus.Selected,        // single choice → the record
        Tags: cmbTags.SelectedItems       // choices (multi) → a table
    }
)

A single choice takes the option-set record; a multi-choice takes a table of records.

Preselect a combo box on an edit form

powerfx

Filter(
    Choices(Projects.Tags),
    Value in ThisItem.Tags.Value
)

ComboBox DefaultSelectedItems — match the row’s current values back to the choice set.

Choices keeps the dropdown honest

Because Choices() reads the column definition, adding an option at the source shows up in the app with no code change. Bind a combo’s Items to Choices(Source.Column) and only fall back to Distinct when the column isn’t a real choice/lookup.

Users & people

7/7

Function	What it returns	Example
User()	Signed-in user (built-in, no connector)	User().Email → "ada@contoso.com"
Office365Users.MyProfile()	Full profile of the current user	Office365Users.MyProfile().Department
Office365Users.UserProfile(id)	Profile of another user	…UserProfile("sam@contoso.com").JobTitle
Office365Users.Manager(id)	That user’s manager	Office365Users.Manager(User().Email).DisplayName
Office365Users.DirectReports(id)	A user’s reports (table)	Office365Users.DirectReports(mgr)
Office365Users.SearchUserV2({…}).value	People-picker search results	Office365Users.SearchUserV2({searchTerm: txt.Text, top: 5}).value
Office365Groups.ListGroupMembers(id).value	Members of a group	Office365Groups.ListGroupMembers(gblGroupId).value

Patch a SharePoint Person column

powerfx

Patch(
    Projects, Defaults(Projects),
    {
        Title: txtTitle.Text,
        AssignedTo: {
            '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
            Claims: "i:0#.f|membership|" & cmbPerson.Selected.Mail,
            DisplayName: cmbPerson.Selected.DisplayName,
            Email: cmbPerson.Selected.Mail
        }
    }
)

Person/group columns need a claims record — build it from the combo box selection.

Is the current user in a group?

powerfx

User().Email in Office365Groups.ListGroupMembers(gblAdminGroupId).value.mail

User() vs the Office 365 Users connector

User() is built in and free but only exposes Email, FullName, and Image. For job title, department, manager, or photos you need the Office 365 Users standard connector. Cache MyProfile() in a named formula or variable — never call it per gallery row.

Math & numbers

9/9

Function	What it does	Example → result
Sum / Average	Aggregate a column	Sum(Orders, Total)
Min / Max	Smallest / largest	Max(Orders, Total)
Round / RoundUp / RoundDown	Round to N digits	Round(3.14159, 2) → 3.14
Int / Trunc	Drop the fraction	Trunc(3.9) → 3
Mod	Remainder	Mod(10, 3) → 1
Abs	Absolute value	Abs(-5) → 5
Sqrt / Power	Root / exponent	Power(2, 10) → 1024
RandBetween	Random integer in range	RandBetween(1, 6)
CountRows / CountIf	Count rows	CountIf(Orders, Total > 100)

Date & time

7/7

Function	What it does	Example
Now / Today / UTCNow	Current date-time	Today()
DateAdd	Shift by interval	DateAdd(Today(), 7, Days)
DateDiff	Difference between dates	DateDiff(Start, End, Days)
DateValue / DateTimeValue	Parse string to date	DateValue("2026-06-15")
Text (format)	Format a date	Text(Now(), "yyyy-mm-dd hh:mm")
Weekday / Hour / Minute	Extract parts	Weekday(Today())
IsToday / IsUTCToday	Date comparison helper	Filter(T, IsToday(Created))

Format strings are case-sensitive

In Text(), mm is minutes and MM/mmmm are month. Use lowercase dd for day and yyyy for year. Times are local unless you work in UTCNow().

Navigation & behavior

9/9

Function	What it does	Example
Navigate	Go to a screen with a transition	Navigate(Home, ScreenTransition.Fade)
Back	Return to the previous screen	Back()
Notify	Show a banner message	Notify("Saved", NotificationType.Success)
Reset / ResetForm	Reset a control / form	Reset(txtSearch)
SetFocus	Move keyboard focus	SetFocus(txtEmail)
Launch	Open a URL, app, or deep link	Launch("https://flowlibs.com")
Select	Run another control’s OnSelect	Select(btnSave)
Concurrent	Run formulas in parallel	Concurrent(ClearCollect(a, A), ClearCollect(b, B))
Trace	Log to Monitor / App Insights	Trace("checkout started")

Speed up OnStart with Concurrent

Wrap independent data loads in Concurrent(...) so they run in parallel instead of one after another — often the single biggest win for app launch time.

Validation & conversion

8/8

Function	What it does	Example
Value	Parse text to a number	Value("1234") → 1234
Text	Format a number / date	Text(0.25, "0%") → "25%"
IsNumeric	Is the value a number?	IsNumeric(txtQty.Text)
IsBlank / IsEmpty	Null / empty-table test	IsBlank(txtEmail.Text)
IsMatch	Test text against a pattern	IsMatch(txtEmail.Text, Match.Email)
Match / MatchAll	Extract via regex	Match("Order 42", "[0-9]+").FullMatch
GUID	New / parse a GUID	GUID()
Boolean	Parse a boolean string	Boolean("true")

Built-in Match patterns

Prefer the Match enum over hand-written regex where you can: Match.Email, Match.Hyperlink, Match.MultipleDigits, Match.PhoneNumber. Add MatchOptions.Contains for partial matches.

JSON & untyped objects

6/6

Function	What it does	Example
ParseJSON	Text to an untyped object	ParseJSON(txtJson.Text)
Value (coerce)	Read a number field	Value(ParseJSON(x).price)
Text (coerce)	Read a text field	Text(ParseJSON(x).name)
JSON	Object / table to JSON text	JSON(colCart, JSONFormat.IndentFour)
Index	Nth row of a table	Index(Filter(T, Active), 1)
Table / Record	Build inline data	Table({Id: 1}, {Id: 2})

ParseJSON returns Untyped

ParseJSON gives an *untyped object* — coerce each field explicitly with Text(), Value(), DateValue(), or Boolean() before use, and index arrays with Index(obj, n).

UI & theming

6/6

Function / property	What it does	Example
RGBA	Color from channels (0–255, 0–1 alpha)	RGBA(0, 120, 212, 1)
ColorValue	Color from hex / CSS name	ColorValue("#0078D4")
ColorFade	Lighten (+) or darken (−) a color	ColorFade(Brand, -20%)
Color.Name	Named color enum	Color.White
Self	The current control	ColorFade(Self.Fill, -10%)
Parent	Containing control / screen	Parent.Width

Theme tokens as named formulas

powerfx

Brand = RGBA(0, 120, 212, 1);
BrandDark = ColorFade(Brand, -25%);
Surface = RGBA(250, 250, 252, 1);
TextMuted = RGBA(96, 94, 92, 1);
SpaceM = 16;

App.Formulas — one source of truth for the palette and spacing.

Hover / pressed states with Self

powerfx

// Button Fill
If(Self.IsHovered || Self.IsPressed, BrandDark, Brand)

Name your theme once, reference everywhere

Define colors and spacing in App.Formulas, then bind control properties to the tokens — re-skinning the app becomes a one-line change, and named formulas recompute lazily so there’s no OnStart cost. For responsive layouts, prefer containers and Parent.Width over hard-coded X/Y.

Offline & storage

6/6

Function / signal	What it does	Example
SaveData	Persist a collection to the device	SaveData(colCart, "cart")
LoadData	Reload a saved collection	LoadData(colCart, "cart", true)
ClearData	Erase saved storage	ClearData("cart")
Connection.Connected	Is the device online?	If(Connection.Connected, …)
Connection.Metered	On a metered network?	If(!Connection.Metered, Sync())
Concurrent	Run loads in parallel	Concurrent(LoadData(a,"a"), LoadData(b,"b"))

Online-or-cache startup

powerfx

If( Connection.Connected,
    ClearCollect(colTasks, Tasks); SaveData(colTasks, "tasks"),
    LoadData(colTasks, "tasks", true)
)

App.OnStart — fetch live and cache when online, fall back to the cache when offline.

Parallel cache load

powerfx

Concurrent(
    LoadData(colTasks, "tasks", true),
    LoadData(colPrefs, "prefs", true)
)

SaveData has hard limits

SaveData is capped at 1 MB in Teams and the web player (mobile gets a device-dependent 30–70 MB), it can’t run in Studio’s browser preview, and LoadData *appends* — Clear the collection first if you want a replace. Web storage is plain text, so never cache secrets. For large or relational Dataverse data, use built-in mobile offline instead.

Components & reuse

Canvas components are reusable control groups with their own custom properties — the canvas equivalent of a function or a UI component. Put shared ones in a component library and import them per app.

5/5

Custom property	Direction	Typical use
Input (data)	Host → component	Pass the items, theme color, or title in
Output (data)	Component → host	Expose the selected item / computed value
Input (function)	Host → component	Inject a formula the component calls
Output (function)	Component → host	Let the host supply a callback
Behavior (event)	Component → host	Raise OnChange / OnSelect-style events

Read an input, raise an event

powerfx

// Inside the component — a label's Text
cmpHeader.Title          // Input (data) custom property

// The component raises its event from a button OnSelect
cmpHeader.OnAction()     // Behavior custom property

// On the instance in the host app — OnAction:
Navigate(DetailScreen)

Components don’t see app scope

A canvas component can’t read screen controls or globals directly — everything it needs comes in through input custom properties, and everything it returns goes out through output properties. That isolation is exactly what makes it reusable across screens and apps.

Formula style guide

Consistent naming and formatting make formulas reviewable. FlowLibs house style follows the Microsoft canvas coding standards: type-prefixed control names, lifted named formulas, and comments that explain the *why*.

8/8

Prefix	Control / object	Example
btn	Button	btnSave
txt	Text input	txtEmail
lbl	Label	lblTitle
gal	Gallery	galTasks
frm	Edit form	frmTask
cmb / ddl	Combo box / dropdown	cmbOwner
col	Collection	colCart
gbl / loc	Global / context variable	gblUser / locIsOpen

Comments

powerfx

// line comment — explain the why, not the what
/* block comment
   spanning several lines */
Set(gblUser, User().Email)   // trailing comment

Format for readability

powerfx

Filter(
    Tasks,
    Status = "Open" &&
    AssignedTo.Email = User().Email
)

One argument per line, indented, operators trailing.

Name formulas and screens too

Prefix controls by type, suffix screens (scrHome), and lift repeated expressions into named formulas or With(). Keep the App checker on, and keep a formula under a screenful — if it needs scrolling, it probably wants a named formula or component.

Common patterns

Real formulas that combine several functions — each is a single expression you can drop straight into a property and adapt.

Search, filter, and sort a gallery

powerfx

SortByColumns(
  Filter(
    Search(Tasks, txtSearch.Text, "Title", "Owner"),
    Status = ddStatus.Selected.Value
  ),
  "DueDate", SortOrder.Ascending
)

Layer Search → Filter → SortByColumns on a gallery’s Items.

Cascading dropdown (distinct child values)

powerfx

Distinct(
  Filter(Cities, Country = ddCountry.Selected.Value),
  Name
)

Block submit on a duplicate

powerfx

If(
  CountRows(Filter(Members, Lower(Email) = Lower(txtEmail.Text))) > 0,
  Notify("That email already exists", NotificationType.Error),
  Patch(Members, Defaults(Members), { Email: txtEmail.Text });
  Notify("Added", NotificationType.Success)
)

Display name with fallback

powerfx

Coalesce(
  User().FullName,
  Proper(Left(User().Email, Find("@", User().Email) - 1)),
  "Guest"
)

Age in whole years from a date of birth

powerfx

RoundDown(DateDiff(dpDOB.SelectedDate, Today(), Days) / 365.25, 0)

Running total for the current row’s order

powerfx

Sum(Filter(LineItems, OrderId = ThisItem.OrderId), Amount)

Join selected items into a string

powerfx

Concat(Filter(Products, Selected), Name, ", ")

Toggle an item in a selection collection

powerfx

If(
  ThisItem.ID in colSelected.ID,
  Remove(colSelected, LookUp(colSelected, ID = ThisItem.ID)),
  Collect(colSelected, ThisItem)
)

Gallery item OnSelect — add if missing, remove if present.

Currency with a blank placeholder

powerfx

If(IsBlank(ThisItem.Price), "—", Text(ThisItem.Price, "[$-en-US]$#,##0.00"))

Start of this week (Monday)

powerfx

DateAdd(Today(), -(Weekday(Today(), StartOfWeek.Monday) - 1), Days)

Enable Submit only when the form is valid

powerfx

And(
  !IsBlank(txtName.Text),
  IsMatch(txtEmail.Text, Match.Email),
  Value(txtQty.Text) > 0
)

Use as the button’s DisplayMode: If(<this>, DisplayMode.Edit, DisplayMode.Disabled).

Three-level cascading dropdown

powerfx

// ddRegion.Items
Distinct(Locations, Region)

// ddCountry.Items
Distinct(Filter(Locations, Region = ddRegion.Selected.Value), Country)

// ddCity.Items
Distinct(
  Filter(Locations,
    Region = ddRegion.Selected.Value &&
    Country = ddCountry.Selected.Value),
  City
)

Each level filters by every level above it; clear children in the parent’s OnChange.

Save every edited gallery row at once

powerfx

Patch(
  Tasks,
  ForAll(galTasks.AllItems As row,
    {
      Task: row.Task,
      Status: row.cmbStatus.Selected.Value
    }
  )
)

Patch a table built from the gallery’s AllItems — one call, one round-trip.

Sum a related child table for the current row

powerfx

// SharePoint / SQL — filter the child table
Sum(Filter(OrderLines, OrderId = ThisItem.ID), Quantity * UnitPrice)

// Dataverse — walk the 1:N relationship directly
Sum(ThisItem.OrderLines, Quantity * UnitPrice)

Debounced search (wait for typing to stop)

powerfx

// txtSearch.OnChange — (re)start the timer
UpdateContext({ locStart: false }); UpdateContext({ locStart: true })

// tmrDebounce: Start = locStart, Duration = 400, Repeat = false
// tmrDebounce.OnTimerEnd
UpdateContext({ locSearch: txtSearch.Text, locStart: false })

// galResults.Items
Filter(Customers, StartsWith(Name, locSearch))

A short timer holds off the Filter until the user pauses — kinder to delegation.

Role-based visibility

powerfx

// App.Formulas — resolve the role once
IsAdmin = User().Email in colAdmins.Email;

// control Visible
IsAdmin

// or DisplayMode
If(IsAdmin, DisplayMode.Edit, DisplayMode.View)

Star rating (read + set)

powerfx

// star icon Color
If(ThisItem.Value <= gblRating, Color.Gold, Color.Gray)

// star icon OnSelect
Set(gblRating, ThisItem.Value)

A star icon inside a Sequence(5) gallery — ThisItem.Value is 1..5.

Multi-select ↔ delimited string

powerfx

// selected combo items → "a; b; c" for a text column
Concat(cmbTags.SelectedItems, Value, "; ")

// stored string → table for DefaultSelectedItems
ForAll(Split(ThisItem.Tags, ";") As t, { Value: Trim(t.Value) })

Client-side pagination

powerfx

FirstN(
  LastN(
    Sort(Tasks, Created, SortOrder.Descending),
    CountRows(Tasks) - (locPage - 1) * 20
  ),
  20
)

Page locPage (1-based), 20 rows per page — next: UpdateContext({locPage: locPage + 1}).

Result count with a no-results message

powerfx

With(
  { n: CountRows(galResults.AllItems) },
  If(n = 0, "No matches", $"{n} result(s)")
)

Delegation & performance

Delegation decides whether filtering/sorting happens at the data source (scales to millions of rows) or on the device (capped at the delegation limit, default 500, max 2,000). A blue underline / warning in the editor means a formula is *not* delegable and will silently truncate to that limit.

Operation	Dataverse	SharePoint	SQL Server
= , <> (equals / not equals)	Yes	Yes	Yes
< > <= >= (compare)	Yes	Yes	Yes
StartsWith	Yes	Yes	Yes
EndsWith	Yes	No	Yes
Search() (substring)	Partial	No	Partial
in / exactin	Partial	No	Partial
Sort / SortByColumns	Yes	Yes	Yes
Sum / Average / Min / Max	Yes	No	Yes
CountRows / CountIf	Yes	No	Partial
Filter on a calculated value	No	No	No
GroupBy / Distinct	No	No	No

Delegation support varies by connector and column type — always verify against your own source.

Functions that never delegate

First / FirstN / Last / LastN, and GroupBy / Ungroup / Distinct on every connector.
Collection writers: Collect, ClearCollect, Concat, Concatenate, and AddColumns over a data source.
Any text or date transform *inside the predicate* — Left, Right, Mid, Len, Lower/Upper, &, and date math push the comparison to the device.
Sum / Average / CountRows on SharePoint, and Search on SharePoint columns.

Workarounds

Compare the raw column, not a transformed one: Filter(Orders, Status = "Open") delegates; Filter(Orders, Lower(Status) = "open") does not. Store a normalised column if you need case-insensitive matching.
Pre-filter at the source with a delegable predicate, then do the non-delegable shaping in memory on the (≤ 2,000-row) result.
Cache small, stable reference tables with ClearCollect in OnStart; never ClearCollect a large transactional table.
Replace per-keystroke Filter in a gallery with a debounced timer or a search button (see the recipe above).
For counts/sums on SharePoint, keep a roll-up/calculated column or move the data to Dataverse or SQL.

Tip

Every cheat table above is searchable

Use the filter box on each table to jump to a function fast. This whole guide is generated from one structured dataset, so it stays in sync with the MCP reference.

Spotted an error or have a suggestion? Let us know

Power Fx Cheat Sheet

Power Fx30 min readUpdated 2026-06-16

Power AppsPower FxCanvas AppsVariablesPatternsDelegationError HandlingOffice 365 UsersThemingComponentsFormulas

Fundamentals

Where it runs: canvas apps, model-driven custom pages, cards for Teams/Outlook, and Copilot Studio topics.
Named formulas (App.Formulas) define reusable values that recompute automatically — prefer them over OnStart variable soup.
`=` vs `:=` — comparisons use =; record/scope assignment in functions like Patch/With uses :.
Operators: & concatenates, &&/And, ||/Or, !/Not, in / exactin for membership.

Use named formulas, not OnStart

Text functions

12/12

Function	What it does	Example → result
Concatenate / &	Join strings	"Hi " & First.Name → "Hi Ada"
Left / Right / Mid	Substring by position	Mid("FlowLibs",1,4) → "Flow"
Len	Length of a string	Len("abc") → 3
Lower / Upper / Proper	Change case	Proper("ada lovelace") → "Ada Lovelace"
Trim / TrimEnds	Remove extra spaces	Trim(" a b ") → "a b"
Substitute	Replace by match (all)	Substitute("a-b-c","-","/") → "a/b/c"
Replace	Replace by position	Replace("12345",2,2,"X") → "1X45"
Find	Position of substring (1-based)	Find("@","a@b") → 2
StartsWith / EndsWith	Prefix / suffix test	EndsWith("report.pdf",".pdf") → true
Text	Format number/date to string	Text(1234.5,"$#,##0.00") → "$1,234.50"
Value	Parse string to number	Value("42") → 42
Split / Concat	String ↔ table of values	Split("a,b,c",",") → 3-row table

Logic & conditionals

7/7

Function	What it does	Example
If	Branch on condition(s)	If(Score >= 50, "Pass", "Fail")
Switch	Match a value to results	Switch(Status,"New",1,"Done",3,0)
Coalesce	First non-blank value	Coalesce(Nickname, FullName, "Guest")
IsBlank / IsEmpty	Null / empty-table test	If(IsBlank(Email), Notify("Required"))
IsError / IfError	Catch + replace errors	IfError(1/Qty, 0)
And / Or / Not	Boolean composition	And(IsToday(Due), !Done)
in / exactin	Membership (case-insen / -sen)	"sql" in "Azure SQL" → true

IfError beats IsError

Wrap risky expressions with IfError(value, fallback) so a single bad row or divide-by-zero does not blank the whole control. Use IfError(value, fallback, afterError) when you also need to log.

Error handling & signals

7/7

Function	What it does	Example
IfError / IsError	Catch + replace, or test	IfError(Patch(DS,…), Notify("failed"))
IsBlankOrError	Blank or error in one test	IsBlankOrError(LookUp(U, Id = x))
Error	Raise or rethrow a custom error	Error({Kind: ErrorKind.Validation, Message: "Bad input"})
Errors(source)	Errors from the last data op	Errors(Tasks)
FirstError / AllErrors	Error context in a handler	Notify(FirstError.Message)
Notify	Banner message (≤ 500 chars)	Notify("Saved", NotificationType.Success)
Trace	Telemetry to Monitor / App Insights	Trace("checkout", TraceSeverity.Information)

Try / catch a save

powerfx

IfError(
    Patch(Orders, Defaults(Orders), { Total: Value(txtTotal.Text) }),
    Notify($"Could not save: {FirstError.Message}", NotificationType.Error);
    Trace("save failed", TraceSeverity.Error, { detail: FirstError.Message })
)

IfError stops the chain on failure and gives you FirstError for the message + log.

Form OnSuccess / OnFailure

powerfx

// Submit button — OnSelect
SubmitForm(frmEdit)

// Form — OnSuccess
Notify("Saved", NotificationType.Success); Back()

// Form — OnFailure
Notify(frmEdit.Error, NotificationType.Error)

Let the form raise the events — don’t hand-roll success/failure in the button.

App.OnError is report-only

Tables & collections

11/11

Function	What it does	Example
Filter	Rows matching a condition	Filter(Tasks, Status = "Open")
Search	Substring search across columns	Search(Tasks, txtFind.Text, "Title")
LookUp	First matching row (or a field)	LookUp(Users, Id = 7).Email
Sort / SortByColumns	Order a table	Sort(Tasks, Due, Ascending)
FirstN / LastN / First / Last	Take rows	FirstN(Sort(T, Date, Descending), 5)
AddColumns / DropColumns	Reshape a table	AddColumns(T, "Year", Year(Date))
CountRows / CountIf	Count	CountIf(Tasks, Status = "Open")
Distinct / GroupBy	Unique values / aggregate	Distinct(Tasks, Owner)
ForAll	Map a formula over rows	ForAll(Cart, Patch(...))
Collect / ClearCollect / Patch	Write to a collection / source	ClearCollect(colCache, Source)
With	Local named scope	With({r: LookUp(U, Id=x)}, r.Name)

Patch a record (create or update)

powerfx

Patch(
    Tasks,
    Coalesce(LookUp(Tasks, Id = selectedId), Defaults(Tasks)),
    {
        Title: txtTitle.Text,
        Status: ddStatus.Selected.Value,
        DueDate: dpDue.SelectedDate
    }
)

Create when ID is blank, otherwise update — the defaulted-record pattern.

Collection shaping

These functions return a *new* shaped table — they never mutate the source. Reach for them to trim columns before caching, build lookup tables, or roll child rows up onto a parent.

8/8

Function	What it does	Example
AddColumns	Append computed columns	AddColumns(Cart, "Line", Qty * Price)
DropColumns	Remove columns	DropColumns(Cart, "Internal")
RenameColumns	Rename columns	RenameColumns(Data, "old", "new")
ShowColumns	Keep only these columns	ShowColumns(Users, "Id", "Mail")
GroupBy	Group rows into a nested table	GroupBy(Sales, "Region", "ByRegion")
Ungroup	Flatten a grouped table	Ungroup(grp, "ByRegion")
ForAll	Project a new table per row	ForAll(Sequence(5), { n: Value })
Sequence	Generate N rows (1..N)	Sequence(7) → 1..7

Roll a child total onto each group

powerfx

AddColumns(
    GroupBy(LineItems, "OrderId", "Lines"),
    "OrderTotal", Sum(Lines, Amount)
)

Build a table from a delimited string

powerfx

ForAll(
    Split(txtCsv.Text, ",") As line,
    { Value: Trim(line.Value) }
)

ForAll returns a table — it is not a loop

Records, scope & references

6/6

Keyword	What it refers to	Example
ThisItem	Current row in a gallery / form	ThisItem.Title
ThisRecord	Current record in ForAll / Filter / Sum	ForAll(Cart, ThisRecord.Qty * ThisRecord.Price)
Self	The control the formula is on	Self.Fill
Parent	The containing control / screen	Parent.TemplateHeight
As	Name a record scope to avoid ambiguity	ForAll(Orders As o, o.Total)
With	Local named values for one formula	With({r: LookUp(U, Id = x)}, r.Name)

Use As to disambiguate nested scopes

When you nest ForAll/Filter/Sum, the inner ThisRecord shadows the outer one. Name each scope with As (e.g. ForAll(Orders As o, ForAll(o.Lines As l, ...))) so you can reach both.

Variables & state

7/7

Function	Scope	Example
Set	Global variable (whole app)	Set(gblUser, User().Email)
UpdateContext	Context variable (one screen)	UpdateContext({locIsOpen: true})
Collect / ClearCollect	Collection (in-memory table)	ClearCollect(colCart, Source)
Clear	Empty a collection	Clear(colCart)
Patch	Create / update a record	Patch(Tasks, Defaults(Tasks), {Title: "New"})
Remove / RemoveIf	Delete rows	RemoveIf(Tasks, Status = "Done")
UpdateIf	Bulk-update rows	UpdateIf(Tasks, Status = "New", {Status: "Open"})

Set vs UpdateContext vs Collect

Choices & option sets

7/7

Function / property	What it does	Example
Choices(source.Column)	Valid values for a choice / lookup column	Choices(Tasks.Status)
Defaults(source)	Blank record with column defaults	Defaults(Tasks)
ddl.Selected.Value	Picked value (single-select)	ddStatus.Selected.Value
cmb.SelectedItems	Picked rows (multi-select)	cmbTags.SelectedItems
DefaultSelectedItems	Preselect a combo box	Filter(Choices(Projects.Tags), …)
IsBlank(.Selected)	Nothing picked yet?	IsBlank(ddStatus.Selected)
Distinct(source, Col)	Unique values when there is no choice column	Distinct(Tasks, Owner)

Patch single + multi choices

powerfx

Patch(
    Tasks,
    Coalesce(LookUp(Tasks, Task = selectedId), Defaults(Tasks)),
    {
        Status: ddStatus.Selected,        // single choice → the record
        Tags: cmbTags.SelectedItems       // choices (multi) → a table
    }
)

A single choice takes the option-set record; a multi-choice takes a table of records.

Preselect a combo box on an edit form

powerfx

Filter(
    Choices(Projects.Tags),
    Value in ThisItem.Tags.Value
)

ComboBox DefaultSelectedItems — match the row’s current values back to the choice set.

Choices keeps the dropdown honest

Users & people

7/7

Function	What it returns	Example
User()	Signed-in user (built-in, no connector)	User().Email → "ada@contoso.com"
Office365Users.MyProfile()	Full profile of the current user	Office365Users.MyProfile().Department
Office365Users.UserProfile(id)	Profile of another user	…UserProfile("sam@contoso.com").JobTitle
Office365Users.Manager(id)	That user’s manager	Office365Users.Manager(User().Email).DisplayName
Office365Users.DirectReports(id)	A user’s reports (table)	Office365Users.DirectReports(mgr)
Office365Users.SearchUserV2({…}).value	People-picker search results	Office365Users.SearchUserV2({searchTerm: txt.Text, top: 5}).value
Office365Groups.ListGroupMembers(id).value	Members of a group	Office365Groups.ListGroupMembers(gblGroupId).value

Patch a SharePoint Person column

powerfx

Patch(
    Projects, Defaults(Projects),
    {
        Title: txtTitle.Text,
        AssignedTo: {
            '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
            Claims: "i:0#.f|membership|" & cmbPerson.Selected.Mail,
            DisplayName: cmbPerson.Selected.DisplayName,
            Email: cmbPerson.Selected.Mail
        }
    }
)

Person/group columns need a claims record — build it from the combo box selection.

Is the current user in a group?

powerfx

User().Email in Office365Groups.ListGroupMembers(gblAdminGroupId).value.mail

User() vs the Office 365 Users connector

Math & numbers

9/9

Function	What it does	Example → result
Sum / Average	Aggregate a column	Sum(Orders, Total)
Min / Max	Smallest / largest	Max(Orders, Total)
Round / RoundUp / RoundDown	Round to N digits	Round(3.14159, 2) → 3.14
Int / Trunc	Drop the fraction	Trunc(3.9) → 3
Mod	Remainder	Mod(10, 3) → 1
Abs	Absolute value	Abs(-5) → 5
Sqrt / Power	Root / exponent	Power(2, 10) → 1024
RandBetween	Random integer in range	RandBetween(1, 6)
CountRows / CountIf	Count rows	CountIf(Orders, Total > 100)

Date & time

7/7

Function	What it does	Example
Now / Today / UTCNow	Current date-time	Today()
DateAdd	Shift by interval	DateAdd(Today(), 7, Days)
DateDiff	Difference between dates	DateDiff(Start, End, Days)
DateValue / DateTimeValue	Parse string to date	DateValue("2026-06-15")
Text (format)	Format a date	Text(Now(), "yyyy-mm-dd hh:mm")
Weekday / Hour / Minute	Extract parts	Weekday(Today())
IsToday / IsUTCToday	Date comparison helper	Filter(T, IsToday(Created))

Format strings are case-sensitive

In Text(), mm is minutes and MM/mmmm are month. Use lowercase dd for day and yyyy for year. Times are local unless you work in UTCNow().

Navigation & behavior

9/9

Function	What it does	Example
Navigate	Go to a screen with a transition	Navigate(Home, ScreenTransition.Fade)
Back	Return to the previous screen	Back()
Notify	Show a banner message	Notify("Saved", NotificationType.Success)
Reset / ResetForm	Reset a control / form	Reset(txtSearch)
SetFocus	Move keyboard focus	SetFocus(txtEmail)
Launch	Open a URL, app, or deep link	Launch("https://flowlibs.com")
Select	Run another control’s OnSelect	Select(btnSave)
Concurrent	Run formulas in parallel	Concurrent(ClearCollect(a, A), ClearCollect(b, B))
Trace	Log to Monitor / App Insights	Trace("checkout started")

Speed up OnStart with Concurrent

Wrap independent data loads in Concurrent(...) so they run in parallel instead of one after another — often the single biggest win for app launch time.

Validation & conversion

8/8

Function	What it does	Example
Value	Parse text to a number	Value("1234") → 1234
Text	Format a number / date	Text(0.25, "0%") → "25%"
IsNumeric	Is the value a number?	IsNumeric(txtQty.Text)
IsBlank / IsEmpty	Null / empty-table test	IsBlank(txtEmail.Text)
IsMatch	Test text against a pattern	IsMatch(txtEmail.Text, Match.Email)
Match / MatchAll	Extract via regex	Match("Order 42", "[0-9]+").FullMatch
GUID	New / parse a GUID	GUID()
Boolean	Parse a boolean string	Boolean("true")

Built-in Match patterns

Prefer the Match enum over hand-written regex where you can: Match.Email, Match.Hyperlink, Match.MultipleDigits, Match.PhoneNumber. Add MatchOptions.Contains for partial matches.

JSON & untyped objects

6/6

Function	What it does	Example
ParseJSON	Text to an untyped object	ParseJSON(txtJson.Text)
Value (coerce)	Read a number field	Value(ParseJSON(x).price)
Text (coerce)	Read a text field	Text(ParseJSON(x).name)
JSON	Object / table to JSON text	JSON(colCart, JSONFormat.IndentFour)
Index	Nth row of a table	Index(Filter(T, Active), 1)
Table / Record	Build inline data	Table({Id: 1}, {Id: 2})

ParseJSON returns Untyped

ParseJSON gives an *untyped object* — coerce each field explicitly with Text(), Value(), DateValue(), or Boolean() before use, and index arrays with Index(obj, n).

UI & theming

6/6

Function / property	What it does	Example
RGBA	Color from channels (0–255, 0–1 alpha)	RGBA(0, 120, 212, 1)
ColorValue	Color from hex / CSS name	ColorValue("#0078D4")
ColorFade	Lighten (+) or darken (−) a color	ColorFade(Brand, -20%)
Color.Name	Named color enum	Color.White
Self	The current control	ColorFade(Self.Fill, -10%)
Parent	Containing control / screen	Parent.Width

Theme tokens as named formulas

powerfx

Brand = RGBA(0, 120, 212, 1);
BrandDark = ColorFade(Brand, -25%);
Surface = RGBA(250, 250, 252, 1);
TextMuted = RGBA(96, 94, 92, 1);
SpaceM = 16;

App.Formulas — one source of truth for the palette and spacing.

Hover / pressed states with Self

powerfx

// Button Fill
If(Self.IsHovered || Self.IsPressed, BrandDark, Brand)

Name your theme once, reference everywhere

Offline & storage

6/6

Function / signal	What it does	Example
SaveData	Persist a collection to the device	SaveData(colCart, "cart")
LoadData	Reload a saved collection	LoadData(colCart, "cart", true)
ClearData	Erase saved storage	ClearData("cart")
Connection.Connected	Is the device online?	If(Connection.Connected, …)
Connection.Metered	On a metered network?	If(!Connection.Metered, Sync())
Concurrent	Run loads in parallel	Concurrent(LoadData(a,"a"), LoadData(b,"b"))

Online-or-cache startup

powerfx

If( Connection.Connected,
    ClearCollect(colTasks, Tasks); SaveData(colTasks, "tasks"),
    LoadData(colTasks, "tasks", true)
)

App.OnStart — fetch live and cache when online, fall back to the cache when offline.

Parallel cache load

powerfx

Concurrent(
    LoadData(colTasks, "tasks", true),
    LoadData(colPrefs, "prefs", true)
)

SaveData has hard limits

Components & reuse

5/5

Custom property	Direction	Typical use
Input (data)	Host → component	Pass the items, theme color, or title in
Output (data)	Component → host	Expose the selected item / computed value
Input (function)	Host → component	Inject a formula the component calls
Output (function)	Component → host	Let the host supply a callback
Behavior (event)	Component → host	Raise OnChange / OnSelect-style events

Read an input, raise an event

powerfx

// Inside the component — a label's Text
cmpHeader.Title          // Input (data) custom property

// The component raises its event from a button OnSelect
cmpHeader.OnAction()     // Behavior custom property

// On the instance in the host app — OnAction:
Navigate(DetailScreen)

Components don’t see app scope

Formula style guide

8/8

Prefix	Control / object	Example
btn	Button	btnSave
txt	Text input	txtEmail
lbl	Label	lblTitle
gal	Gallery	galTasks
frm	Edit form	frmTask
cmb / ddl	Combo box / dropdown	cmbOwner
col	Collection	colCart
gbl / loc	Global / context variable	gblUser / locIsOpen

Comments

powerfx

// line comment — explain the why, not the what
/* block comment
   spanning several lines */
Set(gblUser, User().Email)   // trailing comment

Format for readability

powerfx

Filter(
    Tasks,
    Status = "Open" &&
    AssignedTo.Email = User().Email
)

One argument per line, indented, operators trailing.

Name formulas and screens too

Common patterns

Real formulas that combine several functions — each is a single expression you can drop straight into a property and adapt.

Search, filter, and sort a gallery

powerfx

SortByColumns(
  Filter(
    Search(Tasks, txtSearch.Text, "Title", "Owner"),
    Status = ddStatus.Selected.Value
  ),
  "DueDate", SortOrder.Ascending
)

Layer Search → Filter → SortByColumns on a gallery’s Items.

Cascading dropdown (distinct child values)

powerfx

Distinct(
  Filter(Cities, Country = ddCountry.Selected.Value),
  Name
)

Block submit on a duplicate

powerfx

If(
  CountRows(Filter(Members, Lower(Email) = Lower(txtEmail.Text))) > 0,
  Notify("That email already exists", NotificationType.Error),
  Patch(Members, Defaults(Members), { Email: txtEmail.Text });
  Notify("Added", NotificationType.Success)
)

Display name with fallback

powerfx

Coalesce(
  User().FullName,
  Proper(Left(User().Email, Find("@", User().Email) - 1)),
  "Guest"
)

Age in whole years from a date of birth

powerfx

RoundDown(DateDiff(dpDOB.SelectedDate, Today(), Days) / 365.25, 0)

Running total for the current row’s order

powerfx

Sum(Filter(LineItems, OrderId = ThisItem.OrderId), Amount)

Join selected items into a string

powerfx

Concat(Filter(Products, Selected), Name, ", ")

Toggle an item in a selection collection

powerfx

If(
  ThisItem.ID in colSelected.ID,
  Remove(colSelected, LookUp(colSelected, ID = ThisItem.ID)),
  Collect(colSelected, ThisItem)
)

Gallery item OnSelect — add if missing, remove if present.

Currency with a blank placeholder

powerfx

If(IsBlank(ThisItem.Price), "—", Text(ThisItem.Price, "[$-en-US]$#,##0.00"))

Start of this week (Monday)

powerfx

DateAdd(Today(), -(Weekday(Today(), StartOfWeek.Monday) - 1), Days)

Enable Submit only when the form is valid

powerfx

And(
  !IsBlank(txtName.Text),
  IsMatch(txtEmail.Text, Match.Email),
  Value(txtQty.Text) > 0
)

Use as the button’s DisplayMode: If(<this>, DisplayMode.Edit, DisplayMode.Disabled).

Three-level cascading dropdown

powerfx

// ddRegion.Items
Distinct(Locations, Region)

// ddCountry.Items
Distinct(Filter(Locations, Region = ddRegion.Selected.Value), Country)

// ddCity.Items
Distinct(
  Filter(Locations,
    Region = ddRegion.Selected.Value &&
    Country = ddCountry.Selected.Value),
  City
)

Each level filters by every level above it; clear children in the parent’s OnChange.

Save every edited gallery row at once

powerfx

Patch(
  Tasks,
  ForAll(galTasks.AllItems As row,
    {
      Task: row.Task,
      Status: row.cmbStatus.Selected.Value
    }
  )
)

Patch a table built from the gallery’s AllItems — one call, one round-trip.

Sum a related child table for the current row

powerfx

// SharePoint / SQL — filter the child table
Sum(Filter(OrderLines, OrderId = ThisItem.ID), Quantity * UnitPrice)

// Dataverse — walk the 1:N relationship directly
Sum(ThisItem.OrderLines, Quantity * UnitPrice)

Debounced search (wait for typing to stop)

powerfx

// txtSearch.OnChange — (re)start the timer
UpdateContext({ locStart: false }); UpdateContext({ locStart: true })

// tmrDebounce: Start = locStart, Duration = 400, Repeat = false
// tmrDebounce.OnTimerEnd
UpdateContext({ locSearch: txtSearch.Text, locStart: false })

// galResults.Items
Filter(Customers, StartsWith(Name, locSearch))

A short timer holds off the Filter until the user pauses — kinder to delegation.

Role-based visibility

powerfx

// App.Formulas — resolve the role once
IsAdmin = User().Email in colAdmins.Email;

// control Visible
IsAdmin

// or DisplayMode
If(IsAdmin, DisplayMode.Edit, DisplayMode.View)

Star rating (read + set)

powerfx

// star icon Color
If(ThisItem.Value <= gblRating, Color.Gold, Color.Gray)

// star icon OnSelect
Set(gblRating, ThisItem.Value)

A star icon inside a Sequence(5) gallery — ThisItem.Value is 1..5.

Multi-select ↔ delimited string

powerfx

// selected combo items → "a; b; c" for a text column
Concat(cmbTags.SelectedItems, Value, "; ")

// stored string → table for DefaultSelectedItems
ForAll(Split(ThisItem.Tags, ";") As t, { Value: Trim(t.Value) })

Client-side pagination

powerfx

FirstN(
  LastN(
    Sort(Tasks, Created, SortOrder.Descending),
    CountRows(Tasks) - (locPage - 1) * 20
  ),
  20
)

Page locPage (1-based), 20 rows per page — next: UpdateContext({locPage: locPage + 1}).

Result count with a no-results message

powerfx

With(
  { n: CountRows(galResults.AllItems) },
  If(n = 0, "No matches", $"{n} result(s)")
)

Delegation & performance

Operation	Dataverse	SharePoint	SQL Server
= , <> (equals / not equals)	Yes	Yes	Yes
< > <= >= (compare)	Yes	Yes	Yes
StartsWith	Yes	Yes	Yes
EndsWith	Yes	No	Yes
Search() (substring)	Partial	No	Partial
in / exactin	Partial	No	Partial
Sort / SortByColumns	Yes	Yes	Yes
Sum / Average / Min / Max	Yes	No	Yes
CountRows / CountIf	Yes	No	Partial
Filter on a calculated value	No	No	No
GroupBy / Distinct	No	No	No

Delegation support varies by connector and column type — always verify against your own source.

Functions that never delegate

First / FirstN / Last / LastN, and GroupBy / Ungroup / Distinct on every connector.
Collection writers: Collect, ClearCollect, Concat, Concatenate, and AddColumns over a data source.
Any text or date transform *inside the predicate* — Left, Right, Mid, Len, Lower/Upper, &, and date math push the comparison to the device.
Sum / Average / CountRows on SharePoint, and Search on SharePoint columns.

Workarounds

Compare the raw column, not a transformed one: Filter(Orders, Status = "Open") delegates; Filter(Orders, Lower(Status) = "open") does not. Store a normalised column if you need case-insensitive matching.
Pre-filter at the source with a delegable predicate, then do the non-delegable shaping in memory on the (≤ 2,000-row) result.
Cache small, stable reference tables with ClearCollect in OnStart; never ClearCollect a large transactional table.
Replace per-keystroke Filter in a gallery with a debounced timer or a search button (see the recipe above).
For counts/sums on SharePoint, keep a roll-up/calculated column or move the data to Dataverse or SQL.

Tip

Every cheat table above is searchable

Use the filter box on each table to jump to a function fast. This whole guide is generated from one structured dataset, so it stays in sync with the MCP reference.

Spotted an error or have a suggestion? Let us know