With version 2508.3 the UDF preview switch has been removed and the new analysis engine switch (in the New section) has been updated to include UDFs. UDFs are dependent on the new analysis engine which is why we have combined them. This switch is enabled by default for new apps.

User defined function benefits

If you are new to UDFs, they make apps easier to create, understand, test, and maintain. They also help Studio load and save apps faster.

How do they do this?

Instead of writing logic into each individual control’s properties, often replicating similar logic across the app, extract common logic into a single UDF with parameters to control how it works. The UDF is easier to understand, test, and maintain in isolation from any one place it is used. Duplication of logic is reduced which avoids multiple versions from getting out of sync. Modularization of logic is a time-honored strategy for scaling up the complexity of programs and apps.

For example, a simple UDF may do a calculation based on parameters, as in this example of a Fahrenheit to Celsius converter, placed in App.Formulas of a Canvas app:

FtoC( f: Number ) : Number = (f - 32) / 1.8;

We can use the label control to see the result of automatically evaluating this function with a constant input:

We can also add a slider to drive the input to the function and see that result. Our function hasn’t changed and now it is being used in two different ways. Not only that, as the slider changes, the function’s result is recalculated automatically, just as built in function are:

User defined functions can also perform actions. In this example, we have a function to add an entry to a collection and display a notification, wrapped in curly braces as it has side effects:

AddSale( customer: Text, amount: Number ) : Void = {
  Collect( Sales, { Customer: customer, Amount: amount } );
  Notify($"Sale to {customer} of {Text(amount, "$ #,###.00")}"); };

We call this function from a Button control OnSelect event, bind a DataTable control to the collection, and witness the results of the actions taken by the function:

More details…

For more information see User defined functions in the docs.

User defined types (UDTs), which enable passing record and tables in and out of UDFs, are still a work in progress that we plan to bring to GA shortly.

The post Power Apps’ User defined functions GA appeared first on Microsoft Power Platform Blog.

Another popular way to reuse formulas, User defined functions (UDF) have entered preview! We are now in the last stage of shipping this popular feature and hope to GA in the next couple of months. If you have not used this feature yet, now would be a great time to give it a try and let us know what you think in the community experimental features forum.

Last but not least, User Defined Types (UDT) will be reaching Preview soon too. We are planning for their GA shortly after UDFs.

UntypedObject becomes Dynamic

Until the introduction of UDFs and UDTs, the names of data types wasn’t used in Power Fx syntax. So, until now we haven’t worried that much about the data type names. During the development of the ParseJSON function we needed a new data type, the name UntypedObject was proposed, and it stuck.

Since then, UntypedObjects have become very popular. But unfortunately, it isn’t the best name. First, it is a long compound word and not very friendly. It doesn’t always represent an Object, it can be a single number. It isn’t actually Untyped, as we know the types of the elements we parsed from JSON (number, text, Boolean, array, etc). Power Platform connectors use the term Dynamic, requiring a mapping in our documentation from Untyped to Dynamic. Also, C# uses Dynamic as the name of a dynamic data type in a similar way.

If we were going to make a change, now would be the time. And so we have. We have renamed UntypedObject to Dynamic. Not to worry, there is no change in semantics, this is a name change only. If you aren’t using UDFs or UDTs, there is no change at all. The only folks who will notice a change, and need to update their code, are those who were passing UntypedObjects as UDF parameters, using it as a UDF return type, or using it in a UDT.

The documentation is in the process of being updated. The list of available data types for parameters, not including those you may define with UDTs, is now:

Void as UDF parameter

One last change, which shouldn’t have a meaningful impact, is that Void is no longer supported as a UDF parameter data type. This was never useful, as soon as a formula touched a Void data type it would result in a runtime error. Now it produces a compile time error which are always preferred.

Onward to more GA

Our next post on UDFs will be that they have GA’d and can now be used in production. We hope to be there soon. If you run into any issues or have suggestions, please let us know on the community experimental features forum.

The post Enhanced Component Properties, User Defined Functions, and UntypedObjects on the move appeared first on Microsoft Power Platform Blog.

• 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.

With version 3.24063 we are making changes to how we do this in Canvas apps. We will now have four feature stages:

• New: These are new features that are generally available (GA), fully supported, and documented. These will generally be on by default for new apps, but may take time to deploy everywhere. Enable for existing apps on your own schedule.
• Preview: These features are almost done and will be New soon. But at this stage there still may be some breaking changes made. This is the last opportunity for feedback. These will generally be off by default and will be documented, although some exceptions will be made especially for fast moving AI related features. These features are not GA, should not be used in production, and are covered by our preview terms of service.
• Experimental: An early stage “Preview,” these truly are experiments and may never reach GA. We are assessing the value proposition and design of the feature, and the feature may radically change or be removed completely at any time. These features will be off by default and will generally not be documented. These features are not GA, should not be used in production, and are covered by our preview terms of service.
• Retired: These are GA features but are on their way to being removed from the product. They are still fully supported and document, but we either have a new, better way to do the same thing or usage is low. Disable for existing apps on your own schedule. Generally, these features are off by default.

Why did we add a fourth New category? We needed a new category that clearly indicates that features are GA and the long-term direction of the product, and yet still needed the flexibility of having a switch to control the feature. The switch allows us to slowly deploy the feature and watch for problems, and at the same time gives you control to enable the feature for your production workloads on your own schedule especially when a breaking change is involved.

GA of features moved to New

Practically what this means is that we won’t have features sit in Preview for as long as we have in the past. With this release, we are also announcing the GA of these features that have moved to New:

• Modern controls and themes (this feature set was already GA under General settings but the switch is more appropriate here)
• New analysis engine
• Expanded media support for SaveData on Power Apps mobile
• SQL Server stored procedures

GA of features with disable moved to Retired

The following features are also now officially GA and no longer have switches in Preview:

• Delayed load
• Formula-level error management
• Non-blocking OnStart rule
• Formula-level prefetching
• New data format in Microsoft Excel Online Business connector
• Keeping recently visited screens in memory
• SaveData, LoadData, and ClearData in the web player
• Simplified tab index
• Performance optimization for hidden controls

However, you won’t find switches for these in New. These features have been in the product and on by default for a while and no longer qualify as “new.” To control these features, we have added “Disable” versions of all of the above in Retired, as the old behavior without this feature is now retired and will eventually be removed from the product.

For example, “Delayed load” has been a Preview switch for ages and has now graduated to GA. Since this has been a feature for a long time, instead of New, we added “Disable delayed load” to Retired. Using the new behavior of “Delayed load”, without a switch being toggled, is the way most apps should operate. But until we fully remove the old behavior (without “Delayed load”), there is still a way to get it through the disabled “retired” switch.

These Retired behaviors will one day be removed from the product. The timelines and process for removal will vary based on the feature, usage, and alternatives available and will be well communicated ahead of time if it involves a breaking change.

Summary

A few other notes, the “Enable improved data table control selection and Value property” feature will remain in Preview but will be disabled by default. This long running feature has been superseded by the new modern control “Table (Preview)” and we recommend that new apps use this feature instead. This change does not impact existing use of the old table control; all we have changed is the default for new apps.

As that last example demonstrates, there are still “Preview” tags throughout the product, for which there is not a Settings switch. Despite the lack of a switch, these are still “Preview” features, are not GA, and should not be used in production apps.

Alot of change. But actually, if you didn’t open Settings, the only thing that has changed is that the old table control is now disabled by default for new apps. We have mostly just done some rearranging. No existing apps will change behavior.

It is our hope that these changes will clarify that Preview features are not for production as they will generally be off by default. We will graduate features up to New and GA faster so that they can be on by default for everyone, and old behaviors that should no longer be used but are still GA will be easily identified in Retired.

You can read more about the feature stages at Understand New, Preview, Experimental, and Retired features in Canvas apps. To recap:

• New: Good for all workloads. Generally turned on for new apps; turn on for existing apps as appropriate.
• Preview: Almost ready to ship. Good for feedback, but not for production. Turn off by default except for what is being tested.
• Experimental: An experiment. Good for early adopters and early feedback, but not for production. Keep all off, except for specific features that you are helping us refine.
• Retired: Good for all workloads. But there is probably a better way and the old behavior will eventually be removed. Generally turned off for new apps; turn off for existing apps when you can.

As always, we love your feedback, please leave comments in the community forum.

The post GA of many long time Preview features 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.

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.

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.

It’s a huge step. Adding error handling to an existing language turned out to be a very tall order, touching almost everything, from data types, to functions, to the runtime. Thank you for all of your support, feedback, and patience as we got this done.

What does it mean for you?

• Your apps will more reliably detect and report errors.
• You can write blank/null values to a database.
• You can detect and replace errors with the IsError, IsErrorOrBlank, and IfError functions.
• You can control error reporting and logging at a central location with App.OnError.
• You can create and throw your own custom errors with the Error function.

Error handling is a big change in behavior. By entering preview, we are signaling that we believe we are done, that we anticipate no further significant changes from here. Many of you already use error handling in production and this move to preview should only embolden more of you to do so. If significant changes are needed from here, we will treat them as a separate feature.

We are rolling this out slowly as it is such a big change. All of you will soon see that the Formula-level error handling switch has moved from experimental to preview in the settings (as of version 3.22082). It will still be default to off for most tenants. Over the coming weeks we will slowly change the default for new apps only to on across the tenants. Makers can still disable this feature and will be able to do so for a long time.

I say again: we are changing the default for new apps only. Existing apps will continue running as they always have. We have no plans at this time to turn this on for existing apps, and as this is such a big change, we may never do this and make this a permanently available switch. Your feedback will guide us.

The documentation for Error, IfError, IsError, IsErrorOrBlank functions and the App.OnError property covers these changes. IfError and IsError are very similar to their Excel counterparts. We are also working on overview docs that will be released shortly.

But before that, let’s take a brief tour.

Let’s start with what Excel does, the inspiration for Power Fx. For an error like division by zero, Excel is very clear that something has gone wrong with a # error message that shows right in the cell. This error will propagate to other cell formulas if A1 is used in a formula:

Today, without error handling, Power Apps won’t report anything in this scenario, instead treating the division by zero error as a blank value. That’s not good, as the maker and the end user of the app have no idea something may have gone wrong:

Errors happen. Unexpected data flows in, networks go down, storage fills up, to name just a few situations that an app may encounter in the real world. Makers don’t often think through all the ways that things can go sideways which makes default error handling even more important.

Returning a blank for an error is also a problem because blank is a legitimate value in our type system and in many databases. Without error handling, Power Apps won’t allow you to write a blank to a database instead thinking it is an error.

So, instead of returning an easy to ignore or misinterpret blank value, with error handling turned on we now report an error to the end user (the error banner) and show the formula as having an error to the maker (the red filled in circle on the control):

Further, if you look at the value of the formula, it is not a blank but an error value. Just as any formula can result in a blank, now any formula can also result in an error:

Now, we still aren’t showing an error in the label control itself as Excel does. We couldn’t do this generically because, unlike Excel, the error could be on a property of a control for which there is no way to display the error. For example, where should an error on a slider control? Where should an error be shown for an imperative operation in the middle of a button’s OnSelect formula? We settled on showing the end user banner and flagging the control in the design experience.

That’s not to say you can’t detect and display an error in that label control. Error handling provides a wealth of mechanisms to control how errors are handled and reported. For example in this case, we can wrap the division by zero with an IfError function to return a custom message in the label:

The Text function call is required for type compatibility. Or we can use IfError to throw a different, more specific error with the Error function:

Or we can have a catchall for all errors in the app with App.OnError. For example, we can log the error and present a different message to the end user:

If we look at the log, we see the details of the captured error from FirstError (and there is also an AllErrors), including where it happened and when it was detected:

The possibilities are endless! You now have all the tools you need to detect, replace, report, and log errors, including a good default behavior if you never take advantage of these tools. And, bonus, you can also now write blank (or null) values to databases.

Please let us know what you think in the Power Apps community forum. There is a dedicated and active space for error handling discussions at Error Handling – Power Platform Community (microsoft.com).

The post Power Fx: Error handling graduates to preview appeared first on Microsoft Power Platform Blog.

String interpolation

Note: There is a known issue with this feature when used in some apps that also use service functions through a connector.  On save of the app, $” will be replaced with Concatenate.  The problem has been fixed in 3.22051 which will begin deployment shortly.

Have you ever spliced together a long string with the & operator or Concatenate function?

"Welcome " & FirstName & " " & LastName & ", it is great to meet you!"

What if instead, you could embed those references to FirstName and LastName directly into the string?

$"Welcome {FirstName} {LastName}, it is great to meet you!"

The second form is a lot cleaner and easier to read.  Most modern programming languages have the ability to embed expressions within a string and now Power Fx does too.   Like C#, we call this feature string interpolation and it follows the same syntax as C#.  You can read all about the details in the docs.   You can of course continue to use Excel compatible standard strings and the concatenate operator and function; string interpolation only adds a new option.

The basic rules are:

• String interpolation strings begin with a $” instead of just  “.
• Curly braces delineate the embedded expression.
• To include a curly brace in the string, double it such as {{ or }}, just as we do to escape double quotes in a standard string.
• Expressions can include standard strings or string interpolated strings.

Here are a few more interesting examples:

$"2+3 = {2+3}" 
// result: 2+3 = 5

$"{{this is inside curly braces}}"
// result: {this is inside curly braces}

With( {x:5, y:7},
      $"Point ({x},{y}) is {Text( Sqrt( x^2+y^2 ), "#.###" )} from the origin" )
// result: Point (5,7) is 8.602 from the origin
// shows a standard string nested within string interpolation

With( {FirstName: "John", MiddleName: "Q.", LastName: "Doe"},
      $"{Trim( $"Welcome {FirstName} {MiddleName} {LastName}" )}, we're glad you are here!" )
// result: Welcome John Q. Doe, we're glad you are here!
// spacing is correct with 0, 1, 2, or 3 name parts that are non-blank
// shows nested string interpolation

Why add this now?  As we expand Power Fx to other products in the Power Platform, we have found that many of them use string interpolation by default.  For example, consider the below action in Power Virtual Agents.  The maker enters a string for the Message and can embed dynamic expressions within the string, in this case for the variables UserName and AgentName.  This is a form of string interpolation with the leading $” provided by the system.

Index function

Have you run across Last( FirstN( Table, N ) ) in your Power Fx travels?  Yes, that is a rather roundabout way to get to the N^th record of Table.

No longer!  Excel has an Index function for, among other things, returning a row out of a range and so we adopted the same function.  Index( Table, N ) is equivalent to the above.  You can read all about it in the docs.

Why add this now?   General goodness and because we are working on JSON parsing and we needed a more convenient way to index into a JSON array.  Oops, general JSON parsing, did I say that out loud?!  Stay tuned…

RandBetween function

And finally another Excel function, RandBetween( Low, High ) that returns an integer between Low and High inclusive.  Much easier to use than multiplying a range by Rand() and making sure you have the lower and upper bounds handled correctly.  Docs tell the tale.

Why add this now?  General goodness and I think the team was tired of me using this as my example of a pure function I could create with enhanced component properties.  Now I need to find another example.

Feedback welcome

As always, your feedback is most welcome here and in the Power Apps Community forum.  Your input is very helpful to us in prioritizing what we do next.

The post Power Fx: String interpolation, Index function, and RandBetween function appeared first on Microsoft Power Platform Blog.

We announced Power Fx at Ignite in March with two blog posts, one introducing Power Fx and another going into details of the language.   Both have been among the most popular of our posts this year.  The interest and excitement in this new language has far exceeded our expectations.

Now we are ready to share the source code.  This release is very much a  preview, with a major version number of 0.  We will be working hard, with your input, to solidify the language semantics and the hosting APIs.   Not all the functions are implemented yet, the formula bar is not yet available, and there will be many other parts of the puzzle that I’m sure you will ask about.  That’s fine, it’s a journey, and we very much look forward to taking this journey with you.

Our GitHub repo at https://github.com/microsoft/power-fx is a great place for your feedback and where we will be making future announcements.

Low code for everyone

Power Fx is the low code language for everyone.  It leverages the existing knowledge of hundreds of millions of users who already know how to express logic in Excel.  Natural language support with GPT-3 and no code builders that create Power Fx formulas can lower the bar even further.

Pro devs love Power Fx too because it can dramatically cut their development time.  It offers a declarative, strongly typed, and functional language base.   Incremental compilation, Intellisense, instant error reporting, and running while authoring provide a great authoring experience.  We embrace existing tools and workflows such as VS Code and Git, today announcing experimental co-authoring support for Power Apps Studio via GitHub, Azure Dev Ops, or any other Git provider.

And best of all, their is a smooth spectrum of support between these two camps, using Power Fx as the common language.  Fusion teams with a wide spectrum of skills and expertise can work together to very effectively create solutions.

Low code everywhere

Since March, we have delivered private previews of two integrations into the Power Platform.  The first was Model-driven app commanding, enabling makers to write Power Fx to implement command, simplifying the process and no JavaScript is required:

Second, we implemented Dataverse formula columns.  Use Power Fx to easily write concise and powerful formulas across the columns of a record:

And we are just getting started.   Integrations into Power Virtual Agents and Power Automate Desktop are under way and will arrive next year.

The value proposition is clear: if someone learns Power Fx in one product, they can leverage that knowledge in another product, and another.  It’s the same reason Power Fx was born in the first place, to leverage the existing knowledge of hundreds of millions of Excel users.

But why stop there.  Wouldn’t the leverage of knowledge be even more powerful if it was industry wide?  Successful programming languages today aren’t isolated.  They find uses in scenarios we can’t even imagine yet.  They need users and an active community to help them grow.   This is why we are making Power Fx open source.  Low code for everyone, everywhere.

Acumatica has been on this journey with us from the beginning.  Early on they saw the potential and started working with us to help define the functionality and hosting APIs, providing our team with critical feedback.  Ajoy Krishnamoorthy, Chief Strategy Officer & Executive Vice President at Acumatica, presented their Power Fx work at Acumatica’s Summit 2021.

Our goal is to enable Acumatica, and all of you, to empower your users with low code customizations based on the well known Excel formula language.  That is Power Fx.

Your turn!

Let’s take Power Fx out for a spin with a simple example: a Read-Eval-Print-Loop (REPL) console application where we can define formulas, work with variables, and evaluate expressions.

I’m going to use the freely available Visual Studio Community Edition for these steps to make it easy.  But you don’t have to, everything here is available on GitHub as C# sources and as NuGet packages.

1. If you don’t already have a copy installed of Visual Studio 2019 or later, install the Community Edition.
2. Start Visual Studio.  In the What would you like to do? dialog select Clone a Repository on the right hand side.
3. Enter https://github.com/microsoft/power-fx-host-samples and select Clone.
4. Select the ConsoleREPL solution in the Solution Explorer.
5. From the Build menu, select Build Solution.  This will download and install the required Power Fx NuGet packages.
6. From the Debug menu select Start Debugging.  A console window will pop up.
7. Power Fx is running!  Enter some Excel formulas and try it out.

Let’s take this step by step:

• To evaluate an expression, simply type it in.  For example, 2+2.  Internally within the host application’s code, a call to engine.Eval was made with the expressions string, all parsing was handled by Power Fx.
• Use the Set function to set a variable, just as it is done in Power Apps Canvas.  For example, Set( Radius, 10 ) sets the Radius variable to the number 10.  Internally, a call to engine.UpdateVariable was made.
• Use name = expression to define a formula.   In this case, Area = 3.14 * Radius * Radius, the well known formula for the area of a circle.    Internally, a call to engine.SetFormula was made.
• Since this formula has dependencies on other formulas and variables, it will be automatically recalculated when those dependencies change.  As we change Radius to 400 with Set( Radius, 400 ), which calls engine.UpdateVariable internally, we see the new value for Area automatically recalculated.
• We’ll add a formula for Circumference for good measure.  And then as Radius is again changed to 800, both Area and Circumference are automatically recalculated.  Just like the cell dependencies of a spreadsheet.
• Evaluate Help() for information on using the REPL.  The Help function is a host provided function and not part of the Power Fx language proper, a common pattern as hosts provide their own functionality.  It was registered with a call to engine.AddFunction.

You can view the full source code for the Console REPL host sample  at https://github.com/microsoft/power-fx-host-samples/blob/main/Samples/ConsoleREPL/ConsoleREPL.cs.

Circles are always fun, but let’s look at another example involving tables.  The same filtering, sorting, and aggregating facilities that are available in Power Apps Canvas are available here:

Going deeper

In this release, we are making available a C# implementation of Power Fx.  Talking to customers and our internal partners, running C# in a server environment was the most interesting first step.  Power Apps uses a JavaScript code generator and Dataverse formula columns uses a SQL code generator, which we may make those available in the future.  Independent of code generator, all of our integrations use the same compiler front end.

We have implemented around 60 functions from Power Apps Canvas and Excel.  We will be adding more in time.

We also have in the works a companion formula bar based on open source Monaco, the same engine powering VS Code, that we will also be providing as open source and as an npm package.  It is the formula bar that can be seen in the two animations above in this blog post for commanding and formula columns.  The formula bar is an integral part of the Power Fx experience, providing suggestions and immediate warning/error feedback while a maker is writing a formula.  This will be coming in the months ahead, it wasn’t ready to release at this time.

Where’s the source code?

There are two GitHub repos of interest for the source code:

• https://github.com/microsoft/power-fx is the core compiler, runtime, and documentation home.  There is the source for the NuGet packages.
• https://github.com/microsoft/power-fx-host-samples is the home for samples that make use of the core.  It’s a great place to see how a host interacts with the engine through the hosting API.

But you don’t necessarily need to clone and build the Core.  There are two NuGet packages that are used by the Console REPL: Microsoft.PowerFx.Core and Microsoft.PowerFx.Interpreter.   The sources in https://github.com/microsoft/power-fx are used to create these packages.

These NuGet packages are currently hosted from a temporary location.  We plan to move them to NuGet.org shortly.

What’s great is Microsoft’s own products are using the same open source offering and NuGet packages available to you.  This helps us ensure the quality of the open source offering and scale up the number of products using Power Fx both inside and outside of Microsoft.

Next steps

As said earlier, welcome to version 0.  It’s a preview, things will be changing.  It’s a starting point for iterating with all of you on the language and how it is hosted.   https://github.com/microsoft/power-fx will be where the action is.

The post Power Fx: Open source now available appeared first on Microsoft Power Platform Blog.