• User defined functions (UDFs) can now include behavior functions with side effects, such as Set, Collect, Reset, and Notify. Declarative is always best, so use this facility only when you must. When you do, wrap the formula in { } and you can then use the ; (or ;;) chaining operator.
• User defined types (UDTs) enable tables and records to be passed in and out of UDFs. UDTs also enable bulk conversion of JSON untyped objects to typed objects, particularly useful with web APIs. Welcome the new Type and RecordOf functions, an expanded role for the AsType and IsType functions, and a new parameter for ParseJSON.
• Enhanced component properties (ECPs) have moved to preview. With any remaining feedback, we plan to take them to general availability in the next few months. ECPs enable the ability to share logic across apps through a component library.

This is the last major update to UDFs planned before we start down the road to general availability. UDTs will be on the same timeline. Now is the time to take this functionality through its final paces and provide feedback before the design is locked; please leave feedback in the community experimental features forum. Both features are experimental and require turning on these switches in Settings > Updates > Experimental:

Behavior UDFs

Named formulas depend on being declarative, something the system can defer and recalc based on changes in the app. They can’t have side effects, such as incrementing a variable, or this wouldn’t be possible. UDFs to date have built on top of named formulas by adding parameters, but still had to be declarative.

Obviously, that is limiting for an app. We need buttons, buttons that do important things like updating a database. We’d like to be able to put that logic in a UDF too. And now you can, by wrapping the UDF in curly braces:

CountUp( increment : Number ) : Void = {
    Set( x, x+increment );
    Notify( $"Count: {x}" );
};

This simple example will increment the global variable x and display a notification with the result each time CountUp(1) is called from, say, the OnSelect formula of a Button control.

This is a huge step for reuse and manageability. Now you can extract and parameterize your action logic and reuse it throughout your app, having only one source of truth for that logic that is easier to maintain.

For more information and examples, see Behavior user defined functions.

User Defined Types

User defined types enable UDFs to take in and return records and tables. Until now, UDFs were limited to scalar data types, such as Number, Text, DateTime, etc.

The new Type function is the key. Use it to define a named formula for a type, using the same syntax you would use for a literal record or table value. For example, imagine you had a table of paper sizes:

PaperSizes =
[ { Name: "A4", Width: 210, Height: 297 },
  { Name: "Letter", Width: 216, Height: 279 },
  { Name: "Legal", Width: 216, Height: 356 } ];

We can define types for a single paper as a special kind of named formula that uses the := assignment operator and Type function. The syntax used within the Type function is the same as the literal record values in the PaperSizes definition, with specific values replaced by their data type names:

PaperType := Type( { Name: Text, Width: Number, Height: Number } );

We can now use this type to pass a single paper size into a UDF:

PaperArea( Paper: PaperType ): Number = Paper.Width * Paper.Height;

And we can call our UDF with:

PaperArea( First( PaperSizes ) ) // returns the number 62370

We can define a type for a table, matching the type of PaperSizes:

MultiPaperType := Type( [ PaperType ] );

And then define a function to filter a table of paper sizes:

LargePaperSizes( Papers: MultiPaperType ) : MultiPaperType =
    Filter( Papers, PaperArea( ThisRecord ) > 70000

And then we can call it with:

LargePaperSizes( PaperSizes )
// returns [ { Name: "Legal", Width: 216, Height: 356 } ]

For more information and examples, see User defined types.

JSON and Untyped ❤️ UDTs

UDTs are great for UDFs. But they have another great use case: converting an untyped object to a typed object. Imagine that JSON was returned from a web API for another set of paper sizes:

MorePaperSizes =
"[ { ""Name"": ""A0"", ""Width"": 841, ""Height"": 1189 },
{ ""Name"": ""A6"", ""Width"": 105, ""Height"": 148 } ]";

You probably know that you can use the ParseJSON function to convert this to an Untyped object.

MoreUntyped = ParseJSON( MorePaperSizes )

From which you can extract individual elements from the JSON by casting them explicitly or implicitly at the point of use. But since the structure is untyped and potentially heterogenous, it cannot be used with functions such as AddColumns which requires a homogenous Power Fx table:

