Modal Title
Software Development

Microsoft Open Sources the Power Fx Language for Customizing Logic in Low-Code Apps

Power Fx; an open source programming language for low code that’s based on Excel.
Mar 10th, 2021 10:28am by
Featued image for: Microsoft Open Sources the Power Fx Language for Customizing Logic in Low-Code Apps

Back in January, Microsoft released an experimental tool to make it easier to collaborate on the drag-and-drop low code Canvas apps you can create in Power Apps, by exporting them to GitHub. Instead of an opaque blob file, the utility extracts editable source code (written in a subset of YAML) that can be edited in an IDE like Visual Studio Code, checked into a source control system and generally become part of standard application lifecycle management where you can diff changes, make a pull request and merge PRs to update the app.

That was the first glimpse of Power Fx; an open source programming language for low code that’s based on Excel. The Canvas app formula language is sometimes also called the Power Apps expression language, because it’s what formulas, IF statements and variables have been written in all along, but it’s also how SharePoint forms are customized and it’s used in Dynamics 365 setups.

Now Microsoft is turning it into an open source, strongly typed, declarative, functional language, where developers can use imperative logic and state management if they need to.

The green functions are Power Fx functions that are the same as in Excel (Microsoft)

The Low Code Cliff Edge

Why does low code — a movement designed to enable people without development expertise to builds apps — need a programming language at all? No low code platform will be able to cover everything users want to achieve, so they need to be extensible. Working in a visual developer environment lets you build workflows and connect components together with static analysis, incremental compilation, but it isn’t always easy to create the custom logic that makes applications more than simple lists and formatting. Organizations want to be able to create their own components to supplement the built-in options. And if a low code app is useful enough that it becomes widely used, they might want a professional developer to take it over, add more features and make it the official option.

Low code development will grow by 23% in 2021, according to Gartner. In fact, IDC predicts that between 2018 and 2023 more new apps — up to 500 million — will be written than in the previous 40 years. That means that a majority of enterprise software could be developed, at least initially, with low code tools in the next few years — by the business experts who know what the software needs to do rather than by developers who work from requirements documents.

It also means that that organizations will be building more software more quickly than they ever have, and wanting to update or replace software just as quickly when situations change.

Usually, extending an app built with low code platform means handing it over to a professional developer who might end up rewriting it using tools they’re familiar with — which means the original app creator either has to learn a full programming language or stop contributing to the app.

But software development is a team sport; low code needs to be able to participate in the pro-development tools developers rely on, and experienced developers need low code platforms to give them to flexibility to extend apps without recreating them from scratch. Recently Microsoft has been emphasizing the ways Power Apps is relevant to professional developers by connecting it to GitHub Actions (and Azure DevOps) to make it easier for them to add their own components.

The next step is making the underlying expression language a first-class programming language for the tools in the Power platform, and for low code generally as an open source project; whether it’s for simple configurations that link pre-built components or for more complex logic.

Power Fx code in Visual Studio Code (Microsoft).

Evolving a Language for Code and Low Code

Developed at Microsoft under the code names Tangram and Siena, Power Fx is based on the Excel formula language; it has the same syntax and functions and many formulas can be copied over from Excel and will work straight away. But it also pulls in work from other Microsoft low code efforts, like Project Siena for creating touch-friendly tablet apps that connected to Excel, SharePoint lists, Azure Mobile Services or any REST API and the Tangram incubation project, which promised interactive 3D and “entirely new ways to see, interact with and use data of deep interest to consumers and business people” (which never became a specific project but sounds very like Power BI), and programming optimization tools like the Microsoft Solver Foundation.

Radu Gruian who led the functional expression language design team had been the cofounder of Project Siena with Robin Abraham (who had done extensive research in “end-user software engineering”), before moving on to build the Power Apps platform with Vijay Mital, who had been an architect for the Power BI technology. Shon Katzenberger worked on the Solver Foundation, C# language design and Tangram; Darryl Rubin was one of Microsoft’s first Distinguished Engineers.

They wanted to create a language that would be familiar to Excel users, and that kept the instant calculation of Excel, where you can see the results (and any mistakes) as soon as you edit a formula, rather than having to go through multiple steps to compile, run and test the code. In traditional programming languages there can be many different pieces of code that affect a variable; in a spreadsheet, although there can be underlying calculations, there’s only one place a specific value is calculated. But they also incorporated ideas from Pascal, Miranda, Mathematic and linear solvers.

