# Power Fx Cheat Sheet

Quick-reference formulas for Power Apps Canvas developers. Last verified July 2026.

Source: https://www.powerappsui.com/cheatsheet · Free & open source (MIT).

---

## Date & Time

### Format date

```powerfx
Text(Today(), "[$-en-US]mmmm d, yyyy")
```

### Relative time

```powerfx
If(
    DateDiff(dt, Now(), TimeUnit.Minutes) < 60,
    Text(DateDiff(dt, Now(), TimeUnit.Minutes)) & " min ago",
    Text(DateDiff(dt, Now(), TimeUnit.Hours)) & " hr ago"
)
```

### Date range filter (last 30 days)

> Delegation: Delegable (SharePoint, Dataverse, SQL)

```powerfx
Filter(Items, Created >= DateAdd(Today(), -30, TimeUnit.Days))
```

### First / last day of month

```powerfx
// First day
Date(Year(Today()), Month(Today()), 1)

// Last day
DateAdd(Date(Year(Today()), Month(Today()) + 1, 1), -1, TimeUnit.Days)
```

### Business days between dates (approx.)

```powerfx
// Approximation: subtracts 2 days per whole week. It does NOT
// account for the start/end weekday or partial weeks - good enough
// for rough estimates, not for payroll/SLA math.
With(
    {d1: startDate, d2: endDate},
    DateDiff(d1, d2, TimeUnit.Days)
    - 2 * Int(DateDiff(d1, d2, TimeUnit.Days) / 7)
)
```

### End of month (shortcut)

```powerfx
EOMonth(Today(), 0)   // last day of current month
```

## Text & Formatting

### Currency

```powerfx
Text(12500, "$#,###.00")  // → "$12,500.00"
```

### Abbreviate large numbers

```powerfx
If(
    v >= 1000000, Text(v / 1000000, "#.#") & "M",
    If(v >= 1000, Text(v / 1000, "#.#") & "K", Text(v))
)
```

### Truncate with ellipsis

```powerfx
If(Len(txt) > 50, Left(txt, 47) & "...", txt)
```

### Extract initials

```powerfx
Upper(
    Left(name, 1) &
    If(Find(" ", name) > 0, Mid(name, Find(" ", name) + 1, 1), "")
)
```

### Proper case

```powerfx
Proper(text)   // already lowercases the rest - no need to wrap in Lower()
```

## Collections & Tables

### Add computed column

> Delegation: Not delegable

```powerfx
AddColumns(Items, "FullName", FirstName & " " & LastName)
```

### Group by

> Delegation: Not delegable

```powerfx
GroupBy(Items, "Category", "GroupItems")
```

### Distinct values

> Delegation: Not delegable

```powerfx
Distinct(Items, Category)
```

### Sort multi-column

> Delegation: Delegable (SharePoint, Dataverse, SQL)

```powerfx
SortByColumns(Items, "Priority", SortOrder.Ascending, "Date", SortOrder.Descending)
```

### Remove duplicates

> Delegation: Not delegable

```powerfx
ForAll(Distinct(colItems, Title), LookUp(colItems, Title = Result))
```

### Generate a number table

```powerfx
Sequence(10)   // table 1..10 (Value column)
```

### Bulk update (ForAll + Patch)

```powerfx
ForAll(
    colChanges As c,
    Patch(YourList, LookUp(YourList, ID = c.ID), { Status: c.Status })
)
```

## Delegation & Performance

### Delegation-safe search

> Delegation: Delegable (SharePoint, Dataverse, SQL)

```powerfx
// On SharePoint, StartsWith delegates - Search(), EndsWith()
// and the "in" operator do NOT (they hit the 500-2000 row cap).
Filter(Items, StartsWith(Title, searchText))
```

### Row count (delegation-aware)

> Delegation: Depends - delegates on Dataverse, SQL