AddColumns( MoreUntyped, InStock, true ) // error

With UDTs, we can now convert this JSON directly to a Power Fx typed object by providing the type as the second parameter to ParseJSON:

MoreTyped = ParseJSON( MorePaperSizes, MultiPaperType )

And now we can add a column:

AddColumns( MoreTyped, InStock, true ) // OK

The IsType and AsType functions have also been overloaded to take an untyped object and type arguments. Web APIs that return an untyped object can now be easily converted to a typed object.

Feedback, please!

These are experimental features, and for good reason, we are still making changes. There are a lot of nuanced details with UDFs and UDTs we want to make sure we get right. We’d love to hear your feedback on how it works, please let us know in the community experimental features forum.

The post User defined functions, user defined types, and enhanced component properties move forward appeared first on Microsoft Power Platform Blog.

Explaining a Formula

Copilot’s “Explaining a Formula” feature bridges the gap between technical and non-technical audiences. By providing a human-readable description of the Power Fx code, users can easily communicate the purpose and functionality of their formulas. This feature is especially helpful when someone is first opening an app they are not familiar with, as it allows them to quickly get up to speed on the app’s logic and functionality, regardless of their technical background. For example:

Original formula:

Filter(Orders, Status = "Shipped" && TotalAmount > 1000)

 

Explained in natural language:

“Retrieve orders that have been shipped and have a total amount greater than $1000.”

 

Note: Explaining a Formula is being released to General Availability in English only, with plans to quickly follow with support in other languages.
Generating Power Fx from Natural Language

Copilot is getting another feature that lets users write formulas with natural language comments. The user just writes what they want, and Copilot creates the Power Fx code for them. This feature uses an existing option in the formula bar, where users can write code comments with // syntax. To get the Power Fx suggestion from the comment, the user types // followed by their comment, waits a bit, and sees the suggestion. The user can press tab to use the suggestion or type over it to change it. If there is no suggestion, the model couldn’t make a valid function from the comment, and the user can try a different comment. This feature not only simplifies formula creation but also reduces the risk of syntax errors. For Instance:

Natural language comment:

“// Calculate the average revenue per customer.”

 

Generated Power Fx:

Average(Orders, TotalRevenue / CountRows(Customers))

 

Note: Comment generated Power Fx is being released as a preview feature with English only support, and the rollout will be gradual as capacity becomes available. This means that some users will be able to try out the feature and provide feedback, while the team works on ensuring there is a positive user experience for our global footprint of Power Apps makers.
Improvement to Inline Copilot Functionality

As an added bonus, the new advanced models powering formula explanations and authoring can also speed up the Formula authoring experience for select controls by triggering the Copilot button in the inline action bar. The Copilot generates formulas for supported controls (Gallery, Data table, Text box, Drop down, Combo box, and Text label) in the Ideas panel and can be modified based on the instructions you provide.

To add a formula, select the control and click on Copilot icon.

Note: This feature was previously available as Power Apps Ideas, and is still in preview. Preview features can be managed in the Power Platform admin center.
Benefits of Using Copilot Features

By leveraging these new Copilot features, users can improve their productivity, enhance collaboration, and reduce errors. Copilot accelerates formula development by providing instant explanations and generating code from plain language. With natural language descriptions, users can collaborate seamlessly with business stakeholders, ensuring everyone understands the app’s logic. Copilot’s error highlighting and clear explanations help users catch mistakes early, leading to more reliable formulas.

Overall, the new Copilot features for Power Fx empower users to create robust apps efficiently. Whether you’re a seasoned developer or a citizen developer, leveraging these capabilities will enhance your Power Apps experience. We value your feedback and would love to hear your thoughts on these new features. To share your experience and provide suggestions, please visit this post on the Power Apps community forum. Your feedback will help us continue to improve and enhance the Power Apps experience for all users. Happy building!

See additional feature documentation on Microsoft Learn

The post Introducing new Copilot features for Power Fx appeared first on Microsoft Power Platform Blog.

The impacted functions are:

• AddColumns, DropColumns, RenameColumns, or ShowColumns
• Search
• GroupBy or Ungroup
• DataSourceInfo

For example, today, this formula adds a Miles column to a table, calculated from the Kilometers column:

