Microsoft Power Fx is a general purpose, strong-typed, declarative, and functional programming language developed by Microsoft as part of the Power Platform. It is specifically designed for use in low-code and no-code environments such as Power Apps and Dataverse. It is a language for expressing logic across Power Platform. It is expressed in user-friendly text.

The language was developed by a team led by Vijay Mital, Robin Abraham, Shon Katzenberger, and Darryl Rubin. This team took inspiration from Excel and other tools and languages like Pascal, Mathematica, and Miranda. This language heavily borrows from the spreadsheet model. Power Fx is available as Open-Source Software.

Power Fx Based on Excel

Power Fx uses a syntax similar to Excel formulas, with many of the same functions and operators available. Users familiar with Excel can leverage their existing knowledge and skills to quickly start building expressions and formulas in Power Apps and Dataverse.

For example, the basic arithmetic and logical operators in PowerFx (such as +, -, *, /, and &&) are the same as in Excel and certain common functions.

This table shows all of Power Fx’s pure functions, the ones marked in green are identical or very close to Excel’s version:

This overlap between Excel and Power Fx can be a significant advantage for users already comfortable working with Excel. It can help reduce the learning curve and make it easier to get started with Power Apps and Dataverse, which can increase productivity and accelerate development times.

However, it also includes several features not found in Excel, such as support for advanced data types, error handling, and functions for working with APIs and data sources.

Overall, the similarity between Excel formulas and Power Fx is a key advantage of the language and is one of the reasons why it has become such a popular tool for building low-code and no-code applications.

Using Formula Columns in Power Apps

Formula columns allow us to add business logic and calculations within Dataverse tables. The new formula columns are based on Power Fx, which has much more functionality than calculated columns. When creating new columns in Dataverse tables, the data type would be selected in Formula, and the calculation can be based on Text, Decimal, Two Options (boolean), and Date types.

To create a formula column, click on the new column button in the Columns area of the Maker portal and select the Formula data type from the list of column types. Give a display name, and then enter the formula you want to use based on the acceptable data types. In the example below, we are concatenating two strings.

Once the column is saved, it will show in the columns view as type Single Line of Text (or another data type), with a little fx icon next to it.

Finally, you can add the column to your form. Once the required fields that comprise the column are entered and the form is saved (either from Create action or Update action), the column will be populated or updated.

Compute Arithmetic Operations

In the following image, the Power Fx formula bar creates a formula column that computes the total price by performing arithmetic operations on two input columns. The return type of the formula is dynamically determined based on the data type of the resulting expression. Instead of updating cells, you can add controls wherever you want on a screen and name them for use in formulas. The formula editor supports IntelliSense to suggest formulas and errors in real-time.

Power Fx in Dataverse

We can reduce Java scripting to create commands and use Power Fx formulas to test and modify Dataverse data. Power Fx formulas can be used for calculated columns and rollups in Dataverse, a more modern and powerful alternative to existing facilities.

In Dataverse, Power Fx expressions are executed on the server side when data is queried or manipulated. This contrasts with Power Apps, where Power Fx expressions are executed in the client’s browser or mobile device.

In this example, we want to perform a calculation wherein the formula field will show the message “Discount Applicable” if the Product sale (Price) is larger than 600,000.

• The formula field will display “Discount Not Applicable” if the requirement is unmet.
• To achieve this, create a Dataverse Formula column (Is Discount Applicable) and select the Data type as Formula (Preview).
• Next, apply the below code inside the Formula box (Fx) as:
  If (Sales>400000, “Discount Applicable,” “Discount Not Applicable”)
• Now refresh the Dataverse table. You can view the new formula field (Is Discount Applicable) with the user-based condition as in the screenshot below.

When you create a calculated column or a view in Dataverse that uses a Power Fx expression, the expression is compiled and executed on the server when data is requested or modified. This means that the processing of Power Fx expressions is handled by the Dataverse service rather than by the client.

One important advantage of this approach is that it can help to improve performance and scalability since the server can handle the processing of complex expressions and queries more efficiently than a client device. It also means that users can access and manipulate large datasets without downloading all the data to their local devices.

However, this also means there are some limitations to using Power Fx in Dataverse since the expressions need to be compiled and executed on the server. For example, some complex expressions or functions may not be supported or have different performance characteristics than Power Apps.

Power Fx and C#

