Power Apps Functions: Complete Guide with Examples

I’ve been building Power Apps since the days when you had to explain to people what a “canvas app” even was. Over the years, I’ve shipped apps for HR onboarding, field inspections, asset tracking, approval workflows, and more inventory systems than I care to count — for teams of five people and for organizations with tens of thousands of records flowing through SharePoint and Dataverse.

And in every single one of those projects, the same thing was true: the app was only ever as good as the formulas behind it.

You can drag controls onto a screen all day. That’s the easy part. The moment a client says “can we filter this by department, hide the delete button for non-managers, and flag anything overdue in red?” — that’s when you’re writing Power Fx. That’s where apps are actually built.

Here’s what I’ve noticed teaching this to hundreds of people: most beginners don’t struggle because Power Fx is hard. They struggle because the documentation lists functions alphabetically, in isolation, with examples that look nothing like real work. You learn what Filter() does, but nobody tells you that combining it with Search() in the wrong order will silently break your app once you hit 500 records. Nobody warns you that patching a choice column with a plain string fails without an error. Those are the things that cost you an afternoon.

So I wrote this tutorial in such a way that I wish someone had explained it to me. Not a function dictionary — a working reference built from actual production apps.

Here’s what you’ll get:

• The 25 functions that genuinely do 90% of the work, explained in the order you’ll need them
• The critical difference between value functions and behavior functions — get this wrong and you’ll spend hours on an error message that makes no sense
• Delegation, explained properly, including the five workarounds I use on every large data source
• Six complete, copy-ready examples: searchable galleries, form validation, cascading dropdowns, role-based security, a shopping cart, and a summary dashboard
• A troubleshooting table of every error I’ve personally hit, and exactly how I fixed it
• The time zone traps that cause more support tickets than anything else in Power Apps

Every formula here is one I’ve written in a real app for a real client. No toy examples.

Whether you’re a citizen developer who just built your first screen, or a consultant who wants a solid reference to keep open in a second tab — bookmark this page. You’ll come back to it.

Let’s get into it.

What Are Power Apps Functions?

A Power Apps function is a built-in piece of logic that takes some input, does something with it, and gives you a result back.

Here’s the simplest example I can give you:

Sum(10, 20, 30)

This returns 60. The function name is Sum, the inputs (called arguments) are the numbers inside the parentheses, and the result is 60.

Power Apps uses a formula language called Power Fx. Microsoft designed Power Fx to feel like Excel on purpose, so people who already know spreadsheets don’t have to learn programming from scratch. And honestly, that design choice is the biggest reason low-code development took off the way it did.

Here are the three things I want you to remember about Power Fx:

1. It’s declarative. You describe what you want, not how to get there step by step. Just like a spreadsheet cell.
2. It recalculates automatically. When the data behind a formula changes, Power Apps updates the result instantly. You never write “refresh” logic for display formulas.
3. It’s strongly typed but forgiving. Power Apps will warn you with a red squiggle when something’s wrong, which is a lifesaver when you’re learning.

Functions vs. Properties vs. Operators

New users mix these up constantly, so let me clear it up quickly.

Term	What It Is	Example
Property	A setting on a control that you write formulas into	Text, Fill, Visible, OnSelect
Function	Built-in logic that returns a value or performs an action	Sum(), Filter(), Navigate()
Operator	A symbol that combines values	+, &, =, in, &&
Signal	A value that changes on its own	Location, Connection, Acceleration

So when I write this in a Label’s Text property:

"Hello, " & User().FullName

Text is the property, User() is the function, and & is the operator that joins the two strings together.

Behavior Functions vs. Non-Behavior Functions

This is the single most important concept in this whole tutorial, and it trips up almost everyone at the start.

Power Apps splits functions into two camps:

Non-behavior functions just calculate and return a value. They don’t change anything. You use them in properties like Text, Items, Visible, Fill, and X/Y.

Sum(Sales, Amount)
Upper("power apps")
If(Value > 100, "High", "Low")

Behavior functions actually do something — they change data, navigate screens, set variables, or open things. You can only use these in behavior properties like OnSelect, OnStart, OnChange, OnVisible, and OnSuccess.

Patch(Employees, Defaults(Employees), {Title: "New Hire"})
Navigate(ScreenTwo)
Set(varUserName, User().FullName)
Notify("Saved!", NotificationType.Success)

Here’s the mistake I made early on: I tried putting Set() inside a Label’s Text property and couldn’t figure out why it kept erroring. You cannot use behavior functions in display properties. Period. Power Apps will show you an error like “Behavior function in a non-behavior property.”

Chaining Multiple Behavior Functions

Inside a behavior property, you can run several actions in sequence. Separate them with a semicolon (;) or, in some regions, a double semicolon (;;).

Set(varLoading, true);
Patch(Requests, Defaults(Requests), {Title: txtTitle.Text});
Notify("Request submitted", NotificationType.Success);
Set(varLoading, false);
Navigate(ConfirmScreen, ScreenTransition.Fade)

They run top to bottom, one after another. Simple as that.

Regional tip: If your machine uses commas as decimal separators (common in Europe), Power Apps uses ; to separate arguments and ;; to chain statements. If you use periods for decimals, it’s , and ;.

How to Write Your First Power Apps Function

Let me get you a quick win before we go deeper.

1. Open Power Apps Studio and create a blank canvas app.
2. Insert a Text label and a Button from the Insert menu.
3. Select the button, and in the property dropdown at the top-left, choose OnSelect.
4. Type this into the formula bar:

Notify("My first Power Apps function works!", NotificationType.Success)

5. Hold Alt and click the button (or press F5 to preview).

You’ll see a green banner slide across the top. That’s it — you just wrote and ran a Power Apps function.

Now select the label and set its Text property to:

"Welcome, " & User().FullName & "!"

Your name appears instantly. No save, no compile, no deploy. This instant feedback loop is what makes Power Apps so pleasant to learn.

The Most Important Power Apps Functions (By Category)

I’ve organized these the way I actually think about them when building apps. I’d suggest bookmarking this section — I still refer back to lists like this after years of building.

1. Text Functions in Power Apps

Text manipulation comes up in nearly every app. Here are the ones I use constantly.

Concatenate and the & Operator

Both join text together. I almost always use & because it’s shorter.

Concatenate("John", " ", "Doe")     // Returns "John Doe"
"John" & " " & "Doe"                // Same result, less typing

For a deeper dive on combining strings, I’ve written a full walkthrough on how to concatenate text strings in Power Apps.

Left, Right, and Mid

These pull out portions of text.

Left("Power Apps", 5)        // "Power"
Right("Power Apps", 4)       // "Apps"
Mid("Power Apps", 7, 4)      // "Apps"  (start at char 7, take 4 chars)