AddColumns( Distances, "Miles", Kilometers * 0.6214 )

Tomorrow, write the formula with the new column name just as you would an existing column of the table, without double quotes:

AddColumns( Distances, Miles, Kilometers * 0.6214 )

It’s a subtle, but important difference.  The use of a text string gave the impression that this name could be dynamic when it could not which led to maker confusion.  It was inconsistent with other places where columns are used and defined, such as in the calculation at the end of the formula above and in a record definition.

This change also enables you to use display names.  If the Contacts table is in Dataverse, you would have previously needed to use logical names to reference the columns:

Search( Contacts, TextInput1.Text, "cr43e_firstname", "cr43e_lastname" )

No longer, this can now be written much more elegantly as:

Search( Contacts, TextInput1.Text, 'First Name', 'Last Name' )

As shown here, use single quotes to insert a space or other special character in the name, as you would with variable names.

But wait… Aren’t we going to break all the existing apps in the world?  Power Apps has a mechanism to update app formulas automatically.  This change is only a syntax change, the behavior of these functions does not change, making it perfect to use this mechanism.  Your apps will be automatically updated to the new syntax when they are loaded into Studio at or after version 3.24042. In general, you don’t need to do anything except to start using the new syntax for new formulas.

This is a part of Power Apps moving to Power Fx 1.0 that we could do without needing a feature switch. There are two more functions, SortByColumns and Validate that will be getting similar treatment, but since those could introduce a breaking change we decided to do that under the upcoming Power Fx 1.0 compatibility switch.

More examples:

DropColumns( Distances, "Kilometers" )                          // before
DropColumns( Distances, Kilometers )                            // after

RenameColumns( Distances, "Kilometers", "Km" )                  // before
RenameColumns( Distances, Kilometers, Km )                      // after

ShowColumns( Distances, "Kilometers" )                          // before
ShowColumns( Distances, Kilometers)                             // after

GroupBy( Sales, "State", "Remainder" )                          // before
GroupBy( Sales, State, Remainder )                              // after

Ungroup( GroupedSales, "Remainder" )                            // before
Ungroup( GroupedSales, Remainder )                              // after

DataSourceInfo( IceCream, DataSourceInfo.Required, "Flavor" )   // before
DataSourceInfo( IceCream, DataSourceInfo.Required, Flavor )     // after

If you have any concerns or questions, please leave them in the comments for this blog post.  Thanks!

The post Power Fx: Column names escape double quotes appeared first on Microsoft Power Platform Blog.

This highly anticipated feature provides a secure layer of protection for fixed credential connections such as SQL Server basic auth (i.e., connection string username and password.)  All new apps now use this feature.  Older apps must be converted to use this feature (if not already converted) and previously shared connections unshared.

With this new feature, connections are no longer shared with other makers. Instead, the app now interacts with a connection proxy object that only allows queries from the app and also only allows the types of queries that are in the app.  The most prominent use of implicitly shared connections is for SQL Server. Admins can use a report available in the COE toolkit to find apps that do not use this feature.      

For more information, see the article on Secure Implicit Connections. 

The post General availability of Secure Implicit Connections appeared first on Microsoft Power Platform Blog.

Today we are pleased to announce the general availability of open-source Power Fx 1.0. “1.0” means that the language definition is now stable and breaking changes will be managed and communicated. It is now ready for you to integrate in your production work loads. It will be coming to Power Apps later this year, details below.

Power Fx has always been about leverage. It was created to leverage the knowledge of millions of Excel users who already know how to write a formula. It expanded that leverage to all corners of the Power Platform, where learning how to write Power Fx in Power Apps meant that you could leverage that knowledge in Power Virtual Agents. And now with the release of Power Fx 1.0 it expands to open-source and all the places that you can imagine using it in your own projects.

What does this mean for Power Apps?

Immediately, not much. Power Apps today uses a pre-1.0 version of Power Fx. Over the next several months, we will be updating to 1.0 in order to match the rest of the Power Platform. Most of this work will be done under a new experimental feature switch which will be coming soon:

