Power Fx Essentials
TL;DR
Power Fx is Excel-like but works on tables, not cells. Key functions: Filter/LookUp (query data), Patch/SubmitForm (write data), Navigate (move between screens), Set/UpdateContext (variables), Collect/ClearCollect (local tables). Always check delegation for large datasets.
Explain Like I'm 12
Power Fx is like Excel formulas but for apps. In Excel, =IF(A1>100, "High", "Low") checks one cell. In Power Fx, If(ThisItem.Price > 100, "High", "Low") checks each item in a list. The formulas look similar, but instead of cells, you're working with screens, buttons, galleries, and databases.
Reading Data
// Filter: returns a TABLE of matching records
Filter(Products, Category = "Electronics" && Price > 50)
// LookUp: returns a SINGLE RECORD (first match)
LookUp(Customers, ID = 42)
// LookUp a specific field
LookUp(Customers, ID = 42).Name
// Search: text search across multiple columns (NOT delegable to most sources!)
Search(Products, SearchBox.Text, "Name", "Description")
// Sort
SortByColumns(Products, "Price", SortOrder.Descending)
// First, Last
First(Products).Name
Last(Filter(Orders, CustomerID = 42)).OrderDate
// CountRows
CountRows(Filter(Orders, Status = "Pending"))
Delegation alert:
Search(), in operator, and complex nested conditions are NOT delegable to SharePoint. Use Filter() with StartsWith() instead, or switch to Dataverse. Non-delegable = only searches first 500-2000 rows.
Writing Data
// Patch: create or update a record directly
Patch(Products, Defaults(Products), {Name: "Widget", Price: 29.99})
// Patch: update existing record
Patch(Products, LookUp(Products, ID = 5), {Price: 34.99})
// SubmitForm: submit the form control's data
SubmitForm(EditForm1)
// Remove: delete a record
Remove(Products, ThisItem)
// RemoveIf: delete matching records
RemoveIf(TempOrders, Status = "Draft")
SubmitForm vs Patch: Use
SubmitForm when you have a Form control (handles validation, error display, mode switching). Use Patch for programmatic writes without a form, or when updating specific fields.
Variables & Collections
| Type | Function | Scope | Use For |
|---|---|---|---|
| Global variable | Set(varName, value) | Entire app | User info, app state, themes |
| Context variable | UpdateContext({varName: value}) | Current screen only | Screen-specific toggles, temp values |
| Collection | Collect(colName, record) | Entire app | Local tables, offline cache, shopping cart |
// Global variable: store logged-in user
Set(varCurrentUser, User().FullName)
// Context variable: toggle a panel's visibility
UpdateContext({showPanel: !showPanel})
// Collection: cache data locally
ClearCollect(colProducts, Products) // Full refresh
Collect(colCart, ThisItem) // Add one item
When to use collections: (1) Cache data to reduce connector calls. (2) Store temporary data that hasn't been saved yet (shopping cart). (3) Build local tables for multi-step forms. (4) Work with data offline.
Delegation Deep Dive
Delegation is the #1 gotcha in Power Apps. When a formula is delegable, the data source does the heavy lifting. When it's not, Power Apps downloads only the first 500-2000 rows and processes locally.
| Operation | SharePoint | Dataverse | SQL Server |
|---|---|---|---|
| Filter with =, <, > | Yes | Yes | Yes |
| Filter with StartsWith | Yes | Yes | Yes |
| Search() | No | Yes | No |
| Sort | Yes | Yes | Yes |
| in operator | No | Yes | Yes |
| Nested conditions | Limited | Yes | Yes |
The blue-dot warning: Power Apps shows a blue dot / yellow triangle on non-delegable formulas. Never ignore this in production — your app will silently return incomplete results on large datasets.
Common Patterns
Search + Filter Gallery
// Gallery Items property (delegable to SharePoint)
SortByColumns(
Filter(Products,
StartsWith(Name, SearchBox.Text) &&
(drpCategory.Selected.Value = "All" || Category = drpCategory.Selected.Value)
),
"Name", SortOrder.Ascending
)Save Form + Navigate Back
// Button OnSelect
SubmitForm(EditForm1)
// Form OnSuccess
Notify("Saved!", NotificationType.Success);
Navigate(BrowseScreen, ScreenTransition.None)Conditional Formatting
// Gallery item fill color
If(
ThisItem.DueDate < Today(), RGBA(255, 200, 200, 1), // Overdue = red
ThisItem.DueDate = Today(), RGBA(255, 255, 200, 1), // Due today = yellow
RGBA(255, 255, 255, 1) // Default = white
)Essential Functions Cheat Sheet
| Category | Functions |
|---|---|
| Query data | Filter, LookUp, Search, Sort, SortByColumns, FirstN, LastN |
| Write data | Patch, SubmitForm, Remove, RemoveIf, Update, UpdateIf |
| Collections | Collect, ClearCollect, Clear, Remove |
| Variables | Set, UpdateContext |
| Navigation | Navigate, Back |
| Text | Concatenate, Left, Right, Mid, Len, Upper, Lower, Text |
| Logic | If, Switch, And, Or, Not, IsBlank, Coalesce |
| Date/Time | Today, Now, DateAdd, DateDiff, Year, Month, Day |
| User | User().FullName, User().Email, User().Image |
| UI | Notify, Reset, SetFocus, RequestHide, Launch |
Test Yourself
Q: What is the difference between Filter and LookUp?
Filter returns a TABLE of all matching records. LookUp returns a SINGLE RECORD (the first match). Use Filter for galleries, LookUp for getting one specific record.Q: When should you use Set vs UpdateContext?
Set creates a global variable (accessible on all screens). UpdateContext creates a context variable (current screen only). Use Set for app-wide state (user info, theme). Use UpdateContext for screen-local toggles.Q: What happens when you use a non-delegable function on 100K rows?
Power Apps downloads only the first 500-2000 rows and applies the function locally. The remaining 98K+ rows are silently ignored. Results will be incomplete and incorrect.
Interview Questions
Q: How do you handle the delegation limit when Search() is not delegable?
Options: (1) Use
Filter with StartsWith() instead of Search (delegable to most sources). (2) Switch to Dataverse which supports delegable Search. (3) Use a ClearCollect to cache a pre-filtered subset, then search locally on the collection. (4) Use Power Automate flow to perform server-side search and return results.Q: Explain the difference between Patch, SubmitForm, and Collect for writing data.
SubmitForm: submits a Form control's data with built-in validation, error handling, and mode-awareness (New/Edit). Patch: writes directly to a data source without a form — flexible but no built-in validation. Collect: writes to a local in-memory collection only (not to the data source). Use SubmitForm with forms, Patch for programmatic writes, Collect for local staging.
Q: How would you build an offline-capable Power App?
(1) On app start,
ClearCollect data from the source into local collections. (2) Check Connection.Connected to detect offline status. (3) When offline, read/write to local collections. (4) When back online, use ForAll + Patch to sync local changes back to the source. (5) Handle conflicts (last-write-wins or merge). (6) Use SaveData/LoadData to persist collections across app restarts.Q: What is component reuse in Power Apps?
Canvas components (and now code components via PCF) let you create reusable controls with custom properties. Define input/output properties, encapsulate logic, and reuse across screens and apps. Component libraries let you share components across multiple apps in an environment. PCF (PowerApps Component Framework) allows building custom controls with TypeScript for both canvas and model-driven apps.