Using Power Fx in Cloud Flows

In March 2021 Microsoft introduced Power Fx as “the low-code language that will be used across the Microsoft Power Platform”. Power Fx is currently used by Canvas Apps and Custom Pages but at Build 2021 Microsoft stated that Power Fx will be extended to Power Automate in the fullness of time. Wouldn’t it be interesting if we could use Power Fx within cloud flows today? Let’s explore how we can achieve this goal.

Power Automate Cloud Flows are intended for use by line-of-business “citizen integrators” and partners. However the expression language used by Power Automate Cloud Flows and its older sibling Azure Logic Apps is somewhat esoteric.

Microsoft released the Power Fx source code in November 2021 along with a nuget package of the Power Fx engine and examples showing how to use it. Armed with this knowledge, we can create an Azure Function to host the Power Fx engine and expose it to cloud flows via a custom connector. Before exploring how it works, let’s see it in action.

Demo

Our custom connector has two actions: List Functions and Calculate Power Fx expressions

Our simple example will swap the first and last names of the user that triggers the flow. e.g. “Cole, Phillip” will be swapped to be “Phillip Cole”. (Please ignore the gross simplification of assuming that everyone has exactly two names with a comma between them!)

The Calculate Power Fx action has two inputs; an optional JSON object for the context parameter where we can provide inputs, and a yaml parameter which contains one or more Yaml expressions. In the example below we have two Power Fx expressions:

• names where we split the provided full name by the comma character
• ReversedName where we recombine the names in reverse order

When the flow is run, the Calculate Power Fx action will be executed, loading the context into the Power Fx engine, and then evaluating each of the expressions in sequence. All variables are returned in the output from the action.

The flow output shown below, can be used in later actions especially via a Parse JSON action.

Power Automate Equivalent

Using native Power Automate expressions we must use two actions. The first action converts the user name into a useable text format, the second reverses the name. e.g.

Although in this simple example the complexity is manageable, we can see there’s duplication of split(outputs('Get_username')). To see the full expression we must hover with the mouse, edit the expression, or even use ‘Peek code’. The experimental editor is an improvement on the standard (small) expression editor.

Another Example

In this example, a number of variables are provided to the Calculate Power Fx action, and multiple expressions use these variables. We pass four variables, a, b, x, and y:

{
  "a": "Hello",
  "b": "World",
  "x": 2,
  "y": 3
}

with a Yaml expression of

c: =Concatenate(a, b)
d: =Left(a, 2)
tot: =x + y
isabove4: |
  = If(x + y > 4,
    "It's above four",
    "It's below four")  

We get the following output in Power Automate

  c = "Hello World"
  d = Wo
  tot = 5
  isabove4 = "It's above four"

e.g.

Complicated Example

In this final example we are using many of the capabilities provided to us by Power Fx, e.g. Tables, Records, Filtering and Sorting.

Flow setup

The Calculate Power Fx action is passed a context containing the number entered when the flow is manually triggered, nested object and tables.

In the screenshot below we can see that the Power Fx has been evaluated, and the results output provided.

Output JSON

Here’s the full output of our Power Fx action for the example above. We can see the ‘Hello World and Mars’ fields have been joined, the list of ages have been averaged, the collection of ages reduced by 10 for each age, the actors have been sorted by their first name, the just the first names of the actors that are proprietors have been returned, and the instruments joined, and we’ve had a cumulative calculation where we’ve reused a variable. So there’s a lot of possiblities! And this is just scratching the surface.

{
  "NumberSquareRoot": 6.708203932499369,
  "joined": "Hello World and Mars",
  "CitiesJoined": "London, Paris, New York, ",
  "Ages": [30.0, 36.0, 47.0],
  "AverageAge": 37.666666666666664,
  "AgeLessTen": [20.0, 26.0, 37.0],
  "ActorsByFirstName": [
    {
      "FirstName": "Basil",
      "LastName": "Fawlty",
      "Role": "Proprietor"
    },
    {
      "FirstName": "Manuel",
      "LastName": "Unknown",
      "Role": "Waiter"
    },
    {
      "FirstName": "Polly",
      "LastName": "Sherman",
      "Role": "Waitress"
    },
    {
      "FirstName": "Sybil",
      "LastName": "Fawlty",
      "Role": "Proprietor"
    }
  ],
  "Proprietors": "Basil and Sybil and ",
  "Instruments": [
    {
      "Name": "Violin",
      "Type": "String"
    },
    {
      "Name": "Cello",
      "Type": "String"
    },
    {
      "Name": "Trumpet",
      "Type": "Wind"
    }
  ],
  "InstrumentsJoined": "Violin, Cello, Trumpet, ",
  "total": 7.0
}

Editing Tips

I’ve found that editing the context JSON in a compose action avoids problems with invalid JSON. This is because the flow will only save if the JSON is valid.

Editing Power Fx YAML directly in Power Automate is possible, but I’ve found it easier to use Visual Studio Code with the YAML extension. In the screenshot below, it is clear where indentation and commenting issues exist. The second - total is indented too far, and the // comment should actually be a # comment.

The Power Fx engine requires that functions are of the correct case (e.g If and not if). In Canvas Power Apps, casing is handled by the Canvas Apps editor so it can be frustrating editing Power Fx using an editor without intellisense.

As with Power Automate, complex expressions can be built up gradually via incremental testing and expansion of a Power Automate flow.

Error handling

If a Power Fx expression is invalid then the error is exposed to Power Automate, as shown below.

YAML structure and comments

We use the same YAML structure that’s used in Canvas Apps as documented by Microsoft.

