Short answer
Use Patch() to create or change records, Filter() and LookUp() to read them, and Set() and UpdateContext() to store values in variables. The most important formulas for canvas apps are listed below with examples. Copy an individual formula into the property shown with the example.
Note on notation: The examples use English notation. Power Fx shows separators for your authoring locale. A decimal-comma locale, such as German, uses semicolons between arguments, the comma as the decimal separator, and double semicolons to chain actions. Patch(Orders, Defaults(Orders), {Amount: 12.5}); Back() then becomes Patch(Orders; Defaults(Orders); {Amount: 12,5});; Back(). Power Apps stores the underlying formula independently of the locale.
Sample data: Orders is a SharePoint list with the text column Title, the single-select choice column Status, the number column Amount, the date column DueDate, and the text column Comment. Customers has the text columns Email, Name, and City. Employees is a SharePoint list with a text Email column and a single-select Role choice column. Email stores the user principal name (UPN) that User().Email returns. Products is a separate connected data source. galOrders is a gallery that displays Orders. The examples use classic controls, such as the text input txtSearch and the date picker dtpStart. varJson contains a JSON string with the fields name and orders, for example {"name":"Contoso","orders":3}.
Reading data
Filter(Orders, Status.Value = "Open")
Filter(Orders, Status.Value = "Open", Amount > 1000)
LookUp(Orders, ID = galOrders.Selected.ID)
LookUp(Customers, Email = User().Email, Name)
Search(Customers, txtSearch.Text, Name, City)
Sort(Orders, DueDate, SortOrder.Descending)
SortByColumns(Orders, "DueDate", SortOrder.Ascending)
First(Filter(Orders, Status.Value = "Open"))
Filter returns a table, LookUp returns the first matching record or blank. With a third argument, LookUp returns a single value. In SortByColumns, the column name is a text string in quotes. Without sorting, First takes the order in which the data arrives. Whether a query is complete for large data volumes depends on delegation. Search, for example, isn't delegated with SharePoint. More on this under delegation in Power Apps.
Writing data with Patch
Patch(Orders, Defaults(Orders), {Title: txtTitle.Text, Amount: Value(txtAmount.Text)})
Patch(Orders, galOrders.Selected, {Comment: txtComment.Text})
Set(varCustomer, Patch({Name: "Contoso"}, {City: "Cologne"}))
To create a record, Defaults() builds the base record, and the data source in Patch and Defaults must be the same. To change a record, the base record must come from the data source, for example from a gallery or from LookUp. Patch returns the changed or new record.
Choice column and error handling
For a SharePoint single-select choice column, pass a choice record, for example the matching entry from Choices(Orders.Status). This assumes that the option "Closed" exists in the list and that varOrderId is an existing ID.
With(
{
baseOrder: LookUp(Orders, ID = varOrderId),
closedStatus: LookUp(Choices(Orders.Status), Value = "Closed")
},
If(
!IsBlank(baseOrder) && !IsBlank(closedStatus),
Patch(Orders, baseOrder, {Status: closedStatus})
)
)
IfError(
Patch(Orders, galOrders.Selected, {Amount: 400}),
Notify("Saving failed: " & FirstError.Message, NotificationType.Error); false,
true
)
IfError requires formula-level error management. It may be turned off in older apps.
Updating several known records
A table of changes must have one row for each base record, in the same order. This assumes two existing, different IDs and the option "Overdue".
With(
{
firstOrder: LookUp(Orders, ID = varFirstOrderId),
secondOrder: LookUp(Orders, ID = varSecondOrderId),
overdueStatus: LookUp(Choices(Orders.Status), Value = "Overdue")
},
If(
!IsBlank(firstOrder) && !IsBlank(secondOrder) && !IsBlank(overdueStatus),
Patch(
Orders,
Table(firstOrder, secondOrder),
Table({Status: overdueStatus}, {Status: overdueStatus})
)
)
)
Check the result for each record in a table update. Several records aren't saved as one transaction. Some records may have been saved when another update fails.
Patch or SubmitForm?
SubmitForm(frmOrder)validates the form and submits the values from its data cards. ThenOnSuccessorOnFailureruns.Patch()writes exactly the fields you specify directly to the data source, also without a form.
Collections
ClearCollect(colCart, {Product: "Performance Check", Quantity: 1})
Collect(colCart, {Product: "Approval Toolkit", Quantity: 2})
Clear(colCart)
RemoveIf(colCart, Quantity = 0)
ClearCollect(colOpenOrders, Filter(Orders, Status.Value = "Open"))
AddColumns(colCart, DoubleQuantity, Quantity * 2)
AddColumns returns a new table, colCart stays unchanged. Since Power Apps 3.24042, you write column names in AddColumns without quotes, and Power Apps updated existing apps automatically.
When you use ClearCollect with a data source, the data row limit (500 by default) can cut off the loaded records.
Variables
Set(varUserEmail, User().Email)
UpdateContext({locShowDialog: true})
Navigate(scrDetails, ScreenTransition.Fade, {locOrderId: galOrders.Selected.ID})
With(
{varNet: Value(txtAmount.Text)},
varNet * 1.19
)
Rule of thumb: Set for values the whole app needs, UpdateContext for values of one screen, With for intermediate results within one formula.
Named formulas
In the Formulas property of the App object, you define calculated values that can't be overwritten elsewhere:
UserEmail = User().Email;
UserInfo = LookUp(Employees, Email = UserEmail);
ShowAdminTools = UserInfo.Role.Value = "Admin";
Named formulas are calculated when needed and recalculated when their dependencies change. They can't contain behavior functions such as Patch or Set, and circular references aren't allowed. Use App.Formulas for calculated values and OnStart for startup actions with side effects. Microsoft recommends named formulas as an alternative to variables in OnStart, because a lot of logic in OnStart delays app loading.
ShowAdminTools only controls what's displayed. Control access to data through permissions in the data source.
User-defined functions
User-defined functions also go in App.Formulas. They've been generally available since September 2025 and require the new analysis engine, which is on in new apps. User-defined types have been generally available since May 2026 and are on by default in new apps as of Power Apps Studio 3.26044.
GrossAmount(net: Number): Number = net * 1.19;
AddToCart(product: Text, quantity: Number): Void = {
Collect(colCart, {Product: product, Quantity: quantity});
Notify("Added to cart", NotificationType.Success)
};
OrderType := Type({Title: Text, Amount: Number, DueDate: Date});
OrderListType := Type([OrderType]);
Behavior functions with side effects go in curly braces. Call it, for example, in the OnSelect property of a button: AddToCart("Performance Check", 1). According to Microsoft, recursion isn't supported. In an existing app, look under Settings > Updates > New > User-defined types.
Text, numbers, and dates
Text(Today(), "dd.mm.yyyy")
Text(Now(), "dd.mm.yyyy hh:mm")
Text(Now(), "dddd, d. mmmm yyyy", "de-DE")
Text(1234.5, "[$-en-US]#,##0.00", "de-DE")
DateAdd(Today(), 7, TimeUnit.Days)
Text(DateDiff(dtpStart.SelectedDate, Today(), TimeUnit.Days))
Text(Value("12.5", "en-US"))
DateValue("2026-11-16", "en-US")
In a date format such as mm/dd/yyyy, mm denotes the month. Directly after h or hh or directly before ss, mm stands for minutes. This differs from Power Automate, where MM is the month and mm the minute. For a fixed format, specify the format language with [$-en-US] and the output language as the third argument. DateDiff counts whole units.
Galleries, choices, and controls
SortByColumns(
Filter(Orders, StartsWith(Title, txtSearch.Text)),
"DueDate",
SortOrder.Descending
)
Refresh(Orders)
Choices(Orders.Status)
Reset(txtSearch)
txtSearch is a classic text input. To preselect a value, use the Default property of a classic drop down or DefaultSelectedItems of a combo box.
Notifications and navigation
Notify("Saved", NotificationType.Success)
Notify("Please fill in all required fields", NotificationType.Warning, 4000)
Navigate(scrDetails, ScreenTransition.Cover)
Back()
Concurrent(
ClearCollect(colCustomers, Customers),
ClearCollect(colProducts, Products)
)
Concurrent can run independent calls in parallel. Each ClearCollect still has a data row limit.
JSON
Set(varCartJson, JSON(colCart, JSONFormat.IndentFour))
Text(ParseJSON(varJson).name)
Text(Value(ParseJSON(varJson).orders))
JSON creates text and belongs in a behavior formula, for example OnSelect. Without a type, ParseJSON returns a dynamic value. Convert the fields you need with the matching function, for example Text(), Value(), Boolean(), or DateValue(), or pass a matching type as the second argument. Field names must match the JSON exactly. Missing fields and null result in blank.
To try the examples, put Set(varJson, "{""name"":""Contoso"",""orders"":3}") in a button's OnSelect property and select it once. The labels show Contoso and 3.
Sources
- Microsoft Learn (en-us): Patch (opens in a new tab) · Filter, Search, and LookUp (opens in a new tab) · Sort and SortByColumns (opens in a new tab) · Choices (opens in a new tab) · IfError (opens in a new tab) · Table (opens in a new tab) · With (opens in a new tab) · Set (opens in a new tab) · App object (opens in a new tab) · AddColumns (opens in a new tab) · Text (opens in a new tab) · DateAdd and DateDiff (opens in a new tab) · DateValue (opens in a new tab) · Notify (opens in a new tab) · JSON (opens in a new tab) · ParseJSON (opens in a new tab) · Global support (opens in a new tab) (separators) · Collect, Clear, and ClearCollect (opens in a new tab) · UpdateContext (opens in a new tab) · Navigate and Back (opens in a new tab) · Concurrent (opens in a new tab) · Refresh (opens in a new tab) · Reset (opens in a new tab)
- Microsoft Learn: SharePoint list integration in Power Apps (opens in a new tab) (en-us, Jan 13, 2026)
- Microsoft Learn: Connect to SharePoint, delegable functions (opens in a new tab) (en-us, Oct 16, 2026)
- Power Platform blog: Power Apps User Defined Functions GA (opens in a new tab) (Sep 2, 2025)
- Power Platform blog: Power Fx User Defined Types generally available (opens in a new tab) (May 13, 2026)