Formulas

8 min read

Power Fx: Introducing Named Formulas

By Greg Lindhorst

September 16, 2022

I am thrilled to introduce you to an old concept. A powerful concept that has been in Excel for a very long time, that we are now bringing to Power Fx. With it, you can simplify your app’s initialization, reduce app load time, reuse logic, and improve the maintainability of your apps.

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.

Greg Lindhorst

See more articles from this author