Mid takes three arguments: the text, the starting position, and how many characters to grab. If you skip the third argument, it grabs everything to the end.

A practical example — extracting the username from an email:

Left(User().Email, Find("@", User().Email) - 1)

If the email is sarah.jones@contoso.com, this returns sarah.jones.

Want more on this? Check out my detailed guide on the Power Apps Substring function.

Len

Counts characters. Great for validation.

Len("Power Apps")              // 10
Len(txtPassword.Text) >= 8     // Returns true/false

Here’s a real use case — disabling a Submit button until the input is long enough. Set the button’s DisplayMode:

If(Len(txtComments.Text) >= 20, DisplayMode.Edit, DisplayMode.Disabled)

More examples in my Power Apps Len function tutorial.

Upper, Lower, and Proper

Case conversion.

Upper("power apps")     // "POWER APPS"
Lower("POWER APPS")     // "power apps"
Proper("power apps")    // "Power Apps"

Proper is handy for cleaning up names users type in all lowercase. See the full Lower, Upper, and Proper function guide for more.

Trim and TrimEnds

Trim removes extra spaces everywhere including doubles in the middle. TrimEnds only removes leading and trailing spaces.

Trim("  Power    Apps  ")       // "Power Apps"
TrimEnds("  Power    Apps  ")   // "Power    Apps"

I always wrap user text input in Trim() before saving it. It prevents so many duplicate-record headaches. Here’s my full Power Apps Trim function walkthrough.

Substitute and Replace

Substitute swaps text by matching what it finds. Replace swaps text by position.

Substitute("Power Apps", "Apps", "Automate")   // "Power Automate"
Replace("Power Apps", 7, 4, "Fx")              // "Power Fx"

I use Substitute far more often — for example, stripping out characters before saving:

Substitute(Substitute(txtPhone.Text, "-", ""), " ", "")

Read more in the Power Apps Replace function guide.

Split

Breaks text into a table of values based on a separator.

Split("Red,Green,Blue", ",")

This returns a single-column table with three rows. You’ll see the column name is Value. Combine it with First() or Last():

Last(Split("report_2024_final.pdf", ".")).Value   // "pdf"

I’ve covered this in depth in my Power Apps Split function tutorial, plus a practical example on how to split text into a collection.

Find, StartsWith, and EndsWith

Find("Apps", "Power Apps")           // 7 (position where it starts)
StartsWith("Power Apps", "Power")    // true
EndsWith("report.pdf", ".pdf")       // true

StartsWith and EndsWith are case-insensitive, which is usually what you want. And importantly, StartsWith is delegable with SharePoint — meaning it filters on the server, not in the app. More on delegation shortly.

Learn more in my StartsWith and EndsWith functions post.

Text (Formatting Function)

This one is deceptively powerful. It converts numbers and dates into formatted text.

Text(1234.5678, "[$-en-US]#,##0.00")     // "1,234.57"
Text(Today(), "dd/mm/yyyy")              // "26/08/2026"
Text(Now(), "mmmm dd, yyyy hh:mm AM/PM") // "August 26, 2026 09:30 AM"
Text(0.856, "0.0%")                      // "85.6%"

You can also use built-in enums, which respect the user’s regional settings:

Text(Today(), DateTimeFormat.LongDate)
Text(Now(), DateTimeFormat.ShortDateTime)

For currency work specifically, see my guide on formatting a number as currency in Power Apps.

Value

The opposite of Text — converts a text string into a number.

Value("42")        // 42
Value("42") + 8    // 50
Value(txtQty.Text) * Value(txtPrice.Text)

This is essential because text inputs always give you text, even when they look like numbers. Full details in my Power Apps Value function article.

String Interpolation (The Modern Way)

Microsoft added string interpolation to Power Fx, and it’s now my preferred way to build strings. Instead of chaining & symbols, you prefix the string with $ and wrap expressions in curly braces:

$"Hello {User().FullName}, you have {CountRows(Tasks)} tasks."

Compare that to the old way:

"Hello " & User().FullName & ", you have " & CountRows(Tasks) & " tasks."

Much cleaner, right? I’ve written a complete guide on Power Apps string interpolation if you want to go deeper.

2. Logical Functions in Power Apps

This is where your app gets its brain.

If

The workhorse of Power Fx.

If(condition, resultIfTrue, resultIfFalse)

Real examples:

If(Value(txtScore.Text) >= 50, "Pass", "Fail")

If(ThisItem.Status = "Approved", Color.Green, Color.Red)

If(IsBlank(txtName.Text), "Name is required", "")

You can also nest conditions by adding more pairs — Power Fx supports an “else if” pattern natively:

If(
    Value(txtScore.Text) >= 90, "A",
    Value(txtScore.Text) >= 80, "B",
    Value(txtScore.Text) >= 70, "C",
    "F"
)

Notice the last value has no condition — that’s the default “else” result. This is much more readable than nesting If inside If. Check out my Power Apps If statement tutorial for lots more examples.

Switch

When you’re comparing one value against many possibilities, Switch is cleaner than If.

Switch(
    Dropdown1.Selected.Value,
    "High",   Color.Red,
    "Medium", Color.Orange,
    "Low",    Color.Green,
    Color.Gray    // default
)

Rule of thumb I follow: Use Switch when you’re testing one value against several options. Use If when each condition is different.

And, Or, Not

You can write these as functions or as operators. I use the operators.

And(A, B)   is the same as   A && B
Or(A, B)    is the same as   A || B
Not(A)      is the same as   !A

Example:

If(
    Len(txtName.Text) > 0 && Len(txtEmail.Text) > 0 && chkTerms.Value,
    DisplayMode.Edit,
    DisplayMode.Disabled
)

IsBlank, IsEmpty, and Coalesce

These three cause endless confusion, so let me be really clear:

Function	Use It For	Example
IsBlank()	A single value that’s empty or null	IsBlank(txtName.Text)
IsEmpty()	A table/collection with zero rows	IsEmpty(Filter(Tasks, Status = "Open"))
IsBlankOrError()	Checks for blank or error	IsBlankOrError(LookUp(...))
Coalesce()	Returns the first non-blank value	Coalesce(txtNick.Text, txtName.Text, "Guest")

Coalesce is underrated. Instead of writing:

If(IsBlank(ThisItem.Nickname), ThisItem.FullName, ThisItem.Nickname)

You can just write:

Coalesce(ThisItem.Nickname, ThisItem.FullName, "Unknown")

IfError

Wrap risky operations so your app doesn’t blow up in the user’s face.

IfError(
    Value(txtInput.Text),
    0
)

If the user typed something that isn’t a number, this returns 0 instead of an error. Combine it with Patch for safe saves:

IfError(
    Patch(Requests, Defaults(Requests), {Title: txtTitle.Text}),
    Notify("Something went wrong. Please try again.", NotificationType.Error)
)

3. Table and Data Functions

These are the functions that actually work with your SharePoint lists, Dataverse tables, Excel files, and collections. This is the heart of most business apps.

Filter

Returns all records that match your criteria.

Filter(Employees, Department = "Sales")

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

Filter(Products, Price > 100, InStock = true)

Note that you can separate multiple conditions with commas (treated as AND) or use && explicitly. I prefer && for clarity.

For a full breakdown with SharePoint, see my guide on how to filter a SharePoint list in Power Apps and applying multiple filters on a Power Apps gallery.

Search

Does a partial, case-insensitive text match — perfect for search boxes.

Search(Employees, txtSearch.Text, "FullName", "Email", "Department")

The syntax is: Search(DataSource, SearchString, Column1, Column2, ...).

Three things I want you to know about Search:

1. Column names go in quotes — this trips up beginners constantly. It’s "FullName", not FullName.
2. It only works on text columns. You can’t search a number or choice column directly with it.
3. If the search box is empty, Search returns all records. That’s actually really convenient — no need for extra If(IsBlank(...)) logic.

Here’s the pattern I use on almost every gallery:

SortByColumns(
    Search(Employees, txtSearch.Text, "FullName", "Department"),
    "FullName",
    SortOrder.Ascending
)

Filter vs. Search — when do I use which?

	Filter	Search
Match type	Exact / formula-based	Partial text match
Case sensitive	Depends on your formula	Always case-insensitive
Column syntax	ColumnName = "x"	"ColumnName" in quotes
Multi-column	Write each condition	Just list the columns
Best for	Precise business rules	User-facing search boxes

If you want partial matching inside a Filter, you can combine them using the in operator or StartsWith:

// Partial match anywhere in the string (NOT delegable)
Filter(Employees, txtSearch.Text in FullName)

// Partial match at the beginning (IS delegable with SharePoint)
Filter(Employees, StartsWith(FullName, txtSearch.Text))

I lean on StartsWith when my list is large, because delegation matters — more on that in a moment.

For more examples, see my full Power Apps Search function tutorial and my guide on how to search a SharePoint list in Power Apps.

LookUp

While Filter returns many records, LookUp returns one single record — the first match it finds.

LookUp(Employees, EmployeeID = 1024)

You can also grab just one column by adding a third argument:

LookUp(Employees, EmployeeID = 1024, FullName)

This is my go-to for pulling related information. For example, showing a manager’s email based on the selected department:

LookUp(Departments, Title = drpDept.Selected.Value, ManagerEmail)

Always wrap it in Coalesce or IfError if there’s a chance no match exists:

Coalesce(
    LookUp(Departments, Title = drpDept.Selected.Value, ManagerEmail),
    "No manager assigned"
)

Read more in my Power Apps LookUp function guide.

Sort and SortByColumns

Sort(Employees, FullName)                        // A to Z
Sort(Employees, HireDate, SortOrder.Descending)  // Newest first
Sort(Products, Price * Quantity, SortOrder.Descending)  // Sort by a formula

SortByColumns uses column names as text strings, and lets you sort by multiple columns:

SortByColumns(Employees, "Department", SortOrder.Ascending, "FullName", SortOrder.Ascending)

Here’s a neat trick — a gallery header that toggles sort direction when clicked. Put this on the header’s OnSelect:

UpdateContext({locSortDesc: !locSortDesc; locSortCol: "FullName"})

Then in the gallery’s Items:

SortByColumns(
    Employees,
    locSortCol,
    If(locSortDesc, SortOrder.Descending, SortOrder.Ascending)
)

More on this in my Power Apps SortByColumns function tutorial.

First, Last, FirstN, LastN

First(Employees)                    // First record
Last(Employees)                     // Last record
FirstN(Sort(Sales, Amount, SortOrder.Descending), 5)   // Top 5 sales
LastN(Tasks, 10)                    // Last 10 tasks

To grab a value out of a single record, use dot notation:

First(Employees).FullName

CountRows, CountIf, Sum, Average, Max, Min

Your aggregation toolkit.

CountRows(Employees)
CountRows(Filter(Tasks, Status = "Open"))
CountIf(Tasks, Status = "Open")             // Same thing, shorter
Sum(OrderLines, Quantity * UnitPrice)
Average(Reviews, Rating)
Max(Sales, Amount)
Min(Products, Price)

A dashboard tile I build all the time:

// Label Text property
$"You have {CountIf(Tasks, AssignedTo.Email = User().Email && Status <> "Complete")} open tasks"

For deeper examples, see my Power Apps Sum function and Power Apps CountRows function guides.

Distinct

Returns unique values from a column as a single-column table with a Value column.

Distinct(Employees, Department)

Perfect for populating a dropdown dynamically:

// Dropdown Items property
Sort(Distinct(Employees, Department), Value)

Full walkthrough in my Power Apps Distinct function post.

AddColumns, DropColumns, ShowColumns, RenameColumns

These reshape tables on the fly.

AddColumns(
    OrderLines,
    "LineTotal", Quantity * UnitPrice
)

Now every row has a new calculated LineTotal column you can display or sum.

ShowColumns(Employees, "FullName", "Email")     // Keep only these
DropColumns(Employees, "Salary", "SSN")         // Remove these
RenameColumns(Employees, "Title", "JobRole")    // Rename

You can stack them:

SortByColumns(
    AddColumns(
        Filter(OrderLines, OrderID = varOrderID),
        "LineTotal", Quantity * UnitPrice
    ),
    "LineTotal",
    SortOrder.Descending
)

I use AddColumns constantly for gallery calculations. Read more in my Power Apps AddColumns function tutorial.

GroupBy and Ungroup

GroupBy bundles records together by one or more columns.

GroupBy(Sales, "Region", "SalesByRegion")

This gives you one row per region, with a nested table called SalesByRegion holding all the matching records. Then you can aggregate:

AddColumns(
    GroupBy(Sales, "Region", "SalesData"),
    "TotalSales", Sum(SalesData, Amount),
    "OrderCount", CountRows(SalesData)
)

That single formula gives you a complete summary table — region, total sales, and order count. Drop it into a gallery and you’ve built a report.

Sequence

Generates a table of numbers. Great for star ratings, calendars, and repeating layouts.

Sequence(5)          // Table with values 1,2,3,4,5
Sequence(10, 0, 5)   // 10 values, starting at 0, step 5: 0,5,10,15...

Use it as a gallery’s Items to create a 5-star rating control:

// Gallery Items
Sequence(5)

// Icon inside the gallery, Color property
If(Value <= ThisItem.Rating, Color.Gold, Color.LightGray)

ForAll