```powerfx
// Row counts are source-dependent:
//   Dataverse / SQL - CountRows and CountIf both delegate
//   SharePoint      - neither delegates; cache first with ClearCollect
CountRows(Filter(Items, Status = "Active"))
```

### Cache with ClearCollect

> Delegation: Delegable (SharePoint, Dataverse, SQL)

```powerfx
ClearCollect(colLocal, Filter(DataSource, Status = "Active"))
```

### Concurrent loading

```powerfx
Concurrent(
    ClearCollect(col1, Source1),
    ClearCollect(col2, Source2),
    ClearCollect(col3, Source3)
)
```

### Non-delegable row limit

```powerfx
// Non-delegable queries only return the first 500 rows
// (raise to 2000 max in Settings › General). Keep filters
// delegable, or cache the data with ClearCollect first.
```

## Error Handling

### Patch to SharePoint with error check

```powerfx
Set(varResult,
    Patch(
        YourSharePointList,
        Defaults(YourSharePointList),
        {
            Title: txtTitle.Text,
            Status: {Value: "Active"},
            AssignedTo: drpAssignee.Selected
        }
    )
);
If(
    IsError(varResult),
    Notify("Save failed: " & FirstError.Message, NotificationType.Error),
    Notify("Saved successfully", NotificationType.Success);
    Navigate(scrList, ScreenTransition.None)
)
```

### Edit existing SharePoint item

```powerfx
Set(varResult,
    Patch(
        YourSharePointList,
        LookUp(YourSharePointList, ID = varSelectedId),
        {
            Title: txtTitle.Text,
            Status: {Value: drpStatus.Selected.Value}
        }
    )
);
If(IsError(varResult),
    Notify("Update failed: " & FirstError.Message, NotificationType.Error),
    Notify("Updated", NotificationType.Success)
)
```

### IfError with fallback value

> Delegation: Delegable (SharePoint, Dataverse, SQL)

```powerfx
IfError(
    LookUp(Items, ID = varId),
    Notify("Not found", NotificationType.Error);
    Blank()
)
```

### Validate before Patch

```powerfx
If(
    IsBlank(txtTitle.Text),
    Notify("Title is required", NotificationType.Warning),
    IsBlank(drpStatus.Selected),
    Notify("Select a status", NotificationType.Warning),
    // All valid - save
    Patch(YourSharePointList, Defaults(YourSharePointList),
        {Title: txtTitle.Text, Status: drpStatus.Selected}
    );
    Notify("Saved", NotificationType.Success)
)
```

### Coalesce for defaults

```powerfx
Coalesce(varUser, "Unknown")
```

## Navigation & Context

### Navigate with context

```powerfx
Navigate(scrDetail, ScreenTransition.None, {selectedItem: ThisItem})
```

### Deep link params

```powerfx
If(!IsBlank(Param("id")), Set(varId, Value(Param("id"))))
```

### Back navigation

```powerfx
Back(ScreenTransition.UnCoverRight)
```

## Responsive Patterns

### Breakpoints

```powerfx
If(App.Width < 640, "mobile", If(App.Width < 1024, "tablet", "desktop"))
```

### Responsive padding

```powerfx
If(App.Width < 640, 12, 24)
```

### Responsive columns

```powerfx
If(App.Width < 640, 1, If(App.Width < 1024, 2, 4))
```

### Max-width centering

```powerfx
Min(Parent.Width - 48, 1200)
```

## Named Formulas (App.Formulas)

### Color theme

```powerfx
nfColors = {
    pageBg:  ColorValue("#F9FAFB"),
    cardBg:  ColorValue("#FFFFFF"),
    primary: ColorValue("#2563EB"),
    danger:  ColorValue("#DC2626")
};
```

### Responsive check

```powerfx
nfIsMobile = App.Width < 640;
```

### User info

```powerfx
nfCurrentUser = {
    Name:     User().FullName,
    Email:    User().Email,
    Initials: Upper(Left(User().FullName, 1))
};
```

## Variables & State

### Global variable (whole app)

