What is Microsoft Power Fx?

Microsoft Power Fx is the low code language for expressing logic across the Microsoft Power Platform.  It is the same language that is at the heart of Microsoft Power Apps canvas apps today and is inspired by Microsoft Excel.   It enables the full spectrum of development from “no code” to “pro code” with no cliffs in between, enabling diverse teams to collaborate and save time and expense.  We are very excited to bring it to more of the Power Platform and to share it with everyone as open source.  Only through a strong user community can a language grow and flourish.

Today’s announcement is a statement of direction.  We have a lot of work to do to extract Power Fx from the Power Apps home where it grew up.  We are actively working to integrate Power Fx into Microsoft Power Virtual Agents, Microsoft Dataverse, and Model-driven Power Apps. We’ll use these as our test beds for getting the packages right for open source sharing.  We have setup a GitHub repository for our open source at https://github.com/microsoft/Power-Fx where you can begin to share your thoughts about the language.  There isn’t much there today, just a copy of some early documentation which we are also in the process of extracting from Power Apps.

When we say “across the Microsoft Power Platform” you may be wondering if there are any implications for Power BI’s existing M and DAX languages.  The answer is no.  Power Fx is complimentary to and has no impact on these languages, all three will live happily together.  M and DAX focus on reading, shaping, joining, and summarizing large amounts of data while Power Fx focuses on reading and writing smaller sets of relational data.

Think Excel

What if you could build an app as easily as you build a spreadsheet?

What if you could leverage your existing spreadsheet knowledge?

These were the questions that inspired the creation of Power Apps and Power Fx.  Hundreds of millions of people create spreadsheets with Excel every day, let’s bring app creation to them that is easy and leverages Excel concepts that they already know.

Replicating Excel’s expression language for data types, operators, and core functions is fairly straightforward.  This table shows all of Power Fx’s pure functions, the ones marked in green are identical or very close to Excel’s version:

Excel has many more functions than this and we are adding more all the time to Power Fx. We also meet with the Excel team and discuss what they are adding next and how we can add functions that stay consistent with their direction.

But you don’t just write expressions in Excel, you write formulas or recipes for how to calculate a specific cell’s value.  A1 = B2 * 2 is a formula that defines the value of A1 at all times and is recalculated automatically as B2 changes.

Power Fx follows this same formula pattern.  Cells are replaced with the properties of controls or objects.  Here’s an example where we have taken this Excel formula from Stack Overflow for reverse string search:

And created a Power Fx formula from it.  All that is different is that the references to cells have been replaced with references to control properties:

Not only does this same syntax and same functions work in Power Fx, but notice something else in the animation.  As Input.Text changes (the top box), Label.Text (the bottom box) is automatically recalculated.  The app is behaving like a spreadsheet, it is recalculating.

Here is another more colorful example that uses a formula for the Fill color of the screen.  As the sliders that control Red, Green, and Blue are changed, the background color automatically changes, as it is being recalculated:

There are no OnChange events for the slider controls as would be common in other languages.  In fact, there is no way to explicitly set the Fill property value at all.  Which has a huge advantage: there is only one source of truth.  If the color isn’t working correctly, one only needs to look to this one formula to understand why.  You don’t need to search through your app to find a rogue piece of code that set the property at an unexpected time.  In fact there is no time element, the correct formula values are always maintained.

Note something else in the animation.  As the sliders are set to a dark color, the labels for Red, Green, and Blue change to white to compensate.  This is done through a simple formula on each label control’s Color property:

What’s great about this is that it is isolated from what is happening for the Fill color,  these are two entirely different calculations.  Instead of large monolithic procedures, Power Fx logic is typically lots of smaller formulas that are independent.  That’s easier to understand and enables enhancements without disturbing existing logic.

Power Fx is a declarative language, just as Excel is.  The maker defines what behavior they want, but it is up to the system to determine and optimize how and when to accomplish it.  To make that practical, most work is done through pure functions without side effects, making Power Fx also a functional language, again just as Excel is.

Now there is a time and a place for imperative, step-by-step, logic and Power Fx offers this too.  Apps write back changes to databases and start automations with the press of a button, while spreadsheets do not do these things.  Whenever possible, for all the benefits cited above and consistency with Excel, we favor the declarative approach.

Low code

Power Fx describes business logic in concise, yet powerful, formulas.  Most logic can be reduced to one-liners with plenty of expressiveness and control for more complex needs.  The goal is to keep the number of concepts a maker needs to understand to a minimum, ideally no more than an Excel user would already know.

For example, to lookup the first name of the employee for an order, one would write the following Power Fx.  Beyond Excel concepts, the only added concept used here is “.” notation for drilling into a data structure, in this case for .Employee.’First Name’.  This animation shows the mapping between the parts of the Power Fx formula and the concepts that need to be explicitly coded in the equivalent JavaScript.