How is the formula language in Power Apps today and Power Fx 1.0 different? There are lots of small changes, most of which are nuances that most makers will never notice. For example, previously Sin(variable) would return a Blank() if variable was unitialized, and now it will return a 0 consistent with Excel. Very likely that will not impact anyone, but it could, and so we are going to treat these as breaking changes and have the switch above to enable the new semantics. When complete, new apps will be created with Power Fx 1.0 while existing apps will continue to run on the old language. No apps will break. We will publish a list of all these changes.

Why make changes now? Power Fx has evolved within Power Apps for many years and there were lots of internal inconsistencies and inconsistencies with Excel. Power Apps has a feature for updating the language in place but not all Power Fx hosts will have such a facility. Before publishing Power Fx 1.0, it was time clean up and stabilize the language.

Decimal

One of the bigger changes is the addition of a decimal data type. Business applications depend on precise currencies and quantities which is why 98% of Dataverse’s numeric fields are decimal, currency, or integers. Floating point is available but rarely used. One reason is that floating point is well known for rounding issues, resulting in the occasional math bug report in Power Apps. For example, 1.2-1.0 is very close to, but not exactly 0.2 in floating point. Supporting decimal math was a requirement for Power Fx to find a home in Dataverse.

With Power Fx 1.0, decimal is the default numeric type. For most hosts, we use the C#/.NET definition of a decimal capable of precisely holding the number 79,228,162,514,264,337,593,543,950,335, with the decimal point placed anywhere within. That is many, many times the range and precision needed for financial calculations and gives us a much larger range for integers than floating point provides. It is 10 times the number of atoms in the human body, 60,000 times the number of milliliters of water in the oceans, oh and it absolutely dwarfs the gross domestic product of all countries on Earth.

Of course, floating point has its place, in particular for scientific calculations and for calculation speed. Not to fear, it is still available too. We’ll have more details as we roll out decimal to Power Apps as part of Power Fx 1.0 compatibility.

What does this mean for your projects?

You can now easily add Excel-like calculations and low code customizations to your own projects. Leverage open-source Power Fx 1.0 in anything you do, it as another tool in your toolbox. As open-source, Power Fx 1.0 is available for you to explore on NuGet and at https://github.com/microsoft/power-fx. It is trivial to use:

1. Create a new C# “Console App” in Visual Studio.
2. Use top level statements.
3. Install the package Microsoft.PowerFx.Interpreter from package source NuGet.org.
4. Copy this code into Program.cs:

using Microsoft.PowerFx;
using Microsoft.PowerFx.Types;
var engine = new RecalcEngine();
engine.UpdateVariable("base", 123456780000000m);
string formula = "Sum( base, 12345.6789, 900000.00000123456789 )";
decimal result = ((DecimalValue)engine.Eval(formula)).Value;
Console.WriteLine($"Deicmal: {result}");

Compile and run your console app and behold the output Decimal: 123456789012345.67890123456789, a C# decimal number that can’t be expressed in floating point, that used the Excel Sum function to aggregate three values including a variable. This example just scratches the surface with facilities for adding your own custom functions and connecting with external data sources, you can see more in Joris de Gruyter’s Power Fx demo at Build 2023. We even include an npm React formula bar based on Monaco the editor in Visual Studio Code for you to make editing formulas easy with IntelliSense.

Road ahead

Our mission is to create the easiest and most powerful system to express business logic for everyone, everywhere.

What comes next? As discussed, getting Power Apps on Power Fx 1.0 is a priority. We are also actively working on improvements to Power Fx hosting in Dataverse formula columns and Dataverse low code plugins. We’ll have more to talk about and show at the Microsoft Power Platform Conference in October.

Will there be a Power Fx 1.1 or 2.0? Yes, in time, there will be more versions of the language but we are in no rush. Changes will be driven by the needs of our makers and hosts and will be carefully managed. Engage with the community at https://github.com/microsoft/power-fx and let us know what you would like to see next.

The post Leverage Power Fx 1.0 appeared first on Microsoft Power Platform Blog.

In addition to calling unbound actions, authors can now access bound actions as well. Previously there was a limit of two call levels. This restriction is now lifted.

For documentation details on how to use this feature, please see the Power Apps documentation on how to Work with Dataverse actions in Canvas Apps. See also the formula language features that enable working with Untyped objects.