Runs a formula for every record in a table. This is one of the most powerful — and most misunderstood — functions in Power Apps.

Important: ForAll is not a traditional loop. It doesn’t run sequentially and you can’t rely on order. Think of it as “apply this to every row and give me back a table.”

ForAll(
    colCartItems,
    Patch(
        Orders,
        Defaults(Orders),
        {
            Product: ThisRecord.ProductName,
            Quantity: ThisRecord.Qty
        }
    )
)

Use ThisRecord to reference the current row — it’s clearer than relying on implicit column names, and it avoids naming collisions.

You can also use As to name the record:

ForAll(colCartItems As Item,
    Patch(Orders, Defaults(Orders), {Product: Item.ProductName})
)

⚠️ Performance warning: ForAll with Patch inside makes one call per record. For 500 records, that’s 500 network calls. Instead, pass a table directly to Patch:

Patch(Orders, ForAll(colCartItems, {Product: ProductName, Quantity: Qty}))

That’s dramatically faster. More detail in my Power Apps ForAll function guide.

4. Data Modification Functions (Behavior)

These actually write data back to your source. Remember — behavior properties only.

Patch

The most flexible way to create or update records. I use Patch for 90% of my data writes.

Create a new record:

Patch(
    Employees,
    Defaults(Employees),
    {
        Title: txtName.Text,
        Department: drpDept.Selected.Value,
        HireDate: dtpHire.SelectedDate
    }
)

Update an existing record:

Patch(
    Employees,
    LookUp(Employees, ID = varSelectedID),
    { Department: "Marketing" }
)

The pattern is always: Patch(DataSource, BaseRecord, ChangesRecord). Use Defaults() for new records, a LookUp or Gallery.Selected for existing ones.

Update multiple records at once:

Patch(
    Tasks,
    ForAll(
        Filter(Tasks, Status = "Pending"),
        { ID: ID, Status: "Approved" }
    )
)

I’ve written a complete deep-dive on the Power Apps Patch function with SharePoint-specific examples for people, choice, and lookup columns.

Patching Special SharePoint Column Types

These catch everyone out, so here’s the cheat sheet:

Choice column:

{ Status: { Value: "Approved" } }

Person column:

{
    Manager: {
        '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
        Claims: "i:0#.f|membership|" & Lower(txtEmail.Text),
        DisplayName: txtName.Text,
        Email: txtEmail.Text,
        Department: "",
        JobTitle: "",
        Picture: ""
    }
}

Lookup column:

{
    Project: {
        '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
        Id: varProjectID,
        Value: varProjectTitle
    }
}

Multi-select choice:

{ Skills: Table({Value: "Power Apps"}, {Value: "SharePoint"}) }

Yes/No column:

{ IsActive: true }

Collect, ClearCollect, and Clear

Collections are in-memory tables that live only in your app session.

Collect(colTasks, {Title: "Review report", Done: false})   // Adds to existing
Clear(colTasks)                                            // Empties it
ClearCollect(colTasks, Tasks)                              // Clear + fill in one step

ClearCollect is my default because it prevents duplicate stacking. I typically load reference data in App.OnStart:

ClearCollect(colDepartments, Distinct(Employees, Department));
ClearCollect(colStatuses, Choices(Tasks.Status));
Set(varUser, User())

Learn more in my Power Apps ClearCollect function and Power Apps collections tutorials.

Remove and RemoveIf

Remove(Employees, Gallery1.Selected)
RemoveIf(colCart, ProductID = ThisItem.ProductID)
Remove(colTasks, First(Filter(colTasks, Done = true)))

Always add a confirmation before deleting. I use a variable-driven popup:

// Delete icon OnSelect
Set(varConfirmDelete, true); Set(varRecordToDelete, ThisItem)

// Confirm button OnSelect
Remove(Employees, varRecordToDelete);
Set(varConfirmDelete, false);
Notify("Record deleted", NotificationType.Success)

Update and UpdateIf

UpdateIf is a quick way to change specific columns across matching records:

UpdateIf(Tasks, DueDate < Today() && Status = "Open", { Status: "Overdue" })

SubmitForm, ResetForm, NewForm, EditForm, ViewForm

If you’re using Edit Form controls instead of Patch:

SubmitForm(frmEmployee)
ResetForm(frmEmployee)
NewForm(frmEmployee)        // Switch to new-record mode
EditForm(frmEmployee)       // Switch to edit mode
ViewForm(frmEmployee)       // Read-only mode

A complete save flow:

// Save button OnSelect
SubmitForm(frmEmployee)

// Form OnSuccess
Notify("Saved successfully!", NotificationType.Success);
ResetForm(frmEmployee);
Navigate(ListScreen, ScreenTransition.UnCoverRight)

// Form OnFailure
Notify("Save failed: " & frmEmployee.Error, NotificationType.Error)

For a full guide, see my Power Apps SubmitForm function post.

5. Variable Functions in Power Apps

Power Apps has three types of variables, and picking the right one matters.

Type	Function	Scope	Best For
Global	Set()	Entire app	User info, app-wide settings, selected records
Context	UpdateContext()	One screen only	Popup visibility, screen-specific toggles
Collection	Collect()	Entire app	Tables of data, cached lists, shopping carts

Set (Global Variables)

Set(varUserEmail, User().Email)
Set(varIsAdmin, User().Email in colAdmins.Email)
Set(varSelectedRecord, Gallery1.Selected)

To clear one:

Set(varSelectedRecord, Blank())

UpdateContext (Context Variables)

UpdateContext({ locShowPopup: true })
UpdateContext({ locShowPopup: false, locMessage: "Saved" })

Notice the curly braces — UpdateContext takes a record, not a name/value pair. That’s the number one syntax mistake I see.

Toggle trick:

UpdateContext({ locShowFilter: !locShowFilter })

With (Scoped Values)

With creates a temporary named value inside a single formula. It makes complex formulas dramatically more readable and avoids recalculating the same thing repeatedly.

With(
    { total: Sum(colCart, Price * Qty) },
    $"Subtotal: {Text(total, "[$-en-US]$#,##0.00")} | Tax: {Text(total * 0.08, "[$-en-US]$#,##0.00")}"
)

Without With, you’d calculate that sum twice. I reach for this whenever a formula starts repeating itself.

For more, read my Power Apps variables tutorial and the guide on global vs. context variables.

6. Date and Time Functions

Getting the Current Date and Time

Today()      // Today's date, no time component
Now()        // Current date AND time
UTCNow()     // Current UTC date and time

Today() recalculates when the app opens; Now() updates continuously. Use Today() for comparisons to avoid time-component surprises.

Building and Extracting Dates

