DEV Community

Sofiane Mebchour
Sofiane Mebchour

Posted on

The Power Fx Formulas You Actually Use Every Day (With French Locale Gotchas)

#powerapps #powerplatform #powerfx #lowcode

Every Power Apps maker ends up googling the same formulas over and over. This is the cheatsheet I wish I had — organized by what you're actually trying to do, not alphabetically: filtering galleries, validating forms, saving data with Patch, dates, state, and (the part most tutorials skip) the French locale traps that silently break copy-pasted formulas.

And because half my projects run in French environments: the locale gotchas that silently break copy-pasted formulas, which almost no English tutorial mentions.

1. Filtering a gallery (the big one) 

// Basic filter
Filter(Orders, Status = "Open")

// Search-as-you-type (delegable)
Filter(Orders, StartsWith(CustomerName, SearchInput.Text))

// Combine search + dropdown filter
Filter(Orders,
  StartsWith(CustomerName, SearchInput.Text),
  ddStatus.Selected.Value = "All" || Status = ddStatus.Selected.Value
)

// Sort the result
SortByColumns(Filter(Orders, Status = "Open"), "OrderDate", SortOrder.Descending)
Enter fullscreen mode Exit fullscreen mode

Delegation tip: prefer StartsWith() over Search() and in on large sources — non-delegable functions silently cap your results at 500-2000 rows.

2. Validating a form 

// Required field + email shape
If(
  IsBlank(txtEmail.Text) || !IsMatch(txtEmail.Text, Match.Email),
  Notify("Please enter a valid email", NotificationType.Error),
  SubmitForm(EditForm1)
)

// Disable the submit button until valid
DisplayMode: If(
  !IsBlank(txtName.Text) && IsMatch(txtEmail.Text, Match.Email),
  DisplayMode.Edit,
  DisplayMode.Disabled
)
Enter fullscreen mode Exit fullscreen mode

Key functions: IsBlank(), IsMatch(), IsError(), Coalesce() (first non-blank value).

3. Saving data with Patch 

// Create a record
Patch(Orders, Defaults(Orders), {
  Title: txtTitle.Text,
  Quantity: Value(txtQty.Text),
  DueDate: dpDue.SelectedDate
})

// Update the selected gallery item
Patch(Orders, galOrders.Selected, { Status: "Closed" })

// Create or update in one formula (upsert)
Patch(Orders,
  Coalesce(LookUp(Orders, ID = varID), Defaults(Orders)),
  { Title: txtTitle.Text }
)
Enter fullscreen mode Exit fullscreen mode

The #1 Patch trap: type mismatches. Value(), Text(), DateValue() are your friends — a text input is always text, even if it looks like a number.

4. Dates without tears 

Today()                              // date only
Now()                                // date + time
Text(Today(), "dd/mm/yyyy")          // format for display
DateAdd(Today(), -30, TimeUnit.Days) // 30 days ago
DateDiff(StartDate, EndDate, TimeUnit.Days)
Enter fullscreen mode Exit fullscreen mode

5. Variables and collections 

Set(varUser, User().Email)                   // global variable
UpdateContext({locShowModal: true})          // screen variable
ClearCollect(colItems, Filter(Orders, ...))  // snapshot a query
With({t: Filter(Orders, Status="Open")},     // scoped, no variable at all
  CountRows(t) & " open / " & Sum(t, Amount) & " €"
)
Enter fullscreen mode Exit fullscreen mode

With() is the most underused function in Power Apps — it replaces most "temporary variable" needs and keeps formulas readable.

6. The French locale gotchas 🇫🇷

If your formula works in a US tenant and throws syntax errors in a French one, here's why:

Argument separators change. In fr-FR, Power Fx uses ; between arguments (because , is the decimal separator):

// en-US
Filter(Orders, Status = "Open", Amount > 100)

// fr-FR — same formula
Filter(Orders; Status = "Open"; Amount > 100)
Enter fullscreen mode Exit fullscreen mode

Chained statements use ;; in French where English uses ;:

// en-US:  Set(x, 1); Notify("done")
// fr-FR:  Set(x; 1);; Notify("terminé")
Enter fullscreen mode Exit fullscreen mode

Decimals flip too: 0.5 (EN) becomes 0,5 (FR) inside formulas.

So a YAML snippet or blog formula written for EN will not paste cleanly into an FR-locale Studio session. Either convert the separators, or keep two variants of your snippets. (I wrote a small converter as part of PowerBlocks — the cheatsheet tool shows every formula in both EN and FR syntax, and it's free.)

The full searchable list

This article covers the formulas I use daily, but the full reference — 80+ functions with EN/FR examples, searchable and filterable by category, one-click copy — lives here: Expression Cheatsheet on PowerBlocks (free, no login). There's a Power Automate equivalent too.

What's the formula you always have to look up? Drop it in the comments — I'll add the most requested ones to the cheatsheet.

Top comments (1)

Collapse
 
alexshev profile image
Alex Shev •

The locale examples are especially valuable because formulas often move between makers by copy-paste. I’d put a tiny test screen beside the cheatsheet that exercises separators, dates, and decimal values against the target environment before a formula reaches a production form.