The preview of Power Fx formula columns in Dataverse has just been announced. Up until now, I ignored formula columns as they were only available in Dataverse for Teams. So I wanted to check out a little bugbear of mine with calculated fields and how they handle nulls. Strictly speaking, calculated fields handle nulls correctly and if you perform an arithmetic operation on a field containing a null the result is null. I did a post a long time ago suggesting a way to get around this.
To create a Power Fx Formula Column you need to use the preview maker portal and you will now see the fx Formula option when you are creating a column.
Enter your Power Fx formula as required and save the column.
Once the column is saved in the preview portal it is available and editable in the maker portal. Be careful as formula columns are still in preview so you don’t want to inadvertently push them to production.
Having added my PFX column to the form I can see that it handles nulls gracefully and treats a null value as a zero. This simplifies greatly the handling of null fields as you would expect from a low-code solution. You no longer need to create shadow calculation fields to check for null values or populate all your numeric fields with zeros using unnecessary database space. Power Fx Formula Columns work in the same way as calculated fields and they create computed fields on the table in the SQL Azure database behind Dataverse. So they only recalculate after you save the record.
Power Fx started life as the Canvas Apps formula language. It is now becoming omnipresent across the Power Platform and soon will be available in Model Driven Apps, Power Virtual Agents, Canvas Apps and business process flows. So now is a good time to get up to speed with it, Power FX Overview, especially if you are struggling with nulls in calculated fields.
