Post
Ask Question

Fromula-Based Columns in Dataverse Tables

Formula based

Formula columns are columns that display a calculated value in a Microsoft Dataverse table. It uses Power Fx - a human-friendly programming language. These columns do not store data but return a value based on the Power Fx formula. This is similar to the calculated column in SharePoint and SQL Server.

How do you add a formula column?

  1. Sign in to Power Apps at make.powerapps.com.
  2. Select Tables, and then select the table where you want to add a formula column.
  3. Select the Columns area, and then select New column.
  4. Enter a Display name for the column. Optionally, enter a Description of the column.
  5. For Data type, select fx Formula.
  6. Type the formula or use formula suggestions.

Formula suggestion

It's a preview feature that accepts your natural language input to interpret and suggest the Power Fx formula using a GPT-based AI model.

In the Formula suggestions box, enter the formula in natural language

New Column

And paste it into the type a formula box. Select Save.

Save

Use cases

  1. Numeric calculations: Calculate mathematical values
  2. Date calculations: To calculate durations or differences between dates. Useful in scenarios like calculating the age of a ticket in the support system.
  3. Conditional formatting: Formatting based on certain conditions. Useful in scenarios like a formula column that returns "Yes" or "No" on whether a certain condition is met.
  4. Text manipulation: To manipulate text. Useful in scenarios like a formula column that replaces certain characters in the string.
  5. Aggregate functions: To perform aggregate functions like SUM, MIN, AVG, MAX, etc.
  6. Complex calculations: Can perform complex calculations that involve multiple fields and functions.

Limitations

The formula that you enter determines the column type. You can’t change a column type after the column is created. That means you can change the formula after you’ve created the column only if it doesn’t change the column type.

Conclusion

Formula columns in Dataverse tables offer a flexible and powerful way to calculate and display data. They use the Power Fx syntax, which is similar to Office Excel, making it a human-friendly programming language. This feature enhances the capabilities of Dataverse, making it a more robust and versatile platform for data management.


Similar Articles