Microsoft Power Fx Common Expressions (Cheat Sheet)

Microsoft Power Fx Common Expressions

Introduction

Power Fx is a low-code, Excel-like programming language for canvas apps in Microsoft Power Apps. This cheat sheet provides quick reference to commonly used formulas, syntax, and patterns to accelerate your development.

---

1. Basic Syntax

• Comments:

// Single line comment
/* Multi-line comment */

• Constants:

true, false
"Text", 123, 123.45

• Variables:

Set(variableName, value); // Global variable
UpdateContext({contextVariable: value}); // Context variable

• Operators:
• Arithmetic: +, -, *, /, ^
• Comparison: =, <>, >, <, >=, <=
• Logical: And, Or, Not

---

2. Working with Data

Tables

• Create a table:

Table({Column1: Value1, Column2: Value2}, {Column1: Value3, Column2: Value4})

• Filter rows:

Filter(TableName, ColumnName = "Value")

• Sort data:

Sort(TableName, ColumnName, Ascending)

• Add a column:

AddColumns(TableName, "NewColumn", Formula)

• Drop a column:

DropColumns(TableName, "ColumnName")

• Rename a column:

RenameColumns(TableName, "OldName", "NewName")

Records

• Create a record:

{Field1: Value1, Field2: Value2}

• Access a field:

RecordName.FieldName

• Update a record:

Patch(TableName, RecordToUpdate, {FieldName: NewValue})

---

3. Functions

Text Functions

• Concatenate:

Concatenate("Hello", " ", "World")

• Convert to Uppercase/Lowercase:

Upper("text")
Lower("TEXT")

• Trim Spaces:

Trim(" text ")

• Substring:

Mid("Text", StartIndex, Length)

Math Functions

• Rounding:

Round(Number, DecimalPlaces)

• Random number:

Rand()

• Absolute value:

Abs(Number)

Logical Functions

• Conditional:

If(Condition, TrueResult, FalseResult)

• Switch:

Switch(Expression, Value1, Result1, Value2, Result2, DefaultResult)

Date and Time Functions

• Current date/time:

Now()
Today()

• Date difference:

DateDiff(StartDate, EndDate, Unit)

• Add to a date

DateAdd(Date, Number, Unit)

---

4. User Interface Functions

• Navigate between screens:

Navigate(ScreenName, Transition)

• Show a message:

Notify("Message", NotificationType)

• Refresh data:

Refresh(DataSource)

• Toggle visibility:

!BooleanVariable

---

5. Common Expressions

• Set Variables:

Set(UserName, "John Doe");
Set(UserAge, 30);

• Navigate Between Screens:

Navigate(HomeScreen, ScreenTransition.Fade);

• Update Context Variables:

UpdateContext({IsVisible: true});

• Display Conditional Text:

If(IsLoggedIn, "Welcome back!", "Please sign in.");

• Filter Records:

Filter(Products, Category = "Electronics");

• Sort Records:

Sort(Employees, Name, Ascending);

• Submit a Form:

SubmitForm(EditForm);

---

6. Complex Expressions

• Chained Filtering and Sorting:

Sort(Filter(Products, Category = "Electronics" And Price > 100), Price, Descending)

• Dynamic Gallery Items Based on Multiple Conditions:

Filter(Products, (Category = Dropdown.Selected.Value Or IsBlank(Dropdown.Selected.Value)) And (StartsWith(Name, SearchBox.Text) Or IsBlank(SearchBox.Text)))

• Cascading Dropdowns:

Filter(Subcategories, CategoryID = DropdownCategories.Selected.ID)

• Aggregate Data Example:

Sum(Filter(Sales, Region = Dropdown.Selected.Value), Amount)

• Generating a Sequential Number for Items:

ForAll(Sequence(CountRows(Products)), {Index: Value})

• Conditional Formatting for a Gallery Item:

If(ThisItem.Stock < 10, Color.Red, Color.Green)

• Custom Error Handling:

If(IsEmpty(Filter(Products, ID = InputID.Text)), Notify("Product not found!", NotificationType.Error), Navigate(ProductDetailsScreen))

---

For more detailed documentation, visit the official Microsoft Power Fx documentation.