Professional developers use great text

Microsoft Power Fx overview

  • 19 minutes to read


Microsoft Power Fx is the new name for the formula language for canvas apps. This overview and the related articles are constantly evolving as we extract the language from canvas apps, integrate it with other Microsoft Power Platform products, and make it available as open source. To learn more about and experience the language today, start with Get started with formulas in Power Apps and sign up for the Power Apps test for free.

Power Fx is the low-code language used in Microsoft Power Platform. It is a universal, strongly typed, declarative, and functional programming language.

Power Fx is expressed in a user-friendly text. It is a low-code language that developers can use directly in an Excel-like formula bar or in a Visual Studio code text window. The “low” in “low code” is due to the concise and simple nature of the language, which makes common programming tasks easier for both manufacturers and developers. It enables the full spectrum of development from no-code for those who have never programmed before to pro-code for experienced professionals without having to learn or rewrite “cliffs” in between. Different teams can work together and save time and money.


In this article we use the term Creatorwhen describing a function that can be used at both ends of the spectrum of programming skills. We refer to the user as developerwhen the feature is more advanced and likely beyond the realm of a typical Excel user.

Power Fx binds objects together with declarative spreadsheet formulas. For example, think of the property Visible of a UI control as a cell in an Excel worksheet with an associated formula that calculates the value based on the properties of other controls. The formula logic automatically recalculates the value, similar to a table, which affects the visibility of the control.

In addition, Power Fx offers mandatory logic if required. Worksheets usually don't have buttons to send changes to a database, but apps often do. The same expression language is used for both declarative and imperative logic.

Power Fx is provided as open source software. It's currently built into canvas apps for you to try out its functionality. We are in the process of extracting it from Power Apps for use in other Microsoft Power Platform products and as open source. For more information, see: Microsoft Power Fx on GitHub

This article gives an overview of the language and its design principles. For more information on Power Fx, see the following articles:

Think spreadsheet

What if you could create an app as easily as creating a worksheet in Excel?

What if you could use your existing spreadsheet knowledge?

These were the questions that inspired the creation of Power Apps and Power Fx. Hundreds of millions of people use Excel to create worksheets every day. Let's give them app build that is easy and uses Excel concepts they already know. By extracting Power Fx from Power Apps, we will answer these questions for building automation, a virtual agent or other domains.

All programming languages ​​included, including Power Fx Expressions: a way to represent a calculation using numbers, strings or other data types. For example, in most languages, multiplication expresses of and. The result of an expression can be inserted into a variable, used as an argument to a procedure, or nested in a larger expression.

Power Fx goes one step further. An expression in itself says nothing about what it calculates. It is up to the creator whether to place it in a variable or pass it to a function. In Power Fx, not only do you write a phrase that doesn't have a specific meaning, but you write one as well formulathat connects the expression with an identifier. You write as a formula for calculating. If or changes, it is automatically updated to a new value. The expression described a calculation, a formula gave this calculation a name and used it as a recipe. For this reason we refer to the Power Fx as Formula language.

For example, this formula from Stack Overflow searches for a string in reverse order. In Excel it looks like the following picture.