Power Fx statements start with = like Excel formulas. That’s familiar but it also means that the YAML used to bind expressions to properties isn’t parsed. In the future, non-formulas that don’t start with + will also be supported.

Power Fx formulas use the same IF (TEST, THEN, ELSE) format as Excel for conditional statements and the same string evaluation functions for text handling (including & rather than the + of JavaScript). Types, operators and functions are based on Excel, or on SQL for data operations and strong typing not available in Excel (types are derived rather than declared, and the types of all values are known at compile time). Unlike Excel, functions can have side effects, but the language attempts to minimize these. And because Excel doesn’t have comments, Power Fx adopts the line and inline comments from C.

Power Fx binds objects to those formulas using declarative logic — describing what you want to do, not when or how to do it — with dot notation that references the object properties, like the fill on a button, the title of a field or the contents of a record or text box.

It also supports imperative logic for refreshing data sets, resetting controls, navigating into different screens, submitting forms and other tasks that you don’t need in a spreadsheet but do need in apps.

As Clay Wesener explained in a session at Microsoft’s Ignite conference earlier this month, Power Fx is more compact than JavaScript and the Power platform does some of the development work with governance tools, quality checkers and collaboration tools

“I’ve replaced effectively seven lines of code with one line of Power Fx. Not only was that easier and quicker to write, but on top of that, my query has been delegated for faster performance. I’ve added the benefit of error handling. I’ve used query projection, which basically means it’s gone faster and it’s been more efficient, and as a developer, I haven’t had to think about that.”

Power Fx opts for many things that make development less confusing to non-experts. The incremental compiler means you don’t need event handlers; if you have sliders to change the background color, the color changes automatically when the slider sets the value in the formula. A separate calculation can use the current background color to change the handles and labels on the sliders to a contrasting color; it’s easier to change independent formulas without affecting logic elsewhere in the application; if you make a mistake in the formula to change the color of a button, that won’t stop the app from working – but button color will just be wrong. There’s no undefined value; if something isn’t defined, it’s treated as blank (or an error).

Asynchronous data operations mean users don’t need to create promises or understand lambda functions, or database keys or data projection. Large amounts of data will be automatically returned in 100-record chunks without the user needing to limit data requests.

Like Excel, you can use commas or periods to separate decimals if that’s what your native language (like French) does. Like the user-friendly display names, this makes Power Fx code easier to localize.

And because the Power Fx language is still developing, every Power Fx code document saved includes a language version stamp. If the language changes in incompatible ways, a “back compact converter” rewrites incompatible formulas when they’re next edited (showing a message with a link to the documentation for major changes). That way the language can evolve quickly but old code will still run.

Pro Code

The Power platform has connectors for many different data sources and web services; those are included with Power Fx, along with code components that allow Power Fx code to interact with JavaScript, and developers can write custom connectors to talk to other REST APIs and services.

Power BI has DAX — data analysis expressions — and M (the Power Query language) for data transformation and those won’t go away, but tools in the Power platform that don’t already have a language for customizing them will now use Power Fx. The options for calculated columns in “Dataverse” — the way data is stored in the cloud to be used with Power Apps or Teams apps (using Azure SQL, Cosmos DB and Azure Data Lake depending on the type of data) — are currently limited; they can become much richer with Power Fx, which will become the native way of working with data in both Dataverse and SharePoint, for rollups, business rules, and validation using formulas.

The same code in Power Fx and JavaScript (Microsoft)

It will also be used for declaring variables to customize chatbot interactions in Power Virtual Agents, adding custom logic in Power Automate (where the current expression language is fairly complex) both in the cloud and on desktop. In the next few months, Power Fx will also get new functions to work with AI Builder, so you can use more than the existing controls to work with AI models, predictions, and intelligent objects.

And all of that can be something both low code and pro developers collaborate on, using the tools they’re comfortable and productive with.

Feature image by Bruno Kelzer on Unsplash.

Group Created with Sketch.
THE NEW STACK UPDATE A newsletter digest of the week’s most important stories & analyses.