We announced Power Fx at Ignite just over two months ago, setting a new standard for the industry with an open source language for low-code development. We could never have imagined the level of interest and excitement that it has generated. Thank you! It has been amazing. Teams both inside and outside of Microsoft approach us regularly to find out how they can offer our Excel-like low code language to their customers.
With its roots in Excel and concise power for logic, Power Fx will be a great language for a wide spectrum of makers, from no code seamlessly through pro code. We are announcing today one of the ways we will accomplish this: using AI tools such as GPT-3 and PROSE to help no code makers write Power Fx.
We have also been working hard on integrating Power Fx with more products across the Power Platform and beyond. Today we’d like to share an update on three of those areas of innovation:
- Dataverse calculated columns. Use Power Fx formulas for calculated columns and rollups in Dataverse, a more modern and powerful alternative to existing facilities.
- Acumatica. Reaching beyond the Power Platform and even Microsoft, Acumatica has integrated Power Fx for calculated fields on forms, that can both reference other fields and do aggregate calculations on related tables.
In the following animation, the new command designer is being used to create a new command which will add a new related Task to the current Account. The designer includes the Power Fx formula bar, complete with IntelliSense and error reporting. As with buttons in canvas apps, the OnSelect formula is being edited to include a Patch function call to create the record.
Power Fx for Model-driven commanding will ship as a public preview by the end of June.
Dataverse calculated columns
You may be saying, but wait, Dataverse has an existing calculated columns and rollups feature. Yes. But it isn’t easy to work with, requiring a full screen popup editor, and doesn’t offer many functions. The functions it does have, like TrimLeft, don’t appear by that same names in other languages, which requires learning.
We are announcing at Build that we going to upgrade this important area with Power Fx. The formula bar will be inline directly in the column settings (shown below temporarily in the Description field). The number of functions available will expand 10 fold. Rollups will be merged in to a single experience, taking advantage of the Dataverse relationship support we enjoy in canvas apps today.
Power Fx for Dataverse calculated columns will ship as a public preview by the end of June.
In this demo, a number of calculated fields have been added to the form with Power Fx. They use the same Excel language that so many users already know. Note that the Sum and Average function are used to aggregate information in related tables, the result you can see at the end of the animation when the discount is updated for one of the records and the field automatically recalculates.
Our integration work has helped us refine our architecture. Here is a high level diagram of what we are thinking. Note the tight integration between the Formula Bar and the Analysis (compiler).
Also of note, it is clear to us that we will need to target different runtime environments:
- C#: Power Virtual Agents, a future integration, also needs to run C# on the server in a managed code runtime . Acumatica’s product is also written in C#.
- T-SQL: Dataverse calculated columns need to run as part of a T-SQL query in the database.
It is still a little too early to have open source code or NuGet packages to share, but we are working steadily toward that goal. The integrations above are using packages that have been extracted and isolated form the main Power Apps sources, a major step forward for us.