Spreadsheets are amazing. They have democratized calculations for billions of people, covering everything from simple sums over a few rows to complex financial models that can take hours to compute. New users and seasoned experts alike use the same tool and the same formula language, enabling collaboration and a seamless path for skills development. Spreadsheets are arguably the most successful programming model ever developed.

Microsoft Power Fx is a new general purpose, low-code programming language that leverages and extends the spreadsheet model to democratize the creation of apps, automation flows, virtual agents, and other business solutions. Novice and professional developers alike use Power Fx across the Microsoft Power Platform. Our mission is for Power Fx to be the easiest and most powerful system to express business logic for everyone, everywhere.

Power Fx is open source. It is designed to be embedded in a larger project and includes a C# compiler, C# interpreter, and React formula bar. Incorporate Power Fx anywhere that low-code customizations would be of service and join the community at https://github.com/microsoft/power-fx. The second half of this article will take you through how to get started. Power Platform hosts consume the same public NuGet and npm packages that everyone else does.

There isn't just one way to integrate Power Fx. We'll first walk through examples of how the Power Platform does it, showcasing:

• Evaluate formulas: Hand it an Excel-like formula and it evaluates it, one formula at a time. Using Power Fx for simple calculations is a great place to start. Dataverse formula columns and Power Virtual Agents use this model.
• Evaluate a graph of formulas: Hand Power Fx a set of interdependent formulas and it will evaluate it efficiently as inputs change. This is the equivalent of Excel recalc. Power Apps and Cards use this model.
• Access external data: With over 1,000 connectors and custom connectors to any REST API, any data source can be integrated into the calculations. Power Apps uses this model.
• Imperative logic: Going beyond spreadsheets, a small set of Power Fx functions can mutate state and have side effects. Power Apps and Dataverse low-code plugins use this model.

Finally, we believe that Power Fx is an excellent target for generative AI code generation, allowing humans to validate and maintain the result in a concise Power Fx form. Power Apps has already taken some steps in this area, but there is much more to do, and we'll have more to share in time.

This article will show examples of Power Fx across Power Platform today, and then show how pro-devs can leverage the Power Fx SDK to build similar features into their owns products.

Power Fx Across the Power Platform

Power Fx can support some amazing things in Power Platform including evaluating formulas (even formulas based on other formulas), access external data, handle imperative logic, and even write SQL-like triggers. We'll cover these in the next few sections.

Evaluating Formulas

It is far easier to leverage what someone already knows rather than teach them something new. For this reason, Power Fx's core is the same Microsoft Excel formula language that so many already know.

Take, for example, calculating a full name from individual name parts. In Excel this might be written as the formula you see at the top of Figure 1:

Figure 1: Example of an Excel formula

In Dataverse formula columns (Figure 2), powered by Power Fx, this exact same formula (Figure 3) can be used with columns of the database table. The semantics of the Trim, Left, and IsBlank functions are the same. The formula column automatically recalculates when one or more of the name parts changes.

Figure 2: Formula columns in Dataverse can automatically recalculate

Figure 3: A similar Power Fx formula in dataverse formula columns

As a host, it is easy to add your own custom functions. We are also actively working on user-defined functions written in the formula language with the same power as Excel's Lambda function.

Evaluating a Graph of Formulas

Just like a spreadsheet, formulas can depend on other formulas, creating a dependency graph. In the case of Power Apps, each property of a control is associated with a formula. Properties cannot be directly mutated as they can in other systems, the value of the property has a single source of truth which is the formula. For example, consider the Service Desk example in Figure 4.

Figure 4: Power Fx formulas on a control property in Power Apps

Highlighted is the formula for the Fill property of the rectangle to the left of each item, using the same Switch function that is available in Excel:

Switch( ThisItem.Status, "In progress", Color.Yellow, 
  "On hold", Color.Red,
  "Closed", Color.Green, Color.Blue )

For each item in this gallery of items, the color will be determined based on this formula. This formula entirely determines the color of the vertical bar and there is no way that it can be set otherwise. As a single source of truth, it is much easier to understand what is happening in the app.

Accessing External Data

Power Fx is designed to work well with tabular data sources such as Dataverse tables, SQL Server tables, and SharePoint lists. But we aren't limited to just Microsoft sources; we offer connectivity through over 1,000 connectors ranging from SAP to Facebook. Any REST APIs can also be consumed directly.

Tables and records are first class data types in Power Fx and are strongly typed. Functions such as Filter and Sort are designed to delegate operations when possible to the data source rather than doing the operation locally. Let's look at how the filtering is done in our Service Desk app, focusing on the first four filter options (Figure 5).

Figure 5: Power Fx expression to switch and filter tables

Depending on the selection in the gallery on the left, one of the Filter functions will be used, showing here “Tickets in progress.”

