Skip to content
Tutorials Center
My learning

Topics

Lesson 17 of 38 ยท Data and Dataverse

Filter, Search, Sort and Patch

Learn the core data functions in Power Apps: Filter, LookUp, Search, SortByColumns, AddColumns, GroupBy, Patch, Remove, UpdateIf and error handling with IfError.

Almost every app reads data, shows part of it and writes changes back. A small set of functions covers nearly all of that. This lesson explains each one with examples, followed by running stand-ins that reproduce the results.

Reading data

Filter

TEXT
Filter(Tasks, Done = false, Owner = User().FullName)

Returns a table of rows where all conditions are true. Fields can be used without table prefix inside the formula.

LookUp

TEXT
LookUp(Tasks, ID = 5)                    // the whole record
LookUp(Tasks, ID = 5, Title)             // just one field

Returns the first matching record (or one value). First(table) and Last(table) are similar.

TEXT
Search(Tasks, txtSearch.Text, "Title", "Description")

Case-insensitive substring match on one or more text columns. Delegation depends on the source.

SortByColumns and Sort

TEXT
SortByColumns(Tasks, "Due", SortOrder.Ascending, "Title", SortOrder.Ascending)
Sort(Tasks, Due, SortOrder.Descending)

SortByColumns is usually more delegable and accepts column names as strings.

AddColumns, ShowColumns, GroupBy, Distinct

TEXT
AddColumns(Orders, "Total", Qty * Price)
ShowColumns(Orders, "Item", "Qty")
GroupBy(Orders, "Customer", "Lines")
Distinct(Orders, Customer)

These shape tables in memory and are mostly not delegable.

The stand-in: read functions

JS Stand-in: Filter, LookUp, SortByColumns, Distinct

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Writing data with Patch

Patch creates and updates records:

TEXT
// Create
Patch(Tasks, Defaults(Tasks), {Title: "New", Owner: User().FullName})

// Update one record
Patch(Tasks, LookUp(Tasks, ID = 2), {Done: true})

// Update using the gallery row
Patch(Tasks, ThisItem, {Done: !ThisItem.Done})

// Create many in one call
Patch(Tasks, ForAll(colNew, {Title: Title, Owner: Owner}))

Patch returns the saved record, including generated IDs, so you can chain:

TEXT
Set(varSaved, Patch(Tasks, Defaults(Tasks), {Title: txtTitle.Text}));
Navigate(scrDetails, ScreenTransition.None, {task: varSaved})

Other writing functions

  • Collect(Source, record) adds records; with a data source it creates rows.
  • Remove(Source, record) and RemoveIf(Source, condition) delete.
  • Update(Source, old, new) replaces a record fully; unmentioned columns become blank, so prefer Patch.
  • UpdateIf(Source, condition, changes) edits all rows matching a condition.

Deleting and updating many rows with RemoveIf and UpdateIf can touch large tables and is not always delegable. Prefer to select rows first, then act.

The stand-in: write functions

JS Stand-in: Patch create and update, Remove, UpdateIf

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Handling errors

Writes can fail (permission, validation, network). Use IfError or check Errors(Source):

TEXT
IfError(
  Patch(Tasks, ThisItem, {Done: true}),
  Notify("Could not update: " & FirstError.Message, NotificationType.Error)
)

Also consider Concurrent(...) to run independent reads in parallel when loading several data sources at once.

Performance tips

  • Filter on the server (delegation) and select only needed columns with ShowColumns.
  • Call Patch for one record at a time in OnSelect; for batches, build a table with ForAll and patch once.
  • Avoid doing LookUp per row of a gallery against a big table; use relationship columns (see Relationships and choices).
  • Do not put Patch inside Items or Text; those must have no side effects.

Common mistakes

  • Using Filter where one record is needed; it returns a table, not a record. Use LookUp or First(Filter(...)).
  • Patch(Tasks, {ID: 5}, {...}) does not find the record; pass the actual record from LookUp.
  • Forgetting Defaults() when creating.
  • Expecting Update to merge fields like Patch.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which function returns the first record that matches a condition?

    LookUp returns one record (or a single field of it); Filter returns a table.
  2. 2. How do you create a new row with Patch?

    Defaults(source) supplies a blank record to create.
  3. 3. What is the difference between Patch and Update?

    Update replaces the whole record, blanking unlisted columns; Patch merges.
  4. 4. Which function lets you react to a failed write?

    IfError runs a fallback formula when the first one produces an error.

Summary

  • Read with Filter, LookUp, Search, SortByColumns, Distinct, AddColumns.
  • Write with Patch (create and update), Remove, RemoveIf, UpdateIf.
  • Patch returns the saved record; handle failures with IfError.
  • Keep queries delegable and writes small.

Next: Relationships and choices