The post Direct Power Fx Dataverse actions support now in preview appeared first on Microsoft Power Platform Blog.

Today, when you author a Power Fx query expression to get data, for instance a Filter, Power Apps will show a double blue underline to indicate when an expression cannot be run (or “delegated”) to the data source’s server. Rather than return no data Power Apps will fetch the first 500/2000 unfiltered records and finish the query operation with the data it can retrieve. While this can be super helpful at times, it can lead to wrong results if you are not careful. And your app will likely be slower if you use workarounds to overcome these limitations or if you bring back more data than you need to. These new delegation features significantly reduce the number of scenarios where the Power Fx query expression is not delegated to the data source server.

First, Power Fx expressions that require asynchronous evaluation will now be delegated. This means you do not have to divide up the parts of an expression into separate formulas for it to be correctly delegated. The parts of the expression that require separate evaluation will automatically be ordered and evaluated independently for you. Named formulas depend on this feature.

Secondly, the list of functions listed below will no longer break delegation. They will be evaluated on the client and evaluated for a delegated expression.

• GUID
• IsToday
• Now
• Rand
• RandBetween
• Today

These first two features roll out starting June 12th and will be in all commercial regions by June 26.

Finally, we are also excited to announce the experimental preview of UpdateIf. UpdateIf will request the set of records that meet the If criteria and then also expands delegation range to be the greater of the (500/2000) row limit or the page size limit. Additionally, it will fully delegate to the back-end data source when it is supported.

RemoveIf will follow under this same feature flag. Both features delegate by identifying individual records and send the update/removes to the server.

The UpdateIf feature has started rolling out in some regions and will be in all commercial regions by June 21.

The post Delegation improvements – Async, UpdateIf, and miscellaneous functions appeared first on Microsoft Power Platform Blog.

Today we are announcing a new Power Fx formula editing experience in Power Apps Studio.  The new formula editor is available an experimental feature that we encourage you to turn on in your apps and try out!  The new editor is based on the powerful Monaco shared code editor component which allows us to provide you with better performance, a consistent editing experience across Studio, and additional error highlighting improvements.  We are very eager to hear your feedback on this as we continue to iterate on Power Fx editing. 

Formula Bar History

Some of you may remember that back in back in 2019, we introduced an “Enhanced Formula Bar.” This was our first attempt at a new formula bar architecture, and it brought many top customer requested improvements to the formula editing experience.  Then, in 2021 we announced that many of those features were being merged into the original formula bar and the “Enhanced Formula Bar” was being retired.  We promised in 2021 that we were starting work on a new iteration of the Studio formula bar that would allow us to bring back the goodness of Enhanced Formula Bar, and today is the first step of this journey!

Improvements to Power Fx editing

Power Fx is central to building rich apps, so we endeavor to provide an experience that is helpful, performant, and that makes Power Fx easy to write and maintain.  Here are some improvements you will see today.

Consistent editing

When you opt into this feature, you will see a different editor in the main Studio formula bar at the top of the canvas area, and when you edit control properties in the Advanced pane along the right. With this, the Power Fx editing experience is now consistent wherever you edit formulas within Studio.

Improved suggestions and completion

Completion suggestions and signature help is faster, more performant, and is shown with a more modern-looking experience:

Enhanced error highlighting

Customers told us that one of the most missed features of the Enhanced Formula Bar was the error highlighting along the right-hand side of the editor.  This is now back in the product with the new experimental feature.  This lets you quickly identify where in your formula block you have an error, which is particularly helpful if you have very long formulas. 

In the queue and coming next

We are going to iterate on this and push out new features when they are ready.  Right now, there are a couple things you might notice are not there including:

• The Fx dropdown which lists Power Fx functions
• Find and replace
• Properties grouped by category in the left-hand side dropdown
• Certain syntax and control highlighting within the editor

We have work booked to eventually add back the most important features, but we need your help prioritizing.  Let us know which of these we should be prioritizing and if you have other requests or ideas for improvement.

Opt in today!  Turn it on for your apps