Switch( FilterGallery.Selected.TicketTypes,
        "All tickets", Tickets,
        "Tickets in progress", Filter(Tickets, Status = "In progress"),
        "Tickets on hold", Filter(Tickets, Status = "On hold"),
        "Tickets closed", Filter(Tickets, Status = "Closed") 
)

Power Fx translates this to a corresponding OData GET call, delegating the Status comparison to the server and requesting only the column needed by the app in return. The details of understanding OData, how it works differently from T-SQL for example, display names vs. logical names, and projection optimizations are all handled by Power Fx on behalf of the maker.

https://orgxxxxxxxx.crm3.dynamics.com/api/data/v9.0/cr5e3_tickets?
        %24filter=cr5e3_status+eq+%27In+progress%27&
        %24select=cr5e3_ticketid%2Ccr5e3_status%2Ccr5e3_id
        %2Ccr5e3_owner%2Ccr5e3_priority%2Ccr5e3_subject
        %2Ccr5e3_description%2Ccr5e3_assignedto

Imperative Logic

Up to this point, all the examples have been based on data flow with no imperative logic. No variables have been used and no On events have fired. Just like a spreadsheet, calculations happen automatically as dependencies change. This is great for novice makers as there are no timing dependencies and no state to initialize or maintain. Formulas provide a single source of truth and cannot be mutated in unexpected ways.

Excel's formula language, which is a pure functional language with no variables, enables Excel to control when and how recalc occurs. You can't add a button to Excel that runs Excel formulas, instead it can only trigger external logic such as VBA.

But alas, apps require buttons. When a Save button is pressed, data needs to be transmitted to a server exactly once and the app may want to wait for the response. Power Fx extends the Excel model by adding imperative logic for app navigation, submitting forms, setting variables, and writing to databases.

In this example shown in Figure 6, we will navigate to another screen when an item in the right gallery is selected:

Figure 6: Power Fx expression to navigate to another screen in Power Apps.

Navigate( TicketdetailsPage )

The second screen's form control is wired up to display and edit the selected item in the first screen's gallery, through TicketDetailsForm.Item = TicketsGallery.Selected. Autosave is possible, but the user may want to explicitly save the form with a Save button, as shown in Figure 7.

Figure 7: Power Fx expression to submit a form.

The Save button's OnSelect is set to the formula SubmitForm( TicketDetailsForm ).

Most logic in Power Apps is data flow based with many small formulas that wire properties between controls, just like the cells of a spreadsheet. This greatly simplifies development and is very scalable. On top of this there will be a few event handlers for buttons, timers, and data caching when imperative logic is required.

Server Triggers