```powerfx
Set(gblUserEmail, User().Email)
```

### Screen (context) variable

```powerfx
UpdateContext({ locStep: 1 })
```

### Toggle a boolean

```powerfx
UpdateContext({ locPanelOpen: !locPanelOpen })
```

### Local value, no variable

```powerfx
With({ subtotal: qty * price }, subtotal * 1.08)
```

### Collection CRUD

```powerfx
Collect(colCart, { Item: "Widget", Qty: 2 });  // add row
RemoveIf(colCart, Qty <= 0);                    // delete matching
Patch(colCart, First(colCart), { Qty: 5 });     // update row
Clear(colCart)                                  // empty
```

## Galleries & Selection

### Selected item's field

```powerfx
galItems.Selected.Title
```

### Search + filter combined

> Delegation: Delegable (SharePoint, Dataverse, SQL)

```powerfx
Filter(
    Items,
    StartsWith(Title, txtSearch.Text),
    Status.Value = drpStatus.Selected.Value
)
```

### Highlight the selected row (row Fill)

```powerfx
If(ThisItem = galItems.Selected, nfColors.primary, nfColors.cardBg)
```

### Count of shown rows

```powerfx
CountRows(galItems.AllItems) & " results"
```

### Sum a column across the gallery

```powerfx
Sum(galItems.AllItems, Amount)
```

## Forms

### New / edit / submit

```powerfx
NewForm(frmItem); Navigate(scrEdit);   // create mode
EditForm(frmItem); Navigate(scrEdit);  // edit mode
SubmitForm(frmItem)                     // save (button OnSelect)
```

### On success (form property)

```powerfx
// frmItem.OnSuccess
Notify("Saved", NotificationType.Success);
Back()
```

### Validate before submit

```powerfx
If(frmItem.Valid, SubmitForm(frmItem), Notify("Fix required fields", NotificationType.Warning))
```

### Card default value

```powerfx
Parent.Default   // in a data card control's Default
```

### Reset a form

```powerfx
ResetForm(frmItem)
```

## Choices & Dropdowns

### Selected value

```powerfx
drpStatus.Selected.Value
```

### Choice / lookup options

```powerfx
Choices(YourList.Status)
```

### Cascading dropdown

> Delegation: Delegable (SharePoint, Dataverse, SQL)

```powerfx
Filter(Cities, Country.Value = drpCountry.Selected.Value)
```

### Multi-select ComboBox → text

```powerfx
Concat(cmbTags.SelectedItems, Value, ", ")
```

### Preselect items

```powerfx
DefaultSelectedItems: Filter(Choices(YourList.Status), Value = "Active")
```

## Math & Aggregation

### Sum / average

> Delegation: Depends - delegates on Dataverse, SQL

```powerfx
Sum(Items, Amount)     // Average(Items, Score)
```

### Max / min of a column

> Delegation: Depends - delegates on Dataverse, SQL

```powerfx
Max(Items, DueDate)    // Min(Items, Price)
```

### Percent complete

```powerfx
Round(done / total * 100, 1) & "%"
```

### Rounding & modulo

```powerfx
RoundUp(x, 0)   RoundDown(x, 2)   Mod(n, 2)
```

### Percent of total per row

> Delegation: Not delegable

```powerfx
AddColumns(
    Items,
    "Share", Round(Amount / Sum(Items, Amount) * 100, 1)
)
```

## Strings & Parsing

### Split into rows

```powerfx
Split("red,green,blue", ",")   // 1-col table (Value)
```

### Join a table to text

```powerfx
Concat(colTags, Value, ", ")
```

### Validate email (regex)

```powerfx
IsMatch(txtEmail.Text, Match.Email)
```

### Extract digits (regex)

```powerfx
Match("Invoice #10432", "\d+").FullMatch   // "10432"
```

### Read JSON (ParseJSON)

```powerfx
Set(gblData, ParseJSON(txtJson.Text));
Text(gblData.customer.name)   // coerce an untyped value
```

