Common Functions: Patch, Filter, LookUp

Analogy categoryAnalogies

Three Functions You Will Use Every Day

Nearly every canvas app revolves around reading and writing rows of data, and three functions carry most of that weight: Filter returns every record from a data source matching a condition, LookUp returns exactly one record matching a condition, and Patch creates or modifies records in a data source. Understanding the precise contract of each — what they return, when they run, and how they interact with delegation — is the difference between an app that scales to a large SharePoint list or Dataverse table and one that silently breaks past a few hundred rows.

🏏

Cricket analogy: These three functions are like a captain's three core tools: Filter is scanning the whole squad for all all-rounders, LookUp is pulling one specific player's file by name, and Patch is updating that player's fitness record after training.

Filter: Many Rows That Match

Filter(Source, Condition1 [, Condition2, ...]) evaluates every record in Source against the given condition(s) — implicitly ANDed together if more than one is supplied — and returns a table containing only the matching rows, without changing the order of the underlying source. It is the function behind almost every gallery's Items property when the app needs to show a subset of data, such as Filter(Orders, Status = "Pending", Region = varSelectedRegion), and critically, when Source is a connected data source like SharePoint or Dataverse rather than a local collection, Power Apps attempts to delegate the filter to the server so it can evaluate the full table remotely instead of pulling everything down first.

🏏

Cricket analogy: Filter is like a selector scanning the entire domestic season's stats and pulling out every batter who averaged above 45 — many names can qualify, and the full squad list underneath stays unchanged.

LookUp: Exactly One Row

LookUp(Source, Condition [, ResultColumn]) scans Source and returns the single first record matching Condition, or blank if none match; if the optional ResultColumn is supplied, it returns just that column's value instead of the whole record. LookUp is the right choice whenever the condition is expected to identify a unique row, such as LookUp(Employees, EmployeeID = varSelectedID) to fetch one employee's record by their unique key, and it is meaningfully cheaper than Filter for this purpose since it can stop scanning as soon as a match is found rather than evaluating the entire table.

🏏

Cricket analogy: LookUp is like a scorer pulling up the exact career record for 'player ID 007' — there's only one such player, and the search stops the instant that one match is found.

Patch: Creating and Modifying Records

Patch(Source, BaseRecord, ChangeRecord1 [, ChangeRecord2, ...]) modifies BaseRecord by merging in the change record(s) and writes the result back to Source, returning the updated record; passing Defaults(Source) as the base record instead of an existing row creates a brand-new record. A single Patch call can also update multiple existing records at once by passing a table of records as the base instead of one record. Patch is a behavior function, so it can only be called from OnSelect, OnChange, or similar behavior properties, and because it writes immediately, it is the direct mechanism behind 'Save' buttons in forms that don't use the built-in Form control's SubmitForm.

🏏

Cricket analogy: Patch is like a team analyst updating one player's fitness file with new test results — Defaults() is like opening a fresh blank player-registration form for a brand-new squad member.

powerfx

// Filter: many matching rows for a gallery
Gallery1.Items = Filter(Orders, Status = "Pending", Region = varSelectedRegion)

// LookUp: the one order matching a unique ID
Set(varOrder, LookUp(Orders, OrderID = varSelectedID))

// LookUp with a result column: just the customer name
Set(varCustomerName, LookUp(Customers, CustomerID = varOrder.CustomerID, FullName))

// Patch: create a new record
Patch(Orders, Defaults(Orders), { CustomerID: varCustomerID, Status: "New", Total: 0 })

// Patch: update an existing record found via LookUp
Patch(Orders, LookUp(Orders, OrderID = varSelectedID), { Status: "Shipped" })

A common pattern combines all three: Patch(Orders, LookUp(Orders, OrderID = varID), { Status: "Shipped" }) uses LookUp to find the exact record to update, and Patch to write the change — this is the standard way to edit a single row without a Form control.

Filter and LookUp only delegate to the server for a subset of operators (=, <>, <, >, and, or, and a limited set of functions), and each data source has its own delegable-function list. A non-delegable condition — like Filter(Orders, Len(Notes) > 50) against SharePoint — silently falls back to querying only the first 500 (or up to 2,000, if raised) rows locally, which can produce incomplete, incorrect-looking results with no error message.

Filter(Source, Condition) returns every matching row as a table; conditions are implicitly ANDed.
LookUp(Source, Condition, [Column]) returns the first matching record only, or just one column's value.
LookUp stops scanning at the first match, making it cheaper than Filter for unique lookups.
Patch(Source, Base, Change) writes a merged record back to the data source and returns it.
Patch(Source, Defaults(Source), {...}) creates a new record instead of updating an existing one.
Patch is a behavior function, valid only inside OnSelect, OnChange, and similar properties.
Non-delegable Filter/LookUp conditions silently truncate results to the delegation row limit.