Let’s look more in depth at all the things that Power Fx is doing for us and the freedom it has to optimize because the formula was declarative:

• Asynchronous.  All data operations in Power Fx are asynchronous.  The maker doesn’t need to specify this, nor does the maker need to synchronize after the call is over.  Most importantly, the maker doesn’t need to be aware of this concept at all, they don’t need to know what a promise or lambda function is.
• Local and remote.  Power Fx uses the same syntax and functions for data that is local in-memory and remote in a database or service.  The user need not think about this distinction.  Power Fx automatically delegates what it can to the server to process filters and sorts there more efficiently.
• Relational data.  Orders and Customers are two different tables, related through a many-to-one relationship.  The OData query requires an “$expand” with knowledge of the foreign key, similar to a Join in SQL.  The formula has none of this, in fact database keys are another concept the maker doesn’t need to know about.  The maker can use simple dot notation to access the entire graph of relationships from a record.
• Projection.  When writing a query, many developers will naively write “select * from …” that brings back all the columns of data.  Power Fx does analysis of all the columns that are used through the entire app, even across formula dependencies.  Projection is automatically optimized and again the maker need not even know what that word means.
• Retrieve only what is needed.  In this example, the LookUp function implies that only one record should be retrieved and that is all that is brought back.  If more records are requested by using the Filter function, for which thousands of records may qualify, only a single page of data is brought back at a time, on the order of a hundred records.  The user must gesture through a gallery or data table to see the additional data, and it is automatically brought in for them.  The maker can reason about large sets of data without needing to think about limiting data requests to reasonable chunks.
• Runs only when is needed.  We defined a formula for the Text property of the label control.  As the variable Selected changes, the LookUp is automatically recalculated and the label updated.  The maker did not need to write an OnChange handler for Selection, needing to remember that this label is dependent upon it.  This is declarative programming as discussed earlier: the maker specified what they wanted to have in the label, not how or when it should be fetched.  In fact, if this label is not visible because it is on a screen that is not visible or its Visible property is false, we can defer this calculation until the label is visible and effectively eliminate it if that rarely happens.
• Excel syntax translation.  Excel is used by hundreds of millions of users, most of which know that “&” is used for string concatenation.  JavaScript uses “+” and other languages use “.”.  We are meeting makers where they are, leveraging the knowledge they already have.
• Display names and localization.  ‘First Name’ is used in the Power Fx formula while nwind_firstname is used in the JavaScript equivalent.  In Dataverse and SharePoint, there is a display name for fields and tables as well as a unique logical name.  The display names are often much more user friendly, as in this case, but they have another important quality: they can be localized.  If you have a multi-lingual team, each team member can see table and field names in their own language.  In all cases, Power Fx makes sure that the correct logical name is sent to the database automatically.

Always live

There is another Excel trait that is critical for citizen developers: immediate feedback.  If you stop to think about it, Excel has no edit mode, compile step, or run state.  What’s great is that you’ve probably never thought about that before: you load your spreadsheet, you edit formulas and values freely, and you get your answers.  The spreadsheet is always live while in Excel and there is no distinction made between editing and running.  Changes in any value or formula are immediate propagated throughout the spreadsheet and the maker can quickly check for the right answer.  Any errors that Excel detects are surfaced immediately and don’t interfere with the rest of the spreadsheet.

Power Fx strives for this same live experience.  It incorporates an incremental compiler to update formulas while the app is running and without disturbing state.  In the animation below, the order number is displayed in a label control dependent on the slider control, even though there are two errors on the labels below it.  The app is very much alive and interactive.  A first attempt at fixing the Employee formula with .InvalidName results in an immediate red squiggly line and error displayed as it should, but the app keeps running.

Watch what happens when .Employee is typed in.  This edit causes the Data pane to add the Employees table, the metadata for this table is retrieved, and suggestions for fields are immediately offered.  We just walked across a relationship from one table to another and the system made the needed adjustments to the app’s references.  The same thing happens when adding .Customer.

After each edit, the slider continues on with its last value and any variables retain their value.  Throughout, the order number has continued to be displayed in the top label as it should.  The app has been live, processing real data, the entire time.  We can save it, walk away, and others can open and use it – just like Excel.  There is no build step, no compile, there is only a publish step to determine which version of the app is ready for users.

No code

One does not need to read and write Power Fx to start expressing logic.  There are lots of customizations and logic that can be expressed through simple switches and UI builders.  We build these “no code” tools to read and write Power Fx to ensure there is plenty of headroom for someone to take it further, acknowledging that “no code” tools will never offer all the expressiveness of the full language.  Even when used with “no code” builders, we have deliberately kept the formula bar front and center in Power Apps to educate the maker on what we are doing on their behalf so they can begin to learn Power Fx.

