One of the updates announced in the 2023 Wave 1 Release was the introduction of Power Fx Formula in Dataverse Columns.
What is Power Fx?
Power Fx is the low-code excel-like language that will be used across Microsoft’s Power Platform. Its purpose is to offer the flexibility of complex logic within the platform in a human-friendly text that can be understood and utilized by citizen developers. You may have used it while creating Canvas Apps, but it is now also available with Model Driven Power Apps! Learn more about Power Fx and find the complete formula reference on Microsoft Learn.
Is this the end of Calculated and Roll-Up fields?
Although Microsoft hasn’t officially deprecated calculated fields (columns) yet, we may see it coming in the future. If you try to create a new calculated column in the preview maker, you will see a recommendation stating – “Using the Formula data type is recommended over creating a calculated column.” Currently, we cannot use the Power Fx formula functionality in roll-up fields or with plugins. Once these features are released, and more feedback is received from customers, there will be a long-term migration plan to deprecate calculated and roll up fields. For the moment, these column types (calculated, roll-up and formula) will co-exist.
Why is this exciting?
- Instead of adding multiple if/else statements, makers can leverage functions such as switch and group functions using brackets(), making the experience faster and more consistent across Canvas and Model Driven Apps.
- The platform recalculated roll-up fields once every 8-12 hours; therefore, developers have been adding plugins for the past decade to achieve real-time roll-up calculations. But results for Formula columns are calculated in real-time on the server side. Therefore, once roll-up fields can be created using Power Fx Formulas, these results will also be available in real time, eliminating the need for custom code.
- Power Fx brings the power of Excel-like low-code functions into the Power Platform and empowers Business users to do more on the platform while still respecting security rules and enterprise-level governance policy.
How do I create a Formula Column?
This feature is still in preview and available in the Maker Portal (https://make.powerapps.com/).
In the Maker portal, add a new column with Data Type ‘Formula’. Whether you are creating the column from the columns list, view or form, the option is available universally. The formula editor will appear when you select the ‘Formula’ data type.
The formula editor has IntelliSense built-in to guide the maker on available options and alert them of any errors in the entered formula. You can find a complete list of available functions on Microsoft Learn: https://learn.microsoft.com/en-us/power-apps/teams/formula-columns#available-functions.
Here’s another example: How to Calculate Age in Years in Dynamics 365?
Happy Power-Apping! 🙂