Power Fx coming to Model-driven Power Apps, Dataverse, and more

Greg Lindhorst, Principal PM Architect, 2021. május 25., kedd

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:

Model-driven commanding. No longer do makers need to learn and write JavaScript to create their own commands! The same Power Fx formulas used in canvas apps can be used to test and modify Dataverse data.
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.

Model-driven commanding

We are announcing at Build that for the first time, makers can create their own commands using Power Fx and the familiar Excel formula bar. This will significantly lower the bar to expressing logic for citizen developers; no longer will they need to learn and debug asynchronous JavaScript code. Professional developers will also spend less time creating, maintaining, and supporting commands.

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.

Acumatica

After the Power Fx announcement, we partnered with Acumatica to evaluate how Power Fx can be leveraged with the low code/no code experience they offer to their customers. The experience gained working with Acumatica has been valuable as we craft our open source offering for everyone to consume.

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.

Early Architecture

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:

JavaScript: Canvas apps and model-driven commanding need to run in the browser using JavaScript.
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.

Onward

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.

For more information about Power Fx, start with my previous What is Microsoft Power Fx? blog. You can also visit our future open source home on GitHub, which holds just documentation today, at https://github.com/Microsoft/Power-Fx