Date(2026, 8, 26)              // Creates a date value
Time(14, 30, 0)                // Creates a time value
Year(Today())                  // 2026
Month(Today())                 // 8
Day(Today())                   // 26
Hour(Now())                    // 9
Minute(Now())                  // 30
Weekday(Today())               // 4 (Wednesday, when Sunday = 1)

A couple of behaviors worth knowing about the Date function, straight from the Microsoft docs:

• If Year is between 0 and 1899, Power Apps adds 1900 to it. So Date(70, 1, 1) gives you January 1, 1970.
• If Month is outside 1–12, it rolls over. Date(2026, 13, 1) returns January 1, 2027.
• If Day exceeds the days in the month, it rolls into the next month. Date(2026, 2, 30) returns March 2, 2026.

That rollover behavior is actually really useful. Want the last day of the current month? Just ask for day zero of next month:

Date(Year(Today()), Month(Today()) + 1, 0)

And the first day of the current month:

Date(Year(Today()), Month(Today()), 1)

I use those two lines constantly for month-to-date dashboards.

Weekday and Named Days

Weekday() returns a number, but you can change what counts as day one:

Weekday(Today())                              // Sunday = 1
Weekday(Today(), StartOfWeek.Monday)          // Monday = 1

To get the actual day name, use Text():

Text(Today(), "dddd")     // "Wednesday"
Text(Today(), "ddd")      // "Wed"
Text(Today(), "mmmm")     // "August"

A handy business-day check:

If(
    Weekday(dtpDate.SelectedDate) in [1, 7],
    "Please select a weekday",
    ""
)

Converting Text into Dates

This is where a lot of beginners get stuck, because text inputs never give you real dates.

DateValue("10/01/2026")                    // Converts text to a date
TimeValue("12:15 PM")                      // Converts text to a time
DateTimeValue("January 10, 2026 12:13 AM") // Converts text to date + time

Three things to remember:

1. DateValue ignores any time portion in the string, and TimeValue ignores any date portion. If you need both, use DateTimeValue.
2. If the text can’t be interpreted as a date, you get an error — so wrap it in IfError.
3. You can force a specific language with a second argument, which is essential for international apps:

DateValue("10/01/2026", "en-GB")    // 10 January 2026
DateValue("10/01/2026", "en-US")    // October 1, 2026

Same string, completely different date. This is exactly the kind of bug that only shows up after you ship to another region, so be explicit whenever the format matters.

A safe conversion pattern I use:

IfError(DateValue(txtDate.Text), Blank())

For a full walkthrough, see my Power Apps DateValue function guide.

Date Math with DateAdd and DateDiff

DateAdd(Today(), 7)                          // 7 days from today
DateAdd(Today(), -30, TimeUnit.Days)         // 30 days ago
DateAdd(Today(), 3, TimeUnit.Months)         // 3 months out
DateAdd(Now(), 2, TimeUnit.Hours)            // 2 hours from now

The default unit is Days, so you can leave it off for simple day math.

DateDiff(dtpStart.SelectedDate, dtpEnd.SelectedDate)                    // Days between
DateDiff(ThisItem.Created, Now(), TimeUnit.Hours)                       // Hours elapsed
DateDiff(ThisItem.BirthDate, Today(), TimeUnit.Years)                   // Age

⚠️ A gotcha worth internalizing: DateDiff counts boundaries crossed, not full units. DateDiff(Date(2026,12,31), Date(2027,1,1), TimeUnit.Years) returns 1, even though it’s only one day apart. For an accurate age calculation, do this instead:

With(
    { bd: dtpBirth.SelectedDate },
    DateDiff(bd, Today(), TimeUnit.Years) -
    If(
        Date(Year(Today()), Month(bd), Day(bd)) > Today(),
        1,
        0
    )
)

That subtracts a year if the birthday hasn’t happened yet this year.

More examples live in my Power Apps DateAdd and DateDiff functions tutorial.

Practical Date Recipes

Here are formulas I copy into nearly every app I build.

Overdue indicator on a gallery item:

If(
    ThisItem.DueDate < Today() && ThisItem.Status <> "Complete",
    Color.Red,
    Color.Black
)

“Due in X days” friendly text:

With(
    { d: DateDiff(Today(), ThisItem.DueDate) },
    Switch(
        true,
        d < 0,  $"Overdue by {Abs(d)} days",
        d = 0,  "Due today",
        d = 1,  "Due tomorrow",
        $"Due in {d} days"
    )
)

That Switch(true, ...) pattern is one of my favourite tricks — it lets you evaluate a series of conditions and return the first one that’s true, just like a cleaner If chain.

Filter records from the last 30 days:

Filter(Orders, OrderDate >= DateAdd(Today(), -30))

Start and end of the current week (Monday start):

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

// Sunday
DateAdd(Today(), 7 - Weekday(Today(), StartOfWeek.Monday))

Count working days between two dates:

CountRows(
    Filter(
        ForAll(
            Sequence(DateDiff(dtpStart.SelectedDate, dtpEnd.SelectedDate) + 1),
            { d: DateAdd(dtpStart.SelectedDate, Value - 1) }
        ),
        !(Weekday(d) in [1, 7])
    )
)

Default a date picker to the first working day next week:

DateAdd(Today(), 8 - Weekday(Today(), StartOfWeek.Monday))

Time Zones: The Thing Everyone Gets Wrong

This deserves its own section because it causes more support tickets than anything else I’ve dealt with.

SharePoint and Dataverse store dates in UTC. Power Apps displays them in the user’s local time zone. So a record created at 10:00 PM in New York can display as the next day for a colleague in London.

Three functions manage this:

TimeZoneOffset()                    // Minutes between local time and UTC
DateAdd(Now(), TimeZoneOffset(), TimeUnit.Minutes)          // Local → UTC
DateAdd(varUTCDate, -TimeZoneOffset(varUTCDate), TimeUnit.Minutes)  // UTC → Local

Note that I pass the date into TimeZoneOffset() in the second example. That’s deliberate — daylight saving means the offset in July is different from the offset in January, so always pass the specific date you’re converting.

My rules for staying sane with time zones:

1. If you only care about the date (birthdays, due dates, holidays), set the SharePoint column to Date Only. This sidesteps the entire problem.
2. If you need date and time, store UTC and convert only for display.
3. Never compare a raw stored datetime against Today() without thinking about the offset.
4. When patching a date-only value, strip the time first:

Patch(Tasks, Defaults(Tasks), { DueDate: DateValue(Text(dtpDue.SelectedDate, "mm/dd/yyyy"), "en-US") })

For a full deep-dive, read my guide on working with time zones in Power Apps and formatting dates in Power Apps.

7. Navigation and Screen Functions

Navigate

Moves the user to another screen.

Navigate(DetailScreen)
Navigate(DetailScreen, ScreenTransition.Fade)
Navigate(DetailScreen, ScreenTransition.Cover, { locRecordID: ThisItem.ID })

