Leverage Power Fx 1.0

Microsoft Power Fx has reached an important milestone. Back in March 2021, we started a journey to extract the low-code formula language from Power Apps, leverage it across the Power Platform, and make it available to you as open source for your own projects. In November 2021 we made it available as a public preview. In May 2023 we shipped it in Power Virtual Agents, Dataverse formula columns, and Cards.

Today we are pleased to announce the general availability of open-source Power Fx 1.0. “1.0” means that the language definition is now stable and breaking changes will be managed and communicated. It is now ready for you to integrate in your production work loads. It will be coming to Power Apps later this year, details below.

Power Fx has always been about leverage. It was created to leverage the knowledge of millions of Excel users who already know how to write a formula. It expanded that leverage to all corners of the Power Platform, where learning how to write Power Fx in Power Apps meant that you could leverage that knowledge in Power Virtual Agents. And now with the release of Power Fx 1.0 it expands to open-source and all the places that you can imagine using it in your own projects.

What does this mean for Power Apps?

Immediately, not much. Power Apps today uses a pre-1.0 version of Power Fx. Over the next several months, we will be updating to 1.0 in order to match the rest of the Power Platform. Most of this work will be done under a new experimental feature switch which will be coming soon:

graphical user interface, text
Power Fx 1.0 compatibility switch in Canvas Power Apps settings.


How is the formula language in Power Apps today and Power Fx 1.0 different? There are lots of small changes, most of which are nuances that most makers will never notice. For example, previously Sin(variable) would return a Blank() if variable was unitialized, and now it will return a 0 consistent with Excel. Very likely that will not impact anyone, but it could, and so we are going to treat these as breaking changes and have the switch above to enable the new semantics. When complete, new apps will be created with Power Fx 1.0 while existing apps will continue to run on the old language. No apps will break. We will publish a list of all these changes.

Why make changes now? Power Fx has evolved within Power Apps for many years and there were lots of internal inconsistencies and inconsistencies with Excel. Power Apps has a feature for updating the language in place but not all Power Fx hosts will have such a facility. Before publishing Power Fx 1.0, it was time clean up and stabilize the language.

Decimal

One of the bigger changes is the addition of a decimal data type. Business applications depend on precise currencies and quantities which is why 98% of Dataverse’s numeric fields are decimal, currency, or integers. Floating point is available but rarely used. One reason is that floating point is well known for rounding issues, resulting in the occasional math bug report in Power Apps. For example, 1.2-1.0 is very close to, but not exactly 0.2 in floating point. Supporting decimal math was a requirement for Power Fx to find a home in Dataverse.

With Power Fx 1.0, decimal is the default numeric type. For most hosts, we use the C#/.NET definition of a decimal capable of precisely holding the number 79,228,162,514,264,337,593,543,950,335, with the decimal point placed anywhere within. That is many, many times the range and precision needed for financial calculations and gives us a much larger range for integers than floating point provides. It is 10 times the number of atoms in the human body, 60,000 times the number of milliliters of water in the oceans, oh and it absolutely dwarfs the gross domestic product of all countries on Earth.

Of course, floating point has its place, in particular for scientific calculations and for calculation speed. Not to fear, it is still available too. We’ll have more details as we roll out decimal to Power Apps as part of Power Fx 1.0 compatibility.

What does this mean for your projects?

You can now easily add Excel-like calculations and low code customizations to your own projects. Leverage open-source Power Fx 1.0 in anything you do, it as another tool in your toolbox. As open-source, Power Fx 1.0 is available for you to explore on NuGet and at https://github.com/microsoft/power-fx. It is trivial to use:

  1. Create a new C# “Console App” in Visual Studio.
  2. Use top level statements.
  3. Install the package Microsoft.PowerFx.Interpreter from package source NuGet.org.
  4. Copy this code into Program.cs:
using Microsoft.PowerFx;
using Microsoft.PowerFx.Types;
var engine = new RecalcEngine();
engine.UpdateVariable("base", 123456780000000m);
string formula = "Sum( base, 12345.6789, 900000.00000123456789 )";
decimal result = ((DecimalValue)engine.Eval(formula)).Value;
Console.WriteLine($"Deicmal: {result}");

Compile and run your console app and behold the output Decimal: 123456789012345.67890123456789, a C# decimal number that can’t be expressed in floating point, that used the Excel Sum function to aggregate three values including a variable. This example just scratches the surface with facilities for adding your own custom functions and connecting with external data sources, you can see more in Joris de Gruyter’s Power Fx demo at Build 2023. We even include an npm React formula bar based on Monaco the editor in Visual Studio Code for you to make editing formulas easy with IntelliSense.

Road ahead

Our mission is to create the easiest and most powerful system to express business logic for everyone, everywhere.

What comes next? As discussed, getting Power Apps on Power Fx 1.0 is a priority. We are also actively working on improvements to Power Fx hosting in Dataverse formula columns and Dataverse low code plugins. We’ll have more to talk about and show at the Microsoft Power Platform Conference in October.

Will there be a Power Fx 1.1 or 2.0? Yes, in time, there will be more versions of the language but we are in no rush. Changes will be driven by the needs of our makers and hosts and will be carefully managed. Engage with the community at https://github.com/microsoft/power-fx and let us know what you would like to see next.