Power FX is a low-code language, often used by non-developers to create formulas and logic for business applications. C#, on the other hand, is a general-purpose programming language that requires more technical knowledge and experience.

The NuGet package provides serialization and deserialization capabilities for Power FX formulas, allowing you to use them in your C# code.

When using Power FX in C#, it is important to ensure that the formula syntax is correct and that any variables or data types used are compatible with the C# code.

Power FX and C# can be used to create powerful business applications that combine low-code functionality with custom programming logic. By leveraging the strengths of both languages, developers can create efficient, user-friendly, and scalable applications.

Power Fx vs. Business Rules in Dynamics

Power Fx and Dynamics Business Rules are tools used to build custom business logic in Microsoft’s Power Platform. Here are some of the key differences between the two:

• Power Fx is a more general-purpose programming language that can be used to build custom business applications on the Power Platform, while Dynamics Business Rules are specifically designed to automate and enforce business logic within Dynamics 365.
• Power Fx provides a wider range of functions and features than Dynamics Business Rules, allowing users to build more complex business logic and workflows. Dynamics Business Rules provide a more limited set of features designed specifically for automating and enforcing business rules within Dynamics 365.
• Flexibility. Power Fx provides more flexibility for building custom business logic and workflows, while Dynamics Business Rules are more rigid in their structure and are designed to enforce specific business rules within Dynamics 365.
• Power Fx follows a declarative programming path, while Dynamics Business Rules are designed to use a graphical user interface.

Power Fx Uses in Other Areas of Power Platform

PowerFx is a core component of the Microsoft Power Platform and is used in several different products and services within the platform. Here are a few examples:

• Power Apps: PowerFx is the primary expression language used in Power Apps to build custom applications. PowerFx expressions can be used in several contexts within Power Apps, including data validation, conditional formatting, and formula-based controls.
• Power Automate: PowerFx can be used in Power Automate to build workflows and automation processes. PowerFx expressions can be used to manipulate data, perform calculations, and make decisions within a flow.
• Power Virtual Agents: PowerFx can be used in Power Virtual Agents to build chatbots and conversational interfaces. PowerFx expressions can process user inputs, make decisions, and generate responses.
• Power BI: PowerFx can be used in Power BI to build custom calculations and measures. PowerFx expressions can perform complex calculations, create visuals, and build custom data models.
• Dataverse: PowerFx is the primary expression language used in Dataverse to manipulate and transform data. PowerFx expressions can create calculated columns, validate data, and generate views and reports.

Limitations of Power Fx

• Power Fx has limited support for complex data types such as arrays, objects, and binary data.
• While Power Fx offers many built-in functions for manipulating data, there are some advanced functions that are not available. For example, there is no built-in function for performing statistical analysis or for working with geographical data.
• Power Fx is designed to work with data within Dataverse, Microsoft List, and Excel, and it has limited support for integrating with external data sources. While it is possible to connect to external data sources using custom connectors, this requires additional setup and configuration.
• Power Fx does not provide a full-featured debugging environment and no built-in debugger, making it difficult to diagnose and fix issues in complex expressions.
• Limited support for current time functions: Dataverse has some restrictions on the use of current time functions in Power Fx expressions. For example, you cannot use the “Now()” function to retrieve the current date and time within a calculated column or a view. Instead, you need to use the “UtcNow()” function, which returns the current date and time in Coordinated Universal Time (UTC).
• Limited extensibility. While PowerFx allows you to create custom functions, these functions are limited in their scope and cannot be shared across multiple applications or environments.

Additional Resources

• https://learn.microsoft.com/en-us/power-platform/power-fx/overview
• https://learn.microsoft.com/en-us/power-platform/power-fx/formula-reference
• Line of Business Basics with PowerApps Dev Course | Pluralsight

Conclusion

• One of the key differences between Power Fx and other programming languages is its focus on simplicity and ease of use. Power Fx is designed to be accessible to non-technical users, allowing them to create complex expressions and workflows without the need for extensive programming knowledge.
• Power Fx is integrated with the Power Platform, which includes tools such as Power Apps, Power Automate, and Power BI. This integration makes it easy to build end-to-end solutions that incorporate data analysis, automation, and custom applications.
• Open-source: Power Fx is an open-source language, which means that the community can contribute to its development and improvement. This can lead to a more robust and flexible language over time.
• Overall, Power Fx’s focus on simplicity, ease of use, and interoperability makes it a unique and powerful tool for building low-code and no-code applications in various contexts.