### Write JSON

```powerfx
JSON(colItems, JSONFormat.IndentFour)
```

## Colors, Theming & SVG

### Hex → color

```powerfx
ColorValue("#2563EB")
```

### Darken / lighten

```powerfx
ColorFade(nfColors.primary, -0.2)   // negative = darker, positive = lighter
```

### Status color switch

```powerfx
Switch(
    ThisItem.Status.Value,
    "Active",  Color.Green,
    "Pending", Color.Orange,
    "Closed",  Color.Gray,
    Color.Black
)
```

### Transparent fill

```powerfx
RGBA(0, 0, 0, 0)
```

### Inline SVG icon (no PCF, no upload)

```powerfx
"data:image/svg+xml;utf8," & EncodeUrl(
    "<svg xmlns='http://www.w3.org/2000/svg' viewBox='0 0 24 24' fill='none' stroke='#2563EB' stroke-width='2'><path d='M5 12h14M12 5v14'/></svg>"
)
```

### SVG with a dynamic color (placeholder pattern)

```powerfx
// Don't concatenate the hex straight into the string - the Image can
// cache the data URI and skip re-rendering when only the color changes.
// Keep ONE base string with a token and Substitute the hex in instead.
// EncodeUrl(varHex) turns "#2563EB" into "%232563EB" so the # survives.
Substitute(
    "data:image/svg+xml;utf8," & EncodeUrl(
        "<svg xmlns='http://www.w3.org/2000/svg' viewBox='0 0 24 24' fill='__COLOR__'><circle cx='12' cy='12' r='10'/></svg>"
    ),
    "__COLOR__",
    EncodeUrl(varHex)
)
```

## Power Automate Integration

### Run a flow & capture its response

```powerfx
// Flow ends with "Respond to a PowerApp" returning a "status" text.
Set(varOut, 'Approve Request'.Run(varSelectedId, txtComment.Text));
Notify(varOut.status, NotificationType.Success)
```

### Send a collection as JSON

```powerfx
// Serialize a collection and hand it to the flow as one text param.
'Sync Cart'.Run(JSON(colCart, JSONFormat.IgnoreUnsupportedTypes))
```

### Parse a JSON response

```powerfx
// Flow returns a JSON string in "payload".
Set(varData, ParseJSON('Get Order'.Run(varId).payload));
Text(varData.customer.name);          // coerce untyped → text
Value(varData.total)                  // coerce untyped → number
```

### Loop a flow's array response into a collection

```powerfx
ClearCollect(colLines,
    ForAll(
        ParseJSON('Get Lines'.Run(varId).lines) As line,
        { Id: Value(line.id), Name: Text(line.name) }
    )
)
```

## Offline & Connectivity

### Detect connection

```powerfx
If(Connection.Connected, "Online", "Offline")
```

### Cache & restore (SaveData / LoadData)

```powerfx
// On successful load - persist to the device
SaveData(colOrders, "orders");

// App.OnStart - restore, ignoring first-run absence
LoadData(colOrders, "orders", true)   // true = don't error if missing
```

### Queue writes while offline

```powerfx
If(Connection.Connected,
    Patch(YourList, Defaults(YourList), varRecord),
    Collect(colPending, varRecord); SaveData(colPending, "pending")
)
```

### Flush the queue when back online

```powerfx
// App.OnStart / a Refresh button, once Connection.Connected is true
ForAll(colPending As p, Patch(YourList, Defaults(YourList), p));
Clear(colPending); SaveData(colPending, "pending")
// SaveData is capped (~1 MB on web / limited on mobile) - keep it small.
```

## Timer Patterns

### Auto-refresh every 30s

```powerfx
// Timer: Duration = 30000, AutoStart = true, Repeat = true
// OnTimerEnd:
ClearCollect(colData, YourList)
```

### Debounce a search box