That third argument passes context variables to the destination screen. It’s the cleanest way to hand data between screens without creating global variables everywhere.

Available transitions: Fade, Cover, CoverRight, UnCover, UnCoverRight, and None.

Back

Returns to the previous screen, automatically reversing the transition.

Back()
Back(ScreenTransition.UnCover)

I always use Back() for cancel buttons instead of Navigate(). It respects the user’s actual path through the app, which matters once you have more than three screens.

Exit and Launch

Exit()                                  // Closes the app
Exit(true)                              // Closes and signs the user out
Launch("https://www.microsoft.com")     // Opens a URL
Launch("mailto:" & txtEmail.Text & "?subject=Your%20Request")
Launch("tel:" & ThisItem.Phone)

You can also pass parameters to another Power App:

Launch("/providers/Microsoft.PowerApps/apps/" & varAppID, { RecordID: ThisItem.ID })

Param

Reads parameters passed into your app via the URL. Put this in a label or variable:

Param("RecordID")

Combine it with App.StartScreen to deep-link straight into a record:

// App StartScreen property
If(!IsBlank(Param("RecordID")), DetailScreen, HomeScreen)

StartScreen is the modern replacement for using Navigate() inside OnStart — it’s faster and Microsoft explicitly recommends it now.

For more, see my Power Apps Navigate function guide.

8. Math and Number Functions

Sum(10, 20, 30)              // 60
Average(10, 20, 30)          // 20
Round(3.14159, 2)            // 3.14
RoundUp(3.1, 0)              // 4
RoundDown(3.9, 0)            // 3
Int(3.9)                     // 3
Abs(-15)                     // 15
Sqrt(144)                    // 12
Power(2, 10)                 // 1024
Mod(10, 3)                   // 1
Rand()                       // Random decimal 0 to 1
RandBetween(1, 100)          // Random integer

Practical uses I reach for:

Alternating row colors in a gallery:

If(Mod(ThisItem.RowNumber, 2) = 0, RGBA(245,245,245,1), Color.White)

Currency rounding before saving:

Round(Value(txtPrice.Text) * 1.2, 2)

Percentage of a total:

Round(ThisItem.Amount / Sum(Sales, Amount) * 100, 1) & "%"

Progress bar width:

Min(1, CountIf(Tasks, Status = "Done") / CountRows(Tasks)) * Parent.Width

9. User, Notification, and Utility Functions

User

User().FullName
User().Email
User().Image

I set these once in App.OnStart so I’m not calling the connector repeatedly:

Set(varUser, User());
Set(varUserEmail, Lower(User().Email))

Always lowercase emails before comparing them. SharePoint isn’t consistent about casing, and case-sensitive comparisons will silently fail.

Notify

Notify("Saved successfully", NotificationType.Success)
Notify("Something went wrong", NotificationType.Error)
Notify("Please check your input", NotificationType.Warning)
Notify("Loading data...", NotificationType.Information, 2000)

The fourth argument is the timeout in milliseconds. Error notifications stay on screen until dismissed unless you specify a duration.

Errors and IfError

Errors(Employees)                     // All errors for a data source
Errors(Employees, Gallery1.Selected)  // Errors for one record

Display them in a label:

Concat(Errors(Employees), Message, " | ")

Concat

Joins a table column into a single string.

Concat(Gallery1.AllItems, Title, ", ")
Concat(cmbSkills.SelectedItems, Value, "; ")

I use this for showing multi-select values as readable text.

Refresh and Reset

Refresh(Employees)         // Pull fresh data from the source
Reset(txtSearch)           // Reset one control to its default
ResetForm(frmEmployee)     // Reset an entire form

Set Focus and Trace

SetFocus(txtName)                       // Move cursor to a control
Trace("User submitted form", TraceSeverity.Information)   // Log to App Insights

SetFocus is a small thing that makes a big accessibility difference. After validation fails, focus the offending field:

If(
    IsBlank(txtName.Text),
    SetFocus(txtName); Notify("Name is required", NotificationType.Error),
    SubmitForm(frmEmployee)
)

10. Delegation: The Most Important Concept You’ll Learn

I saved this for its own section because it’s the difference between an app that works with 50 records and one that works with 50,000.

What Delegation Actually Means

When your data source can handle a query itself, Power Apps delegates the work to it. SharePoint does the filtering on the server and sends back only the matching rows. Fast, accurate, scalable.

When a function can’t be delegated, Power Apps downloads a chunk of your data (default 500 rows, max 2,000) and processes it locally. Everything beyond that limit is silently ignored.

That word — silently — is the killer. Your app doesn’t crash. It just shows wrong results, and you might not notice for months.

How to Spot Delegation Warnings

Power Apps shows a blue underline and a warning triangle next to formulas that can’t be delegated. Never ignore these. In a test app with 30 records everything looks perfect, which is exactly why this bug ships to production so often.

Delegable vs. Non-Delegable with SharePoint

✅ Delegable	❌ Not Delegable
Filter (with supported operators)	Search
LookUp	in operator
Sort / SortByColumns	First / FirstN / Last
StartsWith	Distinct
=, <>, >, <, >=, <=	GroupBy
And / Or / Not	AddColumns / DropColumns
IsBlank (on some columns)	Sum / Average / Max / Min (SharePoint)
Sum / Average (Dataverse)	CountRows / CountIf (SharePoint)
TrimEnds	Len, Left, Mid, Right

Also worth knowing: SharePoint can’t delegate on calculated columns, multi-select choice columns, or person columns beyond basic equality. If you’re filtering heavily on a column, keep it a simple text, number, date, or single choice column.

Five Patterns I Use to Work Around Delegation

1. Filter first, transform second.

// ❌ Bad — AddColumns breaks delegation for everything after it
Filter(AddColumns(Orders, "Total", Qty * Price), Region = "West")

// ✅ Good — delegable filter runs first, then transform the small result
AddColumns(Filter(Orders, Region = "West"), "Total", Qty * Price)

2. Use StartsWith instead of in.

// ❌ Not delegable
Filter(Employees, txtSearch.Text in FullName)

// ✅ Delegable
Filter(Employees, StartsWith(FullName, txtSearch.Text))

3. Narrow with a delegable filter, then search locally.

Search(
    Filter(Employees, Department = drpDept.Selected.Value),
    txtSearch.Text,
    "FullName"
)

If any single department has under 2,000 people, this is completely safe.

4. Cache small reference tables in collections.

Lookup lists — departments, categories, statuses — rarely exceed a few hundred rows. Load them once in App.OnStart and query them locally, where delegation limits don’t apply at all:

ClearCollect(colDepartments, Departments);
ClearCollect(colCategories, Categories);
ClearCollect(colStatuses, Choices(Tasks.Status))

