NOTE: Microsoft Power Fx is the new name for canvas apps formula language. These articles are work in progress as we extract the language from canvas apps, integrate it with other products of the Power Platform, and make available as open source. Start with the Power Fx Overview for an introduction to the language.
Microsoft Power Fx operators are described in the following table. Some of these operators are dependent on the language of the author. See Global apps for more information.
| Symbol | Type | Syntax | Description |
|---|---|---|---|
| . | Property Selector | Slider1.Value Color.Red |
Extracts a property from a table, control, or enumeration. For backward compatibility, ! can be used. |
| . [language dependent] |
Decimal separator | 1.23 | Separator between whole and fractional parts of a number. The character depends on the language. |
| ( ) | Parentheses | Filter(T, A < 10) (1 + 2) * 3 |
Enforces precedence order, and groups sub expressions in a larger expression |
| + | Arithmetic operators | 1 + 2 | Addition |
| - | 2 - 1 | Subtraction and sign | |
| * | 2 * 3 | Multiplication | |
| / | 2 / 3 | Division (also see the Mod function) | |
| ^ | 2 ^ 3 | Exponentiation, equivalent to the Power function | |
| % | 20% | Percentage (equivalent to "* 1/100") | |
| = | Comparison operators | Price = 100 | Equal to |
| > | Price > 100 | Greater than | |
| >= | Price >= 100 | Greater than or equal to | |
| < | Price < 100 | Less than | |
| <= | Price <= 100 | Less than or equal to | |
| <> | Price <> 100 | Not equal to | |
| & | String concatenation operator | "hello" & " " & "world" | Makes multiple strings appear continuous |
| && or And | Logical operators | Price < 100 && Slider1.Value = 20 or Price < 100 And Slider1.Value = 20 |
Logical conjunction, equivalent to the And function |
| || or Or | Price < 100 || Slider1.Value = 20 or Price < 100 Or Slider1.Value = 20 | Logical disjunction, equivalent to the Or function | |
| ! or Not | !(Price < 100) or Not (Price < 100) | Logical negation, equivalent to the Not function | |
| exactin | Membership operators | Gallery1.Selected exactin SavedItems | Belonging to a collection or a table |
| exactin | "Windows" exactin “To display windows in the Windows operating system...” | Substring test (case-sensitive) | |
| in | Gallery1.Selected in SavedItems | Belonging to a collection or a table | |
| in | "The" in "The keyboard and the monitor..." | Substring test (case-insensitive) | |
| @ | Disambiguation operator | MyTable[@fieldname] | Field disambiguation |
| @ | [@MyVariable] | Global disambiguation | |
| , [language dependent] |
List separator | If(X < 10, "Low", "Good") { X: 12, Y: 32 } [ 1, 2, 3 ] |
Separates: This character depends on the language. |
| ; [language dependent] |
Formula chaining | Collect(T, A); Navigate(S1, "") | Separate invocations of functions in behavior properties. The chaining operator depends on the language. |
| As | As operator | AllCustomers As Customer | Overrides ThisItem and ThisRecord in galleries and record scope functions. As is useful for providing a better, specific name and is especially important in nested scenarios. |
| Self | Self operator | Self.Fill | Access to properties of the current control |
| Parent | Parent operator | Parent.Fill | Access to properties of a control container |
| ThisItem | ThisItem operator | ThisItem.FirstName | Access to fields of a Gallery or form control |
| ThisRecord | ThisItem operator | ThisRecord.FirstName | Access to the complete record and individual fields of the record within ForAll, Sum, With, and other record scope functions. Can be overridden with the As operator. |
Use the in and exactin operators to find a string in a data source, such as a collection or an imported table. The in operator identifies matches regardless of case, and the exactin operator identifies matches only if they're capitalized the same way. Here's an example:
-
Create or import a collection named Inventory, and show it in a gallery, as the first procedure in Show images and text in a gallery describes.
-
Set the Items property of the gallery to this formula:
Filter(Inventory, "E" in ProductName)The gallery shows all products except Callisto because the name of that product is the only one that doesn't contain the letter you specified.
-
Change the Items property of the gallery to this formula:
Filter(Inventory, "E" exactin ProductName)The gallery shows only Europa because only its name contains the letter that you specified in the case that you specified.
A few controls and functions apply formulas to individual records of a table. To refer to the individual record in a formula, use one of the following:
| Operator | Applies to | Description |
|---|---|---|
| ThisItem | Gallery control Edit form control Display form control |
The default name for the current record in a Gallery or form control. |
| ThisRecord | ForAll, Filter, With, Sum and other record scope functions | The default name for the current record in ForAll and other record scope functions. |
| As name | Gallery control ForAll, Filter, With, Sum and other record scope functions |
Defines a name for the current record, replacing default ThisItem or ThisRecord. Use As to make formulas easier to understand and resolve ambiguity when nesting. |
For example, in the following Gallery control, the Items property is set to the Employees data source (such as the Employees entity included with the Northwind Traders sample):
Employees
The first item in the gallery is a template that is replicated for each employee. In the template, the formula for the picture uses ThisItem to refer to the current item:
ThisItem.Picture
Likewise, the formula for the name also uses ThisItem:
ThisItem.'First Name' & " " & ThisItem.'Last Name'
ThisRecord is used in functions that have a record scope. For example, we can use the Filter function with our gallery's Items property to only show first names that being with M:
Filter( Employees, StartsWith( ThisRecord.Employee.'First Name', "M" ) )
ThisRecord is optional and implied by using the fields directly, for example, in this case, we could have written:
Filter( Employees, StartsWith( 'First Name', "M" ) )
Although optional, using ThisRecord can make formulas easier to understand and may be required in ambiguous situations where a field name may also be a relationship name. ThisRecord is optional while ThisItem is always required.
Use ThisRecord to reference the whole record with Patch, Collect, and other record scope functions. For example, the following formula sets the status for all inactive employees to active:
With( { InactiveEmployees: Filter( Employees, Status = 'Status (Employees)'.Inactive ) },
ForAll( InactiveEmployees,
Patch( Employees, ThisRecord, { Status: 'Status (Employees)'.Active } ) ) )
Use the As operator to name a record in a gallery or record scope function, overriding the default ThisItem or ThisRecord. Naming the record can make your formulas easier to understand and may be required in nested situations to access records in other scopes.
For example, you can modify the Items property of our gallery to use As to identify that we are working with an Employee:
Employees As Employee
The formulas for the picture and name are adjusted to use this name for the current record:
Employee.Picture
Employee.'First Name' & " " & Employee.'Last Name'
As can also be used with record scope functions to replace the default name ThisRecord. We can apply this to our previous example to clarify the record we are working with:
With( { InactiveEmployees: Filter( Employees, Status = 'Status (Employees)'.Inactive ) },
ForAll( InactiveEmployees As Employee,
Patch( Employees, Employee, { Status: 'Status (Employees)'.Active } ) ) )
When nesting galleries and record scope functions, ThisItem and ThisRecord always refers to the inner most scope, leaving records in outer scopes unavailable. Use As to make all record scopes available by giving each a unique name.
For example, this formula produces a chessboard pattern as a text string by nesting two ForAll functions:
Concat(
ForAll( Sequence(8) As Rank,
Concat(
ForAll( Sequence(8) As File,
If( Mod(Rank.Value + File.Value, 2) = 1, " X ", " . " )
),
Value
) & Char(10)
),
Value
)
Setting a Label control's Text property to this formula displays:
Let's unpack what is happening here:
- We start by iterating an unnamed table of 8 numbered records from the Sequence function. This loop is for each row of the board, which is commonly referred to as Rank and so we give it this name.
- For each row, we iterate another unnamed table of 8 columns, and we give the common name File.
- If Rank.Value + File.Value is an odd number, the square gets an X, otherwise a dot. This part of the formula is referencing both ForAll loops, made possible by using the As operator.
- Concat is used twice, first to assemble the columns and then the rows, with a Char(10) thrown in to create a new line.
A similar example is possible with nested Gallery controls instead of ForAll functions. Let's start with the vertical gallery for the Rank. This gallery control will have an Items formula of:
Sequence(8) as Rank
Within this gallery, we'll place a horizontal gallery for the File, that will be replicated for each Rank, with an Items property of:
Sequence(8) as File
And finally, within this gallery, we'll add a Label control that will be replicated for each File and each Rank. We'll size it to fill the entire space and use the Fill property to provide the color with this formula:
If( Mod( Rank.Value + File.Value, 2 ) = 1, Green, Beige )
There are three ways to refer to a control and its properties within a formula:
| Method | Description |
|---|---|
| By control name | Any control can be referenced by name from anywhere within the app. For example, Label1.Fill refers to the fill property of the control who's name is Label1. |
| Self operator | It's often convenient to reference another property of the same control when writing a formula. Instead of using an absolute reference by name, it's easier and more portable to use a relative reference to oneself. The Self operator provides that easy access to the current control. For example, Self.Fill refers to the fill color of the current control. |
| Parent operator | Some controls host other controls, such as the Screen and Gallery controls. The hosting control of the controls within it is called the parent. Like the Self operator, the Parent operator provides an easy relative reference to the container control. For example, Parent.Fill refers to the fill property of the control that is the container for the current control. |
Self and Parent are operators and not properties on the controls themselves. Referring to Parent.Parent, Self.Parent or Parent.Self is not supported.
The names of variables, data sources, columns, and other objects can contain any Unicode.
Use single quotes around a name that contains a space or other special character.
Use two single quotes together to represent one single quote in the name. Names that do not contain special characters do not require single quotes.
Here are some example column names you might encounter in a table, and how they are represented in a formula:
| Column name in a database | Column reference in a formula |
|---|---|
| SimpleName | SimpleName |
| NameWith123Numbers | NameWith123Numbers |
| Name with spaces | 'Name with spaces' |
| Name with "double" quotes | 'Name with "double" quotes' |
| Name with 'single' quotes | 'Name with ''single'' quotes' |
| Name with an @ at sign | 'Name with an @ at sign' |
Double quotes are used to designate text strings.
Some data sources such as SharePoint and Microsoft Dataverse have two different names to refer to the same table or column of data:
-
Logical name - A name that is guaranteed to be unique, does not change after being created, usually does not allow spaces or other special characters, and is not localized into different languages. As a result, the name can be cryptic. These names are used by professional developers. For example, cra3a_customfield. This name may also be referred to as schema name or just name.
-
Display name - A name that is user-friendly and intended to be seen by end users. This name may not be unique, may change over time, may contain spaces and any Unicode character, and may be localized into different languages. Corresponding to the example above, the display name may be Custom Field with space in between the words.
Since display names are easier to understand, Power Fx will suggest them as choices and not suggest logical names. Although logical names are not suggested, they can still be used if typed indirectly.
For example, imagine you have added a Custom Field to an entity in Dataverse. A logical name will be assigned for you by the system, which you can modify only when creating the field. The result would look similar to:
When authoring a reference to a field of Accounts, the suggestion will be made to use 'Custom Field' since this is the display name. The single quotes must be used because this name has a space in it:
After selecting the suggestion, 'Custom Field' is shown in the formula bar and the data is retrieved:
Although it is not suggested, we could also use the logical name for this field. This will result in the same data being retrieved. No single quotes are required since this name does not contain spaces or special characters:
Behind the scenes, a mapping is maintained between the display names seen in formulas and the underlying logical names. Since logical names must be used to interact with the data source, this mapping is used to convert from the current display name to the logical name automatically and that is what is seen in the network traffic. This mapping is also used to convert back to logical names to switch into new display names, for example, if a display name changes or a maker in a different language edits the app.
NOTE: Logical names are not translated when moving an app between environments. For Dataverse system entity and field names, this should not be a problem as logical names are consistent across environments. But any custom fields, such as cra3a_customfield in this example above, may have a different environment prefix (cra3a in this case). Display names are preferred as they can be matched against display names in the new environment.
Since display names are not unique, the same display name may appear more than once in the same entity. When this happens, the logical name will be added to the end of the display name in parenthesis for one of more of the conflicting names. Building on the example above, if there was a second field with the same display name of Custom Field with a logical name of cra3a_customfieldalt then the suggestions would show:
Name disambiguation strings are added in other situations where name conflicts occur, such as the names of entities, option sets, and other Dataverse items.
Some functions create record scopes for accessing the fields of table while processing each record, such as Filter, AddColumns, and Sum. Field names added with the record scope override the same names from elsewhere in the app. When this happens, you can still access values from outside the record scope with the @ disambiguation operator:
- To access values from nested record scopes, use the @ operator with the name of the table being operated upon using this pattern:
Table[@FieldName] - To access global values, such as data sources, collections, and context variables, use the pattern [@ObjectName] (without a table designation).
For more information and examples, see record scopes.