The gallery and form controls are specific to Power Apps, but Power Fx also provides a rich set of UI-less functions for manipulating data. These can be used in Dataverse low-code plugins (https://learn.microsoft.com/en-us/power-apps/maker/data-platform/low-code-plug-ins?tabs=instant), a way to easily write SQL-like triggers with Power Fx.

In this example (Figure 8), we'll perform three tasks for inventory management in an order entry solution. These tasks will be performed in order as Dataverse low-code plugins are entirely imperative:

1. Check that the quantity on hand can satisfy the order, and if not, return an Error to the end user.
2. Reduce the quantity on hand based on this order with the Patch function.
3. If the quantity on hand is less than the minimum reorder quantity threshold, add a record to the ReorderProducts table with the Collect function.

Figure 8: Power Fx expression for Dataverse entity create

Note the use of ThisRecord.Product in Listing 1 for easily walking the relationship between the Order Details table and the Product table. Also note that we executed a sequence of functions in order, separated by semicolons, within the else portion of the If function.

Listing 1: Power Fx listing for Dataverse entity create

// Return an error if there is not enough on hand for the order
If( ThisRecord.Product.'Quantity On Hand' < ThisRecord.Quantity,
  Error( { Kind: ErrorKind.Custom, 
           Message: "Not enough product on hand" } ),

// Update the quantity if there is enough for this order
  Patch( 'Order Products', ThisRecord.Product, 
         { 'Quantity On Hand': ThisRecord.Product.'Quantity On Hand'
               - ThisRecord.Quantity } );

// If we are below the reorder threshold, make a note 
// in the ReorderProducts table
  If( ThisRecord.Product.'Minimum Reorder Quantity' > 
      ThisRecord.Product.'Quantity On Hand' - ThisRecord.Quantity,
      Collect( ReorderProducts, 
          { Product: ThisRecord.Product, 
            Overage: ThisRecord.Product.'Minimum Reorder Quantity'
                -ThisRecord.Quantity } ) );
);

Integrating Power Fx Into Your Own Projects

Let's walk through how you can add Power Fx to your projects.

Suppose you have an app that lets users create a survey. A survey consists of a list of multiple-choice questions. Initially your app supports a static list of questions and responses, but in time your users ask for dynamic logic to direct the survey based on previous responses. Power Fx is a perfect addition; it is an embeddable language that your end users will already know from Excel.

We have a full working sample at https://aka.ms/PowerFxSurveySample. The rest of this article will walk through a few highlights in that sample and show how to implement this kind of low-code ability.

Power Fx includes a React formula bar and Language Server Protocol implementation that you can use in web pages to allow users to have rich editing for their expressions. This is the same formula bar used in many of the Power Platform examples above. We have a further sample of that at: https://aka.ms/PowerFx_FormulaBarExample.

Another common pattern is to have no-code designers that generate the formula for the user. For example, the user may describe a pattern in a “query builder” control and that emits Power Fx as “code behind” into the document.

To scope this article, we'll skip over the UI elements and focus directly on console interactions and the Power Fx SDK APIs.

For this example, assume your survey designer is saving the user's document in a YAML format. YAML is an ideal serialization format to allow editing outside the designer (such as via VS Code) because it allows embedding Power Fx via significant whitespace instead of escaping. For example, we may have a dynamic survey like in Figure 9:

Figure 9: Survey YAML with Power Fx expressions

In this example, we'll use the convention that properties starting with an equals sign “=” character are Power Fx formulas. This is like Excel's convention but also conveniently plays well with YAML's escaping rules.

As you can see, the expressions make the survey much more dynamic:

• The question's Title and Answer expressions are evaluated and shown to the user. For example, the first question can use a DateAdd() expression to show the actual value of 2 weeks from now and use the Text() format function to format the data in long form in the current locale. These expressions are consistent with what you could write in a Power App Label.Text property.
• Access to the built-in User object to personalize the text with the FullName and Email.
• If the IsHidden expression evaluates to true, then the question is hidden. The previous questions can be referred to as variables by their ApiName. For example, the second question is only shown if the user responded appropriately to the first question.
• The OnComplete expression is run when the survey is completed. It can save the results to a data source like Dataverse and compute a final message to show the user. This is a side-effecting expression, like a Button.Select in Power Apps.

You'll notice almost everything the user specifies is an expression! Even a string literal is just a trivial expression. This follows a pattern similar to Power Apps where every control property is an expression and allows the user to create incredibly dynamic surveys.

For this sample (Figure 10), we have a basic console runner for the surveys.

Figure 10: Running a survey the first time

But if we run with different answers, we can see the dynamic results (Figure 11):

Figure 11: Running survey a second time with different answers

Basic Evaluation

Power Fx is fully open source on https://github.com/microsoft/power-fx and produces NuGet packages available on a daily feed and https://nuget.org. Table 1 lists the key NuGets.

Listing 2 shows the most basic evaluation scenario in Power Fx, using the Microsoft.PowerFx.Interpreter NuGet. This prints Answer is 42.

Some key elements here:

1. Includes the Microsoft.PowerFx.Interpreter NuGet and associated usings.
2. RecalcEngine class is the Power Fx interpreter. It can hold variables and evaluate expressions.
3. FormulaValue base class represents a value within Power Fx interpreter. There are derived classes like StringValue for different Power Fx types.
4. RecalcEngine.Eval() is the key method to evaluate the expression. It returns a FormulaValue which the host can then act on. It will throw on errors.

Static vs. Runtime

Power Fx is statically typed to:

• Allow better IntelliSense and suggested completions during authoring.
• Give makers errors at design time rather than runtime. For example, if there is an error in the expression in the seventh question of the survey, we want the maker to know immediately before they even publish the survey.

The RecalcEngine class has the Check() method to parse and get errors at design time � without evaluating the expression. So, a common pattern is to call Check(), report errors to the user, and only let the user continue if Check succeeded.

The example in Figure 12 shows how a single Eval() call can be split up so we can show the user errors at design time. The CheckResult holds all the work derived on an expression, including the parse, binding, error status, evaluator, dependencies, etc. Any errors can then be displayed to the user by the host application. On successful binding, we get an IExpressionEvaluator for the expression which can be used to evaluate. Eval() is just doing this all in a single convenient step.

Figure 12: Compile-time checking separate from runtime evaluation

A good pattern to follow is:

• Parse the YAML file to Pocos. The Poco has string or primitive fields. Our example uses YamlDotNet. If the YAML was hand edited (such as in VS Code), there may be parse errors to report. See SurveyPoco.cs in the example.
• Validate the YAML, including calling Check() on each Power Fx expression.
• Save the results in a model class which can then be used efficiently at runtime. The model has fields of IExpressionEvaluator. A single model can be evaluated many times for many users. See SurveyModel.cs in the example.

See ExpressionEvaluator.cs in the sample for a class that wraps both static checks and runtime evaluation.

Evaluation, Blanks, and Error handling

Once we successfully have the IExpressionEvaluator, we can then execute it to get a FormulaValue. For example, static checking can ensure that an IsHidden expression is typed to return a Boolean. But at runtime, any Power Fx value can potentially be an error or blank. For example, If(test, x, y) will return x if the test is true and y if the test is false. But If(test, x)*will return a Blank() if test is false. Similarly for errors, 12/x will return 3 if x is 4. But if x is 0, it will produce a runtime error for the divide-by-zero.

All Power Fx values are derived from the FormulaValue base class. Errors would be ErrorValue and blanks would be BlankValue.

Additionally, since a single compiled expression can be evaluated many times, the Eval() method takes per-eval parameters and a cancelation token.

So, to evaluate the IExpressionEvaluator as in Figure 13:

• Call EvalAsync(), passing in a cancellation token to protect for timeout.
• Check the result for a BooleanValue. This is the success case.
• Check for Blank and handle appropriately.
• Check for Error. We can get the specific error messages and report to the user.

Figure 13: Evaluating an expression

Current User

Power Fx has the User object which supersedes Power App's User() function. User has specific well-named properties instead of a generic “Id” field. This gives users consistent semantics which also facilitates sharing code snippets. For example, User.DataverseUserId specifically refers to the user table and User.EntraObjectId specifically refers to an Entra Id (formerly Azure AD). The list of possible properties is curated by Power Fx.

The hosts then can specify which of these properties they support. For example, a host that doesn't use Azure login would not enable the EntraObjectId property.

In our survey example, we'll use Email and FullName, which enables customizing the text like:

$"{User.FullName}, do you want a follow-up mail sent to {User.Email}?"

First, we need to enable this in the symbols:

_engine.Config.SymbolTable.AddUserInfoObject(nameof(UserInfo.FullName));

And then we need to add the user info to the RuntimeConfig that is passed into the Eval() call.

            var userInfo = new BasicUserInfo
            {
                FullName = "fullname",
                Email = "me@contoso.com",
            };
            var rc = new RuntimeConfig();
            rc.SetUserInfo(userInfo);

Calling Connectors

Power Fx can easily call any REST API. To use the connector support, include the Microsoft.PowerFx.Connectors package.

Figure 14: Adding calls to REST APIs in Power Fx

This will print:

“New Year's Day, Martin Luther King, Jr. Day, Presidents Day, Good Friday, Good Friday, Memorial Day, Juneteenth, Independence Day, Labor Day, Columbus Day, Veterans Day, Thanksgiving Day, Christmas Day”

This does not solve authenticating to the Power Platform connectors, but it does enable your code to call any REST API that you can authenticate to.

Calling Dataverse

Power Fx easily connects to tabular data sources in Dataverse. These tables could include Power Fx formula columns as shown in the first section (Figure 2). For example, the Collect() call in the OnComplete expression will add a new row to Dataverse with the survey results. The Dataverse integration is built on top of an IOrganizationService interface from the existing Dataverse SDK (https://learn.microsoft.com/en-us/power-apps/developer/data-platform/org-service/iorganizationservice-interface).

This lets it run inside a Dataverse plugin, on clients, or anywhere else you have an IOrganizationService.

See the sample on the website for more details about how to enable Dataverse SDK calls from your Power Fx expressions.

Calling Custom Functions

Power Fx provides a rich library of built-in functions, including math, string date, locale, and tabular functions. Your host can easily extend these functions by importing swagger files and connecting to Dataverse.

Your host can also add its own custom functions for host-specific scenarios. This is similar to how your host can add its own custom variables. These abilities combine to help the user's expressions integrate deeply with your host application.

In the example in Figure 15, we've created a new custom function. This derives from ReflectionFunction base class, which will then reflect over the Execute() method's signature and add a as a Power Fx function to the config. Execute gets FormulaValues passed in and can return a FormulaValue back to the expression.

Figure 15: Calling custom functions

A Word About Safety

The Power Fx interpreter is safe for executing on servers in multi-user environments without a separate container. This enables high density / high volume scenarios and has characteristics like a JSON serializer.

• Evaluation can take a cancellation token to abort long-running expressions. The interpreter can also be configured with a “governor” object that is invoked throughout execution to monitor and constrain resource consumption.
• Expressions are statically typed, highly constrained, and can only access the symbols provided by a host.
• The Power Fx interpreter is written entirely in safe C# (verifiable IL) and avoids constructs like reflection emit code generation or p/invokes to native code.

Conclusion

Power Fx is the low code language of the Power Platform, leveraging the Excel knowledge that so many makers already have. Integrate Power Fx into your own projects through open source and empower your end users with low-code customizations.

You'll find more samples at: https://github.com/microsoft/power-fx-host-samples.

Please reach out on the discussion page at https://github.com/microsoft/Power-Fx/discussions with any questions or to just share with the community about any projects you're doing with Power Fx!