This is an experimental feature, which means it is off by default.  You will need to opt in by flipping the switch in the Upcoming features dialog.  This switch applies to individual apps, so if you turn it on within one app that means you’ll only see the new experience in that app.  Turn it on explicitly in each app for which you want to use the new experience.

Setting us up to deliver more, faster editing improvements in the future

Behind the scenes, this change to a standardized editor lays important architectural groundwork for many future improvements that will be coming to Power Fx editing within Studio.  Customers have asked us for several enhancements to the editing experience like line numbering, block collapsing, and a dockable or full-screen editor.  While these changes aren’t there yet, we’ve eased the way for us to make faster incremental improvements going forward.  As usage of Power Fx grows across Power Platform, we endeavor to make these features available wherever Power Fx editing happens.  Our direction here is to have this be the core experience, eventually removing the existing formula bar in favor of this new one.  This makes your feedback critically important!  We’d love to hear what you would like to see next and what would make the biggest positive impact to you as a maker.  Give us feedback by creating a new post in this thread: Power Apps Experimental Features community forum

The post Try the new Power Fx Formula Bar in Power Apps Studio appeared first on Microsoft Power Platform Blog.

Without this feature, it has been common for authors to use Power Automate to call Dataverse. However, calling Dataverse directly from Power Fx provides significant performance benefits (and ease of use) and should be preferred for direct transactional reads and updates.

This feature update also allows authors to work with untyped object fields for both inputs and outputs, On the input side, for instance, many Dataverse actions require an untyped object as an argument. You can now pass these arguments in by using ParseJSON to convert a Power Fx record into an untyped object. On the output side, for actions that return untyped objects, you can simply ‘dot’ into returned objects’ properties. You will need to cast specific values for use in specific contexts for use in Power Apps (such as a label.)

Working with untyped fields is not restricted to Dataverse. It works for all types of connectors and provides basic ad-hoc dynamic schema support.

For documentation details on how to use this feature, please see the Power Apps documentation on how to work with Dataverse in Canvas Apps. See also the formula language features that enable working with untyped objects.

The post Call Dataverse actions directly in Power Fx appeared first on Microsoft Power Platform Blog.

Welcome Named Formulas. A funny name, that derives from how this feature appears in Excel, in the “Name Manager.” In Excel, you can name any cell and refer to that name throughout the workbook. It adds a level of indirection that allows you to move that cell reference by name rather than by location. And inspiring the introduction into Power Fx, it also allows you to bind a name to a formula that isn’t in a cell.

Today in Power Fx, you write formulas for the properties of the controls in your app. You can refer to those properties from other formulas to reuse the result of calculation. But you are limited by the controls and their properties. What if you could effectively create your own properties, create your own points of reuse?

Advantages of Named Formulas

Enough theoretical preamble, what if you could write this in an App.Formulas property:

UserEmail = User().Email;
UserInfo = LookUp( Users, 'Primary Email' = User().Email );
UserTitle = UserInfo.Title;
UserPhone = Switch( UserInfo.'Preferred Phone',
                    'Preferred Phone (Users)'.'Mobile Phone', UserInfo.'Mobile Phone',
                    UserInfo.'Main Phone' );

These are formulas in the truest sense of the word. They express how to calculate the UserEmail, UserInfo, UserTitle, and UserPhone from other values, much like F = m * a in Physics calculates force. This logic is now encapsulated and can be used throughout the app and can be updated in this one location. It can be changed from using the Dataverse Users table to using the Office 365 connector without needing to change formulas in the rest of the app.

These formulas don’t say anything about when these should be calculated or how these should be calculated. They are truly declarative. They provide a recipe only.

Ok, you are probably wondering, why use this? Why not just use Set in App.OnStart to accomplish the same thing? You certainly can and we have no intention to ever take that ability away. State variables and Set will always have their place.

Named formulas have advantages:

• The formula’s value is always available.  There is no timing dependency, no App.OnStart that must run first before the value is set, no time in which the formula’s value is incorrect.  Named formulas can refer to each other in any order, so long as they don’t create a circular reference.  They can be calculated in parallel.

• The formula’s value is always up to date.  The formula can perform a calculation that is dependent on control properties or database records, and as they change, the formula’s value automatically updates.  You don’t need to manually update the value as you do with a variable.  