• YAML allows for comments which are lines beginning with a # symbol. Any lines starting with # will be treated as a comment.
• Power Fx expressions support both inline comments (// foo) or block style comments (/** bar **/).
• YAML allows expressions to be split over multiple lines.

# Split into first name and last name, and combine
# Use & to concatenate strings. Use Trim to remove any double spaces
FirstName: = First(names).Result    // First() returns a table, hence .Result
LastName: = Last(names).Result   /* This is a
  multi-line comment */
ReversedName: |
  = Trim(LastName & " " & FirstName)  

To work around the lack of a set() function in the Power Fx engine and to allow a variable to be ‘redeclared’, YAML sequences can be used (lines beginning with -).

x: =3
y: =4
LevelDown:
  # Indentation is ignored. All variables are treated
  # as top level.  Indentation can be used to delimit
  # sections if needed.
  - total: =x + y               // =7
  - total: =total + total + 1   // =15

Available Functions

The List functions outputs the available Power Fx functions that can be used.

Currently only a subset of the functions in Canvas Apps are present in the Power Fx nuget package. As the package matures hopefully more of the core Power Fx functions will be added. I’ve roughly grouped the available functions in the table below:

Technically, how does it work?

We’re using the following building blocks:

• Microsoft.PowerFx nuget - 0.2.1-preview release!
• DotNetYaml - to parse the provided Yaml
• .net 6 Azure Function with Azure Functions v4
• Azure OpenAPI Extensions - to aid the creation of our custom connector

The code is available in detail and is at https://github.com/filcole/PowerFxCustConnector. The summary parts are explained below:

Available functions

Getting the list of available functions is as simple as below.

using Microsoft.PowerFx;
using Microsoft.PowerFx.Core.Public.Values;

var engine = new RecalcEngine();

Console.WriteLine(String.Join("\n", engine.GetAllFunctionNames().OrderBy(x => x)));

Calculate Power Fx

Evaluation of a Power Fx statement is within the CalcPowerFxYaml function, see the code! Power Automate, will trigger this Azure function via an HTTP POST via the custom connector with the JSON context and a Yaml string in the POST body. Once triggered we:

1. Initialise the Power Fx engine
2. Convert our passed context into a RecordValue
3. Evaluate the Yaml into a list of formula objects, which is the variable name string and the Power Fx expression string
4. For each formula object, evaulate the expression and store the result in the engine under the variable name
5. Extract the values of each variable (by name) and return this in a JSON object to our custom connector and then cloud flow

Benefits

Here are the potential benefits of using the Power Fx custom connector:

• We expose the familiar Excel-like Power Fx language to Power Automate (and by extension to Power Virtual Agents).
• We can combine many expressions that could require multiple actions in Power Automate into a single ‘Power Fx’ action. It might be more cost effective to use a single ‘Power Fx’ action than using multiple Power Automate actions.
• We can add comments inline within Power Fx expressions.
• We obtain access to the increased capabilities of Power Fx, e.g.
  • ‘Power of’ functions - Abs, Exp, Ln, Sqrt
  • Aggregations - Average, Min, Max, Sum
  • Logic Operators - familiar boolean operators: &&, ||, !
  • Rounding - familiar rounding functions: Int, Round, RoundDown, RoundUp, Trunc
  • Sorting - currently only Sort
  • ForAll - run a complex formula against every in a table (within memory limits)
  • Sequence - generate a sequence of numbers, e.g. ForAll( Sequence( 10 ), DateAdd( Today(), Value, Days ) )
  • If/Switch - Switch and if statements allow multiple conditions within a single action.
  • Filter - filtering of a table.
  • many functions work across whole tables, rather than single rows, e.g. Lower, Upper, Proper
• If the custom connector is created via API management into the Power Platform then it is available for use within Dataverse for Teams without a premium license - this may be useful for new ‘makers’ that are more familiar with Excel.

Costs

• Hosting an Azure Function can be very low cost, especially on the consumption plan. Yet, there is always a cost involved in adding additional components.
• There may be an additional cost to use Application Insights, though this is good to have.

Limitations

• There is certainly a level of complexity that is required to enable use of Power Fx in Power Automate, although once setup it can be used throughout an organisation.
• Custom connector usage can be throttled, per minute there is limit of 500 requests per connection created by the connector
• Azure functions have a time limit of up to 5 minutes (for the consumption plan). However if expressions are taking longer than this then it is a good time to review the logic and architecture in play.
• An action has a maximum message size of 100MB, or 1GB with chunking.
• TimeZoneOffset is currently not available in the Microsoft.PowerFx library, so working with Timezones could prove challenging. There is no equivalent of the LogicApps timezone conversion functions, e.g.convertFromUtc, convertTimeZone or convertToUtc
• There is a ‘cold-start’ time to running an Azure function hosted in the consumption plan, so if only one flow is occasionally using the Power Fx action it may a minute or so to warm up.

Summary

We created a proof of concept Power Fx custom connector so that Power Fx could be used in Power Automate cloud flows.

• We examined the benefits, limitations and costs when working with our custom connector.
• We may be able to simplify and better document a complex Power Automate expression in Power Fx, a language that will become more prominent over time.
• The Power Fx nuget package has only had one preview release, so production use is best avoided!

References

The following references were useful when compiling this blog.

• Microsoft Docs
• Debugging Azure Functions locally ngrok by Ivan Ficko (@dynamicsninja)
• Inspiration from Calling JavaScript within PowerApps Canvas Apps by Scott Durow (@ScottDurow)
• Build 2021 presentation

Source Code / Solutions

The source code for the Azure function and solutions containing the custom connector and flows are available at github.com/filcole/PowerFxCustConnector