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

1. On the Build tab, select See all, and then expand Tables.
2. From the list of tables in Power Apps app for Teams, select the table you want.
3. 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.
4. 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.

Abs

And

Average

Blank

Char

Concatenate

DateAdd

DateDiff

Day

EndsWith

Exp

Hour

If

IfError

Int

IsBlank

IsError

ISOWeekNum

IsUTCToday

Left

Len

Ln

Lower

Max

Mid

Min

Minute

Mod

Month

Not

Or

Power

Replace

Right

Round

RoundDown

RoundUp

Second

Sqrt

StartsWith

Substitute

Sum

Switch

Text *

Trim

Trunc

TrimEnds

Upper

UTCNow

UTCToday

Value *

Weekday

WeekNum

Year

* 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.

See also

Work with table columns
Formula reference for Power Apps