Work with formula table columns (preview)
[This topic is pre-release documentation and is subject to change.] More information: Power Apps preview program
Formula columns are a data type in Microsoft Dataverse for Teams that are built on Power Fx. You can add a formula column to a table in real time. The Dataverse table stores the logic and gives you the values during fetch operations. Formula columns use the Power Fx syntax that's similar to Office Excel. As you enter the formula, Intellisense helps you with recommendations for formula, syntax, and errors.
Note
Formula columns can be added as a calculated field. Currently, formula columns can't be used in roll-up fields or with plugins.
Add a formula column
- On the Build tab, select See all, and then expand Tables.
- From the list of tables in Power Apps app for Teams, select the table you want.
- Select Add column or select Edit data > Add column. When you select Edit data, you can also select + on the column where you want to add the formula column.
- In the Add new column pane:
- Enter a Name for the column, such as Total price.
- Select Formula as the Type.
- Enter the formula in the Expression box. In this example, the Price column (Decimal data type) is multiplied by the Number of units column (Number data type). Select Create.
When you create a record, the formula column executes the formula and displays the data for the record. If the formula column value for a record doesn't update, select Refresh on the command bar to execute the formula.
Notice that the column type is determined by the formula. You can change the formula after you’ve created it if it doesn’t change the column type. For example, the formula price * discount creates a column type of number, whereas the formula First & “ “ & Last creates a column type of string. You can change a price * discount formula to price * (discount +10%) because that doesn’t change the column type. However, once saved, you can’t change the price * discount formula to Text(price * discount) because that would require changing the column type to string.
Data types
The following data types can be displayed in a formula column:
- Text
- Decimal Number
- Yes/No (boolean)
- Date
Note
The Currency data type is not currently supported.
Function types
The following function types are supported with the formulas used in a formula column:
- Decimal
- String
- Boolean
- Option Set
- DateTime (TZI)
- DateTime (User local) (limited to comparisons with other user local values and the DateAdd function)
- DateTime (Date only) (limited to comparisons with other date-only values, and the DateAdd function)
- Currency
- Whole Number, promoted to Decimal
Operators
These operators are supported with the formulas used in a formula column:
+, -, *, /, %, ^, in, exactin, &
More information: Operators in Power Apps
Available functions
The following scalar functions are available with formula columns.
Text *
Value *
* The Text and Value functions only work with whole numbers, where no decimal separator is involved. Since the decimal separator varies across locales, and the formula columns are evaluated without locale knowledge, there's no way to properly interpret or generate the decimal separator.