```powerfx
// txtSearch.OnChange:
Reset(tmrDebounce)

// tmrDebounce: Duration = 400 (Repeat = false)
// OnTimerEnd:
ClearCollect(colResults, Filter(Items, StartsWith(Title, txtSearch.Text)))
```

### Delayed navigation (splash screen)

```powerfx
// Timer: Duration = 2000, AutoStart = true
// OnTimerEnd:
Navigate(scrHome, ScreenTransition.Fade)
```

### Countdown display

```powerfx
// tmrCountdown running; show remaining seconds
Text(RoundUp((tmrCountdown.Duration - tmrCountdown.Value) / 1000, 0)) & "s"
```

## Users & Profiles

### Current user basics

```powerfx
User().FullName      // "Jane Doe"
User().Email         // "jane@contoso.com"
User().Image         // profile photo URL
```

### Manager (Office365Users)

```powerfx
Office365Users.ManagerV2(User().Email).displayName
```

### Someone's profile

```powerfx
Office365Users.UserProfileV2("jane@contoso.com").jobTitle
```

### Profile photo

```powerfx
Office365Users.UserPhotoV2(User().Email)   // set as an Image.Image
```

### My direct reports

```powerfx
Office365Users.DirectReportsV2(User().Email).value   // a table
```

### Role check (is-in-a-group)

```powerfx
!IsBlank(LookUp(colAdmins, Email = Lower(User().Email)))
```

## Accessibility

### Accessible label (screen readers)

```powerfx
AccessibleLabel = "Delete " & ThisItem.Title
```

### Tab order

```powerfx
TabIndex = 1     // 0 = default order · -1 = skip (decorative only)
```

### Announce a live region

```powerfx
// On a status label - read out when its text changes
Live = Live.Polite       // Live.Assertive interrupts, use sparingly
```

### Move focus to a control

```powerfx
SetFocus(txtEmail)   // e.g. after a validation error
```

### Focus the first field on a new screen

```powerfx
// scrForm.OnVisible
SetFocus(txtFirstName)
```

## Gotchas (hard-won)

### Set() doesn't loop or accumulate

```powerfx
// WRONG: ForAll is NOT a sequential loop - iterations don't see each
//    other's Set(), so this leaves varTotal wrong.
ForAll(colRows, Set(varTotal, varTotal + Amount))
// RIGHT: use an aggregate instead
Set(varTotal, Sum(colRows, Amount))
```

### Blank() breaks collection schema

```powerfx
// WRONG: emptying with Blank() strips the columns - later Collect/Patch
//    silently loses fields or errors on the "missing" schema.
ClearCollect(colX, If(cond, SourceRows, Blank()))
// RIGHT: zero rows but keep the real schema
ClearCollect(colX, If(cond, SourceRows, Filter(SourceRows, false)))
```

### TemplateSize can't see ThisItem

```powerfx
// WRONG: Gallery.TemplateSize is evaluated once for the gallery, not
//    per row - it cannot reference ThisItem.
TemplateSize = If(ThisItem.Expanded, 120, 60)
// RIGHT: keep TemplateSize constant; vary a per-row container's Height,
//    which CAN read ThisItem.
TemplateSize = 60   // container Height: If(ThisItem.Expanded, 120, 60)
```

### AutoLayout needs an AutoLayout parent

```powerfx
// A layout (auto-layout) container only hugs its content when its
// PARENT is also a layout container. Drop one onto a plain screen or
// inside a Rectangle and it won't self-size - nest it in another
// layout container, or give it an explicit Height/Width.
```

### IsBlank vs IsEmpty

```powerfx
IsBlank(txtName.Text)   // a VALUE is blank / empty string
IsEmpty(colCart)        // a TABLE / collection has 0 rows
```

### The delegation warning is silent, not an error

```powerfx
// The blue-underline "delegation" warning doesn't stop the app -
// it just caps results at the data row limit (500, up to 2000).
// You get WRONG data, not an error. Keep filters delegable or
// ClearCollect the source first, then work locally.
```