• The formula’s definition is immutable.  The definition in App.Formulas is the single source of truth and the value can’t be changed somewhere else in the app.  With variables, it is possible that some code unexpectedly changes a value, but this is not possible with named formulas. That doesn’t mean a formula’s value needs to be static – it can change – but only if dependencies change.

• The formula’s calculation can be deferred.  Because its value it immutable, it can always be calculated when needed, which means it need not actually be calculated until it is actually needed. If the value is never used, the formula need never be calculated.  Formula values that aren’t used until screen2 of an app is displayed need not be calculated until screen screen2 is visible.  This can dramatically improve app load time.  Named formulas are declarative and provide opportunities like this for the system to optimize how and when they are computed.

• Named formulas is an Excel concept. Power Fx leverages Excel concepts where possible since so many people know Excel well.  Named formulas are the equivalent of named cells and named formulas in Excel, managed with the Name Manager.  They recalc automatically like a spreadsheet, just like control properties do.

Implications for OnStart

Last year, we introduced the App.StartScreen property as a declarative alternative to using Navigate in App.OnStart. It has been very successful, today App.StartScreen is used much more than the old pattern. At the time, I explained that there are three main reasons for using OnStart:

• Indiciating which screen should be first.

• Setting up global variables.

• Prefetching and caching data.

With the named formulas, we are now addressing the second item on this list. The third item is still being worked on and the subject of a future discussion.

People love to use Set in their OnStart. A recent study showed that when App.Onstart is used, 84% of the properties includes a Set. I get it. I personally use it. I championed the addition of Set years ago. Until now, this has been the only way to setup a value to be reused across your app. For example, I’ve written a chess app that heavily uses App.OnStart:

Many of the Set calls are to setup simple constants. The Board is represented as a string with metadata at the end, an unpacked form of chess FEN notation.

Set( BoardSize, 70);
Set( BoardLight, RGBA(240,217,181, 1) );
Set( BoardDark, RGBA(181,136,99, 1) );
Set( BoardSelect, RGBA(34,177,76,1) );
Set( BoardRowWidth, 10 );                      // expected 8 plus two guard characters for regular expressions
Set( BoardMetadata, 8 * BoardRowWidth + 1 );   // which player is next, have pieces moved for castling rules, etc
Set( BoardBlank, "----------------------------------------------------------------_00000000000000" );
Set( BoardClassic, "RNBQKBNR__PPPPPPPP------------------------_--------__pppppppp__rnbqkbnr__0000000000" );

This is easy to translate to named formulas:

BoardSize = 70;
BoardLight = RGBA(240,217,181, 1);
BoardDark = RGBA(181,136,99, 1);
BoardSelect = RGBA(34,177,76,1);
BoardRowWidth = 10;                      // expected 8 plus two guard characters for regular expressions
BoardMetadata = 8 * BoardRowWidth + 1;   // which player is next, have pieces moved for castling rules, etc
BoardBlank = "----------------------------------------------------------------_00000000000000";
BoardClassic = "RNBQKBNR__PPPPPPPP------------------------_--------__pppppppp__rnbqkbnr__0000000000";

Note that none of the references to these properties needs to change. You can cut and paste from App.OnStart to App.Formulas, modify the syntax appropriately, and that’s it. Wherever BoardClassic was being used, it can continue to be used as it was before. Also note that the order of these definitions is no longer important, so the definitions of BoardRowWidth and BoardMetadata can now appear in any order.

Let’s look at a more advanced case. In App.OnStart I have this imperative logic:

If( !IsBlank( Param( "TestPlay" ) ),
    Set( PlayerId, Lower(Param( "TestPlay" ) )); Set( AdminMode, true ),
    Set( PlayerId, Lower(Left( User().Email, Find( "@", User().Email )-1 )) )
);
If( !IsBlank( Param( "ReviewGo" ) ),
    Set( ReviewGo, true )
);

Instead of setting these variables, the formulas for how to calculate them can be specified in App.Formulas. This result is easier to read by decoupling the definitions of PlayerId, AdminMode, and ReviewGo:

PlayerId = If( !IsBlank( Param( "TestPlay" ) ),
               Lower( Param( "TestPlay" ) ),
               Lower( Left( User().Email, Find( "@", User().Email )-1 ) )
           );
AdminMode = !IsBlank( Param( "TestPlay" ) );
ReviewGo = !IsBlank( Param( "ReviewGo" ) );

Finally, no I didn’t implement a chess playing algorithm directly in Power Fx (yet), I instead am using the excellent open-source Stockfish chess engine running on an Azure VM. A custom connector is used to communicate with the VM which also manages the games between the players. In App.OnStart:

Set( Players, ChessPHP.Players() );
Set( PlayerName, If( IsBlank( LookUp( Players.players, Lower(player) = Lower(PlayerId) ) ),
                     playerid,
                     LookUp( Players.players, Lower(player) = Lower(playerid) ).name & " (" & playerid & ")"
                 )
);

In App.Formulas:

Players = ChessPHP.Players();
PlayerName = If( IsBlank( LookUp( Players.players, Lower(player) = Lower(PlayerId) ) ),
                 PlayerId,
                 LookUp( Players.players, Lower(player) = Lower(PlayerId) ).name & " (" & PlayerId & ")"
             );

Very similar. But what’s great about this is that, until I actually show PlayerName somewhere, the web service API call for ChessPHP.Players() doesn’t need to happen. The app doesn’t take precious time up front during app load to get this information. Because of the formula, the app knows how to get the needed information when the time is right.

I’m still converting my app to fully take advantage of named formulas. Ideally, I will get to a point where I can point to each piece of state and explain why it needs to be mutable and why it can’t be a formula. I expect that to be a handful of items, rather than the hundred or so state variables my app uses today. Named formulas definitely changes how you think about state and code reuse in your app.

To reiterate, I’m not anti-state or anti-Set. Mutable state is essential and differentiates Power Fx from Excel. Set is here to stay. But I do believe it should be used more sparingly than it is today. When the Set hammer was our only tool, everything looked like a nail. We now have a new tool to explore and only real world experience will inform us on the best ways to use them both.

Experimental for feedback

And now it is your turn.

Named formulas are an experimental feature in version 3.22091. The App.Formulas property will only appear if the Named formulas experimental feature is enabled in Settings > Upcoming features > Experimental. As an experimental feature, do not use named formulas in production apps.

How will you use this new feature? What could be improved before it is enabled by default? As always, your feedback is very valuable to us. Please let us know what you think in the Power Apps experimental features community forum.

What’s next

If you follow Excel, you will know that they added the Lambda function a few years ago. A colossal step forward for Excel, it enables user defined functions to be written directly in the formula language. And they used the named formulas concept to do it. For example, here are a set of functions that recursively walk a tree of employees and their reports in Excel, using the Advanced Formula Environment add in for authoring:

This is essentially named formulas with parameters, which become user defined functions. Or, alternatively, you can think of named formulas as user defined functions that have no parameters.

We plan to do something similar. In fact, there is an early prototype version of it running in our Power Fx open source GitHub repo at https://github.com/microsoft/power-fx. They logic looks different because we prefer to have the parameters on the left hand side of the =, we are strongly typed which we have added similar to how TypeScript added types to JavaScript, and we have lambdas built in to many of our functions like Sum. We are experimenting, this all may change. But the structure of how this works is consistent with what Excel is doing:

How does this relate to canvas components? Canvas components can provide pure functions today with experimental enhanced component properties feature. We are working to move this out of experimental, after having made canvas components generally available earlier this summer. Canvas components are great and support sharing across apps through a component library. However, they were designed primarily for a different use case, as a user defined control. Functions written in this way must be instanced as an object, appear as methods on that object, need to be placed on a UI canvas, and they are heavy weight to define using builder UI in Studio. Named formulas, and a future user defined functions, are much lighter weight, easier to create, and can be more easily copied from other apps, documentation, or web examples. They can also be leveraged across all Power Fx hosts with no canvas required.

We have no firm dates on when this will become available in Power Apps but we know code reuse is very much on your minds, and so it on our minds too.

The post Power Fx: Introducing Named Formulas appeared first on Microsoft Power Platform Blog.