Let’s look at some examples.  In Power Apps, the property panel provides “no code” switches and knobs for the properties of the controls.  In practice most property values are static.  We’ll use the color builder to change the background color of the Gallery.  What you will notice is that the formula bar reflects this change, updating the formula to a different RGBA call.  At any time, the maker can go to the formula bar and take this a step further, in this example using ColorFade to adjust the color.  The color property still shows in the properties panel, but an fx appears on hover and the maker is directed to the formula bar.  This is fully two way: removing the ColorFade call returns the color to something the property panel can understand and we can again use it to set a color.

Here’s a more complicated example.  The gallery is showing a list of Employees from Dataverse.  Dataverse provides views over table data.  We can select one of these and the formula is changed to use the Filter function with this view name.  The two drop downs can be used to dial in the correct table and view without touching the formula bar.  But, as in this example, let’s say you want to go further and add a sort.  We can do that in the formula bar and the property panel again shows an fx icon and directs modifications to the formula bar.  And again, if we simplify the formula to something the property panel can read and write, it again can be used.

These have been simple examples.  We believe Power Fx makes a great language for describing no code interactions.  It is concise, powerful, and easy to parse, and provides the headroom that is so often needed with “no cliffs” up to low code.

Pro code

The other direction on the user spectrum is just as important.  Low code makers build things that sometimes require the help of an expert or are taken over by a professional to maintain and enhance.  Professionals also appreciate that low code development can be easier, faster, and less costly than building in a professional tool.  Not every situation requires the full power of Visual Studio.

Professionals want to use professional tools to be the most productive.  This is why in January we introduced language tooling that unpacks a canvas app into constituent parts that can be edited with Visual Studio Code or Visual Studio:

And enables Power Fx to be put under source control with GitHub or Azure DevOps:

Power Fx supports formula based components for sharing and reuse.  A few weeks ago we announced support for parameters to component properties, enabling the creation of pure user defined functions with more enhancements on the way.

In addition, Power Fx is great at stitching together components and services built by professionals.  Out of the box connectors provide access to hundreds of data sources and web services, custom connectors enable Power Fx to talk to any REST web service, and code components enable Power Fx to interact with fully custom JavaScript on the screen and page.

Language evolution

Evolving programming languages forward is both necessary and tricky. Everyone, rightfully so, is concerned that a change no matter how well intentioned could break existing code and require users to learn a new pattern.  Power Fx takes backward compatibility very seriously, but we also strongly believe that we won’t always get it right the first time and we will collectively learn what is best as a community.  We must evolve and Power Fx designed support for language evolution in from the very beginning.

A language version stamp is included with every Power Fx document that is saved.  If we want to make an incompatible change, we write what we call a “back compat converter” that rewrites the formula automatically the next time it is edited.  If it is something major that we need to educate the user about, we’ll also display a message with a link to the docs.  Using this facility, we can still load apps built with the preview versions of Power Apps from many years ago despite all the changes that have occurred since then.

Let’s take an example. In 2018, we introduced the ShowError function to display an error banner with a red background.  This is how it looked in 2018:

Users loved it, but they also asked us for a way to show a success banner (green background) or an informational banner (blue background).  So, we came up with a more generic Notify function that took a second argument for the kind of notification.  We could have just added Notify and kept ShowError the way it was.  But we didn’t, we replaced ShowError with Notify.  Yes, that’s right, we removed a function that had previously been in production with something else.  Why?  There would have been two ways to do the same thing, it would have caused confusion especially for new users, and most importantly it added complexity.  Was some small user re-education required?  Yes.  But nobody complained, everybody appreciated the change, and then moved on to their next Notify feature: timeout control (which we have also added).  For all the users we may have now, there are so many more to come, let’s get it right for them.

This is how the same app looks when loaded into today’s Power Apps.  No action was required by the user to make this transformation happen, it was automatic upon open:

This works because Power Apps today is a closed system.  One of our challenges will be to retain this ability as we open up to more contexts and more ways of working with source code.

What comes next?

We have a lot of work to do.  😊  We’ve announced our plans and introduced everyone to Power Fx.  Now we need to extract the language and documentation from Power Apps canvas and generalize it for other contexts.  We are on it!  Again, our open source home will be at https://github.com/microsoft/Power-Fx.  As we make progress, we’ll share progress and demos through the GitHub site, conferences, webinars, and this blog.

This has been only a high level introduction to Power Fx, there is so much more to the story.  You can learn more in the fledgling Power Fx docs or you can read about it in the existing Power Apps docs.  You can also experience it today in Power Apps canvas with a free trial.

Language improvements are also going to keep coming.  We have a long backlog including named formulas, more user defined and Excel functions, more data types, finishing error handling, dynamic schema, and declarative alternatives to App.OnStart – just to name a few.  There is so much more to come.

Open source is an invitation for you to join us on this journey.  The whole point is to build community and openly discuss your feedback, your issues, and your ideas.  We can begin that discussion right now.  Power Fx is the low code language for everyone, not just to use but to contribute too.