Then in your dropdowns and lookups:

// Dropdown Items
Sort(colDepartments, Title)

// Fast local lookup - no network call
LookUp(colDepartments, Title = drpDept.Selected.Value, ManagerEmail)

Collections live in memory, so Filter, Search, GroupBy, and Sum all work perfectly on them regardless of size. Just be careful not to cache large transactional tables — that defeats the purpose and slows down your app’s startup.

5. Raise your data row limit (with caution).

Go to Settings → General → Data row limit for non-delegable queries and increase it up to 2,000. This is a band-aid, not a fix. It slows your app down and still breaks at 2,001 records. I only use it when I know a table will never exceed a couple thousand rows.

For a complete treatment of this topic, read my full guide on Power Apps delegation and how to handle the 500 item limit in Power Apps.

Real-World Examples of Power Apps Functions

Theory is fine, but let me show you complete, working scenarios you can adapt today. These are patterns I use in nearly every production app I build.

Example 1: A Complete Searchable, Filterable Gallery

This is the single most requested thing in Power Apps, and once you get this pattern down you’ll reuse it forever.

Screen setup: a text input (txtSearch), a dropdown (drpStatus), a date picker (dtpFrom), and a gallery (galTasks).

Dropdown Items:

Ungroup(
    Table(
        { Options: Table({ Value: "All" }) },
        { Options: Choices(Tasks.Status) }
    ),
    "Options"
)

That adds an “All” option to the top of your real choices without hardcoding them.

Gallery Items:

SortByColumns(
    Search(
        Filter(
            Tasks,
            (drpStatus.Selected.Value = "All" || Status.Value = drpStatus.Selected.Value)
            && Created >= dtpFrom.SelectedDate
            && AssignedTo.Email = varUserEmail
        ),
        txtSearch.Text,
        "Title",
        "Description"
    ),
    "DueDate",
    SortOrder.Ascending
)

Here’s why this works: the Filter runs on the SharePoint server (delegable), and the Search runs locally on whatever comes back. As long as any one user’s filtered set stays under 2,000 rows, you’re completely safe.

A “no results” message. Add a label with:

// Text
"No tasks match your filters."

// Visible
CountRows(galTasks.AllItems) = 0

A clear-filters button:

Reset(txtSearch);
Reset(drpStatus);
Reset(dtpFrom)

For more variations, see my guides on filtering a Power Apps gallery by dropdown and searching a gallery in Power Apps.

Example 2: Form Validation Before Saving

Never trust user input. Here’s my standard validation pattern.

Save button DisplayMode:

If(
    !IsBlank(Trim(txtName.Text))
    && IsMatch(txtEmail.Text, Match.Email)
    && !IsBlank(drpDept.Selected.Value)
    && Value(txtSalary.Text) > 0,
    DisplayMode.Edit,
    DisplayMode.Disabled
)

Inline error label under the email field:

// Text
If(
    !IsBlank(txtEmail.Text) && !IsMatch(txtEmail.Text, Match.Email),
    "Please enter a valid email address",
    ""
)

// Color
Color.Red

// Visible
Len(Self.Text) > 0

IsMatch is worth knowing about — it validates text against a pattern. Built-in patterns include Match.Email, Match.Digit, Match.Letter, Match.MultipleDigits, and you can write your own regular expressions too:

IsMatch(txtPhone.Text, "^\d{3}-\d{3}-\d{4}$")
IsMatch(txtZip.Text, "^\d{5}(-\d{4})?$")

Save button OnSelect with full error handling:

Set(varSaving, true);
IfError(
    Patch(
        Employees,
        Defaults(Employees),
        {
            Title: Trim(txtName.Text),
            Email: Lower(Trim(txtEmail.Text)),
            Department: { Value: drpDept.Selected.Value },
            Salary: Value(txtSalary.Text),
            HireDate: dtpHire.SelectedDate
        }
    ),
    Notify("Save failed: " & FirstError.Message, NotificationType.Error);
    Set(varSaving, false),
    // Success path
    Notify("Employee added successfully", NotificationType.Success);
    Reset(txtName); Reset(txtEmail); Reset(txtSalary);
    Set(varSaving, false);
    Back()
)

Notice FirstError.Message — that gives you the actual error text from the data source, which is far more useful than a generic message when you’re troubleshooting.

Loading spinner. Add a Spinner (or an image) with Visible set to varSaving. Small touch, big perceived-quality improvement.

Example 3: A Cascading Dropdown

Country → State → City. Classic requirement, and it’s simpler than most people expect.

Country dropdown Items:

Distinct(colLocations, Country)

State dropdown Items:

Distinct(
    Filter(colLocations, Country = drpCountry.Selected.Value),
    State
)

State dropdown DisplayMode:

If(IsBlank(drpCountry.Selected.Value), DisplayMode.Disabled, DisplayMode.Edit)

City dropdown Items:

Distinct(
    Filter(
        colLocations,
        Country = drpCountry.Selected.Value && State = drpState.Selected.Value
    ),
    City
)

Country dropdown OnChange — reset the children so you never get an orphaned selection:

Reset(drpState); Reset(drpCity)

That OnChange reset is the step everyone forgets, and it’s what causes those confusing “State says Texas but Country says Canada” bugs.

Full walkthrough in my Power Apps cascading dropdown tutorial.

Example 4: A Role-Based Security Pattern

Show and hide features based on who’s signed in.

App.OnStart:

Set(varUser, User());
Set(varUserEmail, Lower(User().Email));
ClearCollect(colPermissions, Filter(AppRoles, Lower(UserEmail) = varUserEmail));
Set(varRole, Coalesce(First(colPermissions).Role, "Viewer"));
Set(varIsAdmin, varRole = "Admin")

Then throughout your app:

// Delete icon Visible
varIsAdmin

// Edit button DisplayMode
If(varRole in ["Admin", "Editor"], DisplayMode.Edit, DisplayMode.View)

// Admin screen navigation - hide entirely
varIsAdmin

Two important caveats. First, this is UI-level security only — it hides buttons, it doesn’t protect data. Real security has to be enforced at the SharePoint or Dataverse permission level. Second, always give a sensible default role ("Viewer" above) so a missing permission record doesn’t accidentally grant access.

Example 5: A Shopping Cart with Collections

This demonstrates collections, Patch, and ForAll working together.

Add to cart:

If(
    ThisItem.ProductID in colCart.ProductID,
    // Already in cart - increase quantity
    Patch(
        colCart,
        LookUp(colCart, ProductID = ThisItem.ProductID),
        { Qty: LookUp(colCart, ProductID = ThisItem.ProductID, Qty) + 1 }
    ),
    // New item
    Collect(
        colCart,
        {
            ProductID: ThisItem.ProductID,
            ProductName: ThisItem.Title,
            Price: ThisItem.Price,
            Qty: 1
        }
    )
);
Notify($"{ThisItem.Title} added to cart", NotificationType.Success, 1500)