Screenshot of a formula bar in Excel with the formula: = RIGHT (A1, LEN (A1) - FIND ("|", SUBSTITUTE (A1, "", "|", LEN (A1) -LEN (SUBSTITUTE (A1, "", "")))) Cell A1 contains the text "Hello, World! It is great to meet you!" Cell A2 contains the text "you!" ::: image-end :::

Power Fx works with the same formula, replacing the cell references with control property references:

Screenshot of a Power Fx formula bar in Power Apps The formula is = RIGHT (Input.Text, Len (Input.Text) - FIND ("|", SUBSTITUTE (Input.text, "", "|", Len (Input.Text ) -Len (Substitute (Input.Text, "", "")))) In the input field below the formula, the text "Hello, World! It is great to meet you!" Is displayed letter by letter. Caption "shows the letters of the last word. When the full text is displayed in the input field, the word" you! "Is displayed in the" Caption "field. ::: image-end :::

If the control value is changed, the control will automatically recalculate the formula and display the new value. There is no event handler here, as would be the case in other languages.

Another example that uses a formula for the color of the screen. If the red, green, and blue sliders are changed, the background color will automatically change when it is recalculated.

There are no events for the slider, as is common in other languages. There is absolutely no way to explicitly set the property value. If the color isn't working as expected, you need to look at this one formula to understand why it isn't working. You don't have to search the app to find code that sets the property at an unexpected time. There is no time element. The correct formula values ​​are always retained.

Because the sliders are set to a dark color, the red, green, and blue labels change to white to offset. It does this by using a simple formula for the property for each label control.

The great thing about it is that it's isolated from what happens with color: these are two completely different calculations. Instead of large monolithic procedures, Power Fx logic is usually made up of many smaller formulas that are independent. This makes it easier to understand and allows for improvements without disturbing the existing logic.

Power Fx is a declarative language, just like Excel. The manufacturer defines the desired behavior, but it is up to the system to determine and optimize how and when this is to be achieved. To make this practical, most of the work is done using pure functions with no side effects, so Power Fx (like Excel) is also a functional language.

Always live

A crucial aspect of worksheets is that they are always active and changes can be seen immediately. Neither a compilation nor an execution mode in a worksheet. Whenever a formula is changed or a value is entered, the worksheet is immediately recalculated to reflect the changes. Detected errors are displayed immediately and do not affect the rest of the worksheet.

The same is implemented with Power Fx. An incremental compiler is used to continuously synchronize the program with the data it is working with. Changes are automatically propagated through the program diagram and affect the results of dependent calculations that may affect the properties of controls such as color or position. The incremental compiler also provides a rich formula editing experience with IntelliSense, suggestions, autocomplete, and type checking.

In the animation below, the order number is displayed in a label control that depends on the slider, although the labels below contain two errors. The app is very lively and interactive. The first attempt to correct the formula by typing in results in an instant red line and an error which appears as it should, but the app continues to run.

Entering a causes the area to add the employee table, retrieve the metadata for that table, and provide suggestions for columns immediately. We just went from one table to another via a relationship and the system made the necessary adjustments to the app's references. The same thing happens when adding.

After each change, the slider continues its last value and all variables retain their value. In the meantime, the order number was still displayed as desired on the upper label. The app was live the whole time and was processing real data. We can save, exit, and others can open and use it like Excel. There is no build step, no compilation step, just a publish step to determine which version of the app is ready for users.

Low code

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

For example, to look up an employee's first name for an order, write the Power Fx as shown in the animation below. In addition to Excel concepts, only the dot notation is used here „.“ added to drill down into a data structure, in this case. The animation shows the relationship between the parts of the Power Fx formula and the concepts that have to be explicitly coded in the corresponding JavaScript.

Let's take a closer look at all of the things Power Fx does for us and the freedom it has to tweak because the formula was declarative:

  • Asynchronous: All data operations in Power Fx are asynchronous. The manufacturer does not need to specify this or synchronize operations after the call has ended. The manufacturer does not have to be aware of this concept at all, he does not have to know what a promise or a lambda function is.

  • Local and remote: Power Fx uses the same syntax and functionality for data stored locally in memory that remotely connects to a database or service. The user does not have to think about this distinction. Power Fx automatically delegates what it can to the server to process filters and sort them more efficiently.

  • Relational data: Orders and Customers are two different tables with a one-to-one relationship. The OData query requires a $ expand knowing the foreign key, similar to joining in SQL. The formula has none of this. In fact, database keys are another concept that the manufacturer doesn't need to know about. The manufacturer can access the entire diagram of relationships from a data set using simple dot notation.

  • projection: When writing a query, many developers write, which shows all columns of data. Power Fx analyzes all columns used throughout the app, including across formula dependencies. The projection is optimized automatically, and here too a manufacturer does not need to know what “projection” means.

  • Get only what is needed: In this example, the function implies that only one record should be retrieved, and that is all that is returned. As more records are retrieved using the function that could qualify for thousands of records, only one page of data, on the order of 100 records per page, is returned at a time. The user has to gesture through a gallery or data table to view additional data, and this is automatically displayed for him. The manufacturer can consider large amounts of data without having to think about restricting data requirements to manageable blocks.

  • Runs only when needed: We have defined a formula for the label control property. If the selected variable changes, it is automatically recalculated and the label updated. The manufacturer didn't have to write an OnChange handler for the selection and didn't have to remember that this labeling depended on it. This is declarative programming, as mentioned earlier. The manufacturer has specified what should be included in the label and not how or when it should be retrieved. If this label is not visible because it is on an invisible screen or its property is incorrect, we can postpone this calculation until the label is visible and effectively remove it if this is infrequent.

  • Excel syntax translation: Excel is used by many people, most of whom know that the ampersand (&) is used for string concatenation. JavaScript uses a plus sign (+) and other languages ​​use a period (.).

  • Display names and localization: is used in the Power Fx formula, while is used in the JavaScript equivalent. In Microsoft Dataverse and SharePoint, in addition to a unique logical name, there is a display name for columns and tables. The friendly names are often much more user-friendly, as in this case, but they have another important property, which is that they can be localized. If you have a multilingual team, each team member can view table and field names in their own language. In all use cases, Power Fx ensures that the correct logical name is automatically sent to the database.

No code

You don't need to read or write Power Fx to express logic. There is a lot of customization and logic that can be expressed through simple switches and UI builders. These low-code tools are designed for reading and writing Power Fx to ensure there is enough wiggle room for someone who wants to develop the language further. It should be noted that tools with little code never offer the full expressiveness of the full language. Even when used with no-code builders, the Formula Bar is in the foreground in Power Apps to educate the creator about what actions are being taken on their behalf so they can learn Power Fx.

Now let's look at a few examples. In Power Apps, the properties pane provides low-code switches and sliders for the properties of the controls. In practice, most property values ​​are static. With the color generator you can change the background color of the. Notice that the formula bar reflects this change and updates the formula in another call. You can switch to the formula bar at any time and go one step further - in this example with to adjust the color. The color property is still displayed in the Properties window when hovered over it, but becomes a fxIcon appears and you are taken to the formula bar. This works in two ways: removing the call returns the color to something the Properties window can understand, and you can use it to set a color again.

Here is a more complicated example. The gallery shows a list of Dataverse employees. Dataverse provides views over tabular data. We can choose one of these views and the formula will be modified to use the function with that view name. The two drop-down menus allow you to select the correct table and view without touching the formula bar. Let's say you want to go further and add a sort. We can do this in the formula bar and the properties window will again show an FX symbol and guide changes to the formula bar. If we simplify the formula to something the property sheet can read and write, it can be used again.

These were simple examples. We believe Power Fx is a great language to describe interactions without code.It is concise, powerful and easy to analyze and offers the often required leeway "without cliffs" up to code-poor.

Per code

Low-code manufacturers sometimes develop things that require the help of an expert or are taken over by a professional developer for maintenance and improvement. Professionals also appreciate that low-code development can be easier, faster, and cheaper than creating a professional tool. Not every situation requires the full power of Visual Studio.

Professionals want to use professional tools to be the most productive. Power Fx formulas can be stored in YAML source files that can be easily edited with Visual Studio code, Visual Studio, or any other text editor, and allow Power Fx to be source controlled using GitHub, Azure DevOps, or any other source control system.

Power Fx supports formula-based components for sharing and reuse. We announced the support of parameters for component properties, which enables the creation of pure custom functions with further enhancements.

Power Fx is also great for putting together components and services developed by professionals. Standard connectors provide access to hundreds of data sources and web services, custom connectors enable Power Fx to communicate with any REST web service, and code components enable Power Fx to interact with fully custom JavaScript on the screen and page.

Design principles


Power Fx is aimed at the manufacturer target group whose members have not been trained as specialists in development. Wherever possible, we use the knowledge that this target group already knows or can take up quickly. The number of concepts required to be successful is kept to a minimum.

Being simple is good for developers too. For the developer audience, it is a low-code language that reduces the time it takes to create a solution.

Excel constancy

The Microsoft Power Fx language is heavily based on the Excel formula language. We would like to use the Excel knowledge and experience of the many manufacturers who also use Excel. Types, operators and function semantics are as close as possible to Excel.

If Excel doesn't provide an answer, let's look at SQL next. The second most widely used declarative language after Excel, SQL can provide guidance on data operations and strong typing that Excel does not.


The manufacturer describes What should do his logic, and not exactly, how or when she should do it. This enables the compiler to be optimized by running operations in parallel, deferring work until it is used, and prefetching and reusing cached data.

For example, in an Excel worksheet, the author defines the relationships between cells, but Excel decides when and in what order formulas are evaluated. Similarly, formulas in an app can be viewed as "recalculating" as needed based on user actions, database changes, or timer events.


We prefer pure functions that have no side effects. This results in logic that is easier to understand and gives the compiler the greatest freedom to optimize.

Unlike Excel, apps inherently change status - for example, apps have buttons that save changes to the record in a database. Some functions therefore have side effects, although we limit this as much as possible.


If possible, the added functionality will work well with the existing functionality. Powerful features can be broken down into smaller pieces that are easier to use independently.

A galleryFor example, control has no separate - and properties. Instead, the and functions are combined into a single property. The UI for expressing and behavior overlay the property using a bidirectional editor for that property.

Strongly typed

The types of all values ​​are known at compile time. This enables the early detection of errors and extensive suggestions when creating the document.

Polymorphic types are supported, but before they can be used their type must be pinned to a static type and that type must be known at compile time. The IsType and AsType Functions are provided for testing and shaping types.

Reference type

Types are derived from their use without being declared. For example, if you set a variable to a number, the variable type is set as a number.

Inconsistent type usage results in a compilation error.

Locale-sensitive decimal separators

In some regions of the world, a period (.) is used as a decimal separator, whereas in other regions a comma (,). This is also the case with Excel. This is usually not done in other programming languages ​​which generally have a canonical period (.) as a decimal separator for all users worldwide. To be as accessible as possible to manufacturers at all levels, it is important that there is a decimal number for a person in France who has used this syntax all their life.

The choice of the decimal separator has a cascading effect on the list separator used for function call arguments and the concatenation operator.

Decimal separator of the author's languagePower FX decimal separatorPower FX list separatorPower FX concatenation operator
. (Point). (Point), (Comma); (Semicolon)
, (Comma), (Comma); (Semicolon);; (double semicolon)

More information: Global support

Not object oriented

Excel is not object-oriented, and neither is Power Fx. For example, in some languages ​​the length of a string is expressed as a property of the string, as in JavaScript. Instead, Excel and Power Fx express this as a function, namely as a.

Components with properties and methods are object-oriented, and Power Fx works fine with them. Whenever possible, however, we prefer a functional approach.


Manufacturers can create their own components and functions using Power Fx. Developers can create their components and functions by writing JavaScript.

Developer friendly

While developers are our main target audience, we try to be developer-friendly wherever possible. Unless this conflicts with the above design principles, we'll do things in a way that a developer will appreciate. For example, Excel can't add comments, so we'll use C-like row and inline comments.

Language evolution

The advancement of programming languages ​​is both necessary and difficult. Everyone is right to be concerned that any change, no matter how well-intentioned, could break existing code and force users to learn a new pattern. Power Fx takes backward compatibility seriously, but we also firmly believe that we won't always get it right the first time and learn together what is best as a community. We need to keep evolving and Power Fx has developed language development support from the start.

A language version stamp is included in every saved Power Fx document. If we want to make an incompatible change, we write what is known as a back-compat converter, which automatically rewrites the formula the next time it is edited. If there is something important that we need to inform the user about, we will also display a message with a link to the documents. With this feature, we can continue to load apps that were created with the preview versions of Power Apps from many years ago, despite all the changes that have taken place since then.

For example, we introduced the feature to display an error banner with a red background.

Users loved it, but also asked us how to display a success banner (green background) or an information banner (blue background). So we came up with a more general function that takes a second argument for the type of notification. We could have just added and left it as it was. But we didn't, we replaced with. We removed a feature that was previously in production with something else. Since there would have been two ways to do the same thing, this would have created confusion, especially for new users, and most importantly, increased complexity. Nobody complained, everyone appreciated the change and then moved on to their next notification function.

This is what the same app looks like when loaded into the latest version of Power Apps. The user did not need to take any action to perform this conversion. It took place automatically when the app was opened.

This feature allows Power Fx to evolve faster and more aggressively than most programming languages.

No undefined value

Some languages ​​like JavaScript use the concept of a undefined Value for uninitialized variables or missing properties. We have avoided this concept for the sake of simplicity. Instances that would not be defined elsewhere are treated either as errors or as blank values. For example, all uninitialized variables begin with an empty value. All data types can assume the value empty.

Similar articles

Data types
Operators and Identifiers
Imperative logic
Global support
Expression grammar
YAML formula grammar
Formulas in Power Apps