Power Fx Formula Columns in Dataverse

We are thrilled to announce the public preview of formula columns in Dataverse – a next generation column in Dataverse.  Formula columns empower citizen developers to express business logic on top of existing columns and reference column from other tables, directly in Dataverse.   As calculations are done at the Dataverse level, results can be seen and used in all Dataverse endpoints, including the Dataverse for Teams table view, canvas and model-driven Power Apps, Power Automate, Power BI, the Dataverse APIs, to name just a few.

Formula columns are based on Power Fx.  We are using the same Excel-like language you use for Power Apps can be applied here.   Power Fx provides a much richer set of functions than were previously available with calculated columns, especially for text processing.  Even Power Fx’s support for Dataverse many-to-one relationships and option sets is included.

In the following image from Dataverse from Teams, the Power Fx formula bar is used to create a formula column that computes total price by performing arithmetic operation on quantity and price column. The return type of the formula is dynamically determined based on the data type of the resulting expression.

The formula editor supports IntelliSense to suggest formulas and errors in real time. 

In public preview:

• The editing experience is limited to Dataverse for Teams, but we have plans to bring it to the maker portal soon.  
• This feature supports decimal, currency, Boolean, lookup and limited date/time functionality.  More to come. 
• On read, all calculations are on done on the fly as added projections of the SQL Select query.  Results are real time.  We are also working on other modes of operation, where values can be calculated and stored on write.  This would enable support for rollups of one-to-many and many-to-many relationships, something else we are working on.
• This feature will be available in few regions starting this week with more regions lighting up in next 6 weeks.

More details can be found in the documentation for formula columns.

Will this functionality replace existing calculated columns and rollups?  Not at present and there will be no rush, those existing technologies are tried and true.  Only when we have more feedback and experience, and we’ve worked through any functionality gaps, will we will draw up and communicate a long term migration plan.  In time formula columns should be able to do everything that can be done today, and much more, in an easier to use and Power Platform consistent manner.

Take it for a spin and please let us know what you think!  Please leave feedback in the Microsoft Dataverse – Power Platform Community forum.