Cart total label:

Text(Sum(colCart, Price * Qty), "[$-en-US]$#,##0.00")

Item count badge:

Sum(colCart, Qty)

Remove an item:

RemoveIf(colCart, ProductID = ThisItem.ProductID)

Submit the whole order in one efficient call:

With(
    { newOrder: Patch(Orders, Defaults(Orders), {
        Title: $"ORD-{Text(Now(), "yyyymmddhhmmss")}",
        CustomerEmail: varUserEmail,
        OrderTotal: Sum(colCart, Price * Qty)
    })},
    Patch(
        OrderLines,
        ForAll(
            colCart,
            {
                OrderID: newOrder.ID,
                Product: ProductName,
                Quantity: Qty,
                UnitPrice: Price
            }
        )
    )
);
Clear(colCart);
Notify("Order placed successfully!", NotificationType.Success);
Navigate(ConfirmationScreen)

That With() wrapper is doing something clever — it creates the parent order, captures the new record (including its auto-generated ID), and immediately uses that ID for all the child line items. And because I’m passing a table into Patch rather than looping Patch inside ForAll, all the line items get created in a single batched operation instead of one call per row.

Example 6: A Summary Dashboard

Four tiles built entirely with functions.

// Total open tasks
CountIf(colTasks, Status.Value = "Open")

// Overdue count
CountIf(colTasks, DueDate < Today() && Status.Value <> "Complete")

// Completion percentage
Round(
    CountIf(colTasks, Status.Value = "Complete") / Max(CountRows(colTasks), 1) * 100,
    0
) & "%"

// Average days to close
Round(
    Average(
        Filter(colTasks, Status.Value = "Complete"),
        DateDiff(Created, Modified, TimeUnit.Days)
    ),
    1
) & " days"

That Max(CountRows(colTasks), 1) is a small defensive trick — it prevents a divide-by-zero error when the collection is empty.

A grouped summary gallery:

AddColumns(
    GroupBy(colTasks, "Owner", "OwnerTasks"),
    "TotalTasks", CountRows(OwnerTasks),
    "Completed", CountIf(OwnerTasks, Status.Value = "Complete"),
    "Overdue", CountIf(OwnerTasks, DueDate < Today() && Status.Value <> "Complete")
)

One formula, a complete team report.

Common Power Apps Function Errors (And How I Fix Them)

I’ve hit every one of these. Here’s the quick diagnosis table.

Error Message	What It Actually Means	The Fix
“Behavior function in a non-behavior property”	You used Set, Patch, Navigate, etc. in Text/Visible/Items	Move it to OnSelect, OnStart, OnChange, or OnVisible
“Invalid argument type. Expecting Text but received Number”	Type mismatch	Wrap with Text() or Value()
“Name isn’t valid. This identifier isn’t recognized”	Typo, or the control/variable doesn’t exist yet	Check spelling; variables only appear after their Set runs once
“The function expects a record value”	You gave Patch or UpdateContext a bare value	Wrap it in curly braces: { Name: "x" }
“Delegation warning” (blue underline)	Query runs locally on 500–2,000 rows only	Restructure using the delegation patterns above
“Network error when using Patch”	Column type mismatch, or a required field is empty	Check choice/person/lookup syntax; use Errors(DataSource) to see details
“Incompatible types for comparison”	Comparing text to a choice or date to text	Use .Value on choices, DateValue() on text dates
Choice column shows blank after patch	You patched a plain string	Use { Value: "Approved" }
Formula works in Studio but fails when published	A variable set in OnStart didn’t run, or permissions differ	Test with Play mode, not just edit mode; check data source permissions

One extra tip: press Ctrl+Shift+F (or use the format button) to auto-format any formula in the bar. On a nested Filter with three conditions, this alone will save you a lot of squinting.

Power Apps Function Best Practices I Follow

After building a lot of these apps, here’s what I’d tell my past self.

1. Name variables with a prefix. I use var for globals, loc for context variables, and col for collections. When you’re staring at a formula six months later, varSelectedEmployee tells you everything; emp tells you nothing.

2. Keep App.OnStart lean. Every line here delays your app’s launch. Load only what’s needed on the first screen, and move the rest to individual screens’ OnVisible. Better yet, use the StartScreen property instead of Navigate() in OnStart — Microsoft recommends this and it’s noticeably faster.

3. Use With() to avoid repeating yourself. If the same sub-expression appears twice in a formula, pull it into a With. Cleaner, faster, easier to debug.

4. Format your formulas across multiple lines. A one-line, 300-character Filter is technically valid and practically unmaintainable. Indent your nested functions.

5. Prefer Patch over SubmitForm for complex scenarios. Forms are great for simple CRUD. The moment you need conditional logic, multi-source writes, or custom validation, Patch gives you full control.

6. Always handle the empty state. Every gallery needs a “no results” message. Every division needs a zero-guard. Every LookUp needs a Coalesce fallback.

7. Check delegation on day one, not day one hundred. Build with 2,000+ test records if that’s what production will look like.

8. Use Concurrent() for independent operations. If you’re loading three unrelated collections at startup, run them in parallel:

Concurrent(
    ClearCollect(colDepts, Departments),
    ClearCollect(colProjects, Projects),
    ClearCollect(colStatuses, Choices(Tasks.Status))
)

This can genuinely halve your load time.

9. Add comments to complex formulas. Power Fx supports both styles:

// Single line comment

/* 
   Multi-line comment
   for longer explanations
*/

10. Test as a real user. Studio runs as you, with your permissions. Publish and test with a colleague’s account before you call it done.

Frequently Asked Questions

Conclusion

Power Apps functions are the difference between a static screen and an application that actually does something. Everything in this guide comes down to a handful of core ideas:

• Value functions calculate; behavior functions act. Know which property you’re in.
• Filter, LookUp, and Search handle almost every data-retrieval need you’ll have.
• Patch and Collect give you full control over writing data.
• Variables and collections hold state — global, local, and tabular.
• Delegation is the concept that separates apps that scale from apps that quietly break.
• With() and good formatting keep complex formulas readable six months from now.

My advice: don’t try to memorize all of this. Bookmark it, build something real, and come back when you hit a wall. That’s genuinely how I learned — one broken gallery at a time.

Start with the searchable gallery from Example 1. It uses Filter, Search, SortByColumns, and delegation-safe structure all in one formula. Get that working with your own data, and you’ll have covered more ground than any tutorial can give you.

You can also browse Microsoft’s official formula reference for the complete, always-current list of every function and its parameters.