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.

This new function dramatically simplifies working with JSON in Canvas apps. For example, we recently worked with a customer who needed to extract information from a relatively simple JSON string stored in a Dataverse table:

[   
    {
        "PredictionID": "e438ec93-dee2-4a6c-92d1-8c9e87a1b1d3",
        "CustomerID": "2c9c3dae-5113-4b6b-9cc5-ad25b955b463",
        "id": "b8cf0ea6-b6f5-46c3-9fc2-5403fb7fdd2d",
        "Score": 0.7848321,
        "ProductID": "628065",
        "ProductName": "Large Widget"
    },
    {
        "PredictionID": "5ac7b6aa-d069-4c2d-b593-bda5bf3e2f56",
        "CustomerID": "2c9c3dae-5113-4b6b-9cc5-ad25b955b463"
        "id": "b8cf0ea6-b6f5-46c3-9fc2-5403fb7fdd2d",
        "Score": 0.82974886,
        "ProductID": "527174",
        "ProductName": "Small Widget"
    },
    ...

Their first solution was to use a Microsoft Power Automate flow to do the parsing which has excellent JSON support. However, the volume and latency of these calls didn’t work for their scenario. So, like many of you, we turned to regular expression matching using this formula:

MatchAll( ProductRecommendationsJSON,
          """Score""\s*:\s*(?<Score>[^,]*)\s*,\s*" &
          """ProductID""\s*:\s*""(?<ProductID>[^""]*)""\s*,\s*" &
          """ProductName""\s*:\s*""(?<ProductName>[^""]*)""", MatchOptions.Multiline ) 

This works and is what the customer shipped their app with. However, it is hard to write and verify with that complex regular expression syntax and is fragile, for example it will break if the order of the JSON fields is changed or another field inserted.

A better answer is a dedicated JSON parser, a highly requested feature on our community ideas forum. With the new ParseJSON function, this can be written as:

ForAll( Table( ParseJSON( ProductRecommendationsJSON ) ),
        { Score: Value( Value.Score ),
          ProductID: Text( Value.ProductID ),
          ProductName: Text( Value.ProductName ) } )

This is both easier to read and more robust, tolerating reordered and additional fields. We have plans to further simplify this with better untyped object array support and casting as a whole via a schema rather than field by field. But this will get you started and will continue to be supported with those enhancements.

Note that this is an experimental feature. This functionality will change. We are releasing it now for your feedback and for use in proof-of-concept, non-production situations. To use this functionality, you must opt-in to the feature under Settings > Upcoming features > Experimental. Please post your feedback in our experimental features forum.

To learn more, keep reading, and check out our Working with JSON documentation.

JSON and Power Apps

The JSON format has seen widespread adoption in serializing application objects for systems communications and saving of state. Many services today offer RESTful services that communicate via JSON payloads, including services such as SharePoint and Azure DevOps. Power Apps provides a large amount of out of the box connectors, many of which talk to services via JSON and provide Power Fx types as input and output.

But there are cases where either a service can provide very dynamic JSON payloads, or the data is provided as text but in practice contains JSON objects.

In 2020 we released an experimental feature called Dynamic Schema which addresses specific scenarios such as custom fields on Azure DevOps work items. The standard connector for Azure DevOps can only know about standard Azure DevOps fields, but a “capture schema” feature allows a maker to have Power Apps capture the output of the connector call and adapt the schema based on what the output provides. A maker can subsequently work with the connector in Power Fx as if the fields were always part of the connector’s schema. This is a fairly static “update” to the schema that can be made when authoring the app.

Today’s experimental release of ParseJSON addresses the other end of the spectrum, and provides an important base in the Power Fx language and runtime to bridge the areas in between.

Untyped Object

To handle the most dynamic scenarios with a JSON string, we need to address the fact that Power Fx is a strongly-typed language. The JSON format, in comparison, offers very few data types. Additionally, in a scenario where the JSON payload can change when the Power App is run (as opposed to when it is built) we must have the ability to read the JSON and convert into the types we need from the formulas that need them.

To support this most dynamic of scenarios, we have introduced a new type called untyped object. It is, in effect, a wrapper for a value or object that can be converted to a concrete Power Fx type at the time the app runs. When authoring the app, makers can use the untyped object to write formulas that make assumptions about the nature of the actual object or value when a user will use the app. This does mean the maker has some knowledge about the incoming data and its structure, as this metadata is not available during authoring and the untyped object cannot provide any help (such as IntelliSense). Untyped objects can contain values (numbers, text, Boolean, dates, etc.), records and tables. To use any underlying values of untyped objects, they have to be cast explicitly to their respective Power Fx types using the type construction functions such as Boolean (a new function), Text, Value, DateTimeValue, etc.

ParseJSON

The ParseJSON function will be the first (and for now the only) function to return an untyped object. It accepts a text value that is a valid JSON string.

For example, assume a custom connector “myConnector” that provides a GetJSONString() function:

ParseJSON( myConnector.GetJSONString() )

Now assume the connector returns the following JSON:

{
    "TextField" : "Hello, World!",
    "Version" : 1.1
}

We can store the untyped object that ParseJSON returns in a variable called “untypedVariable”, and access the individual fields with the regular dot notation:

Set( untypedVariable, ParseJSON( myConnector.GetJSONString() ) )
untypedVariable.TextField
untypedVariable.Version

However, the fields on the untypedVariable (TextField, Version) are also untyped objects and have to be explicitly converted to a type that can be used in formulas for Power Apps properties. For example, to use the TextField in the text property of a label control, the Text() function has to be used to convert the untyped object value to a text value:

Text( untypedVariable.TextField )

Similarly, a JSON array of values or records can be converted to a table directly with the Table function. That will result in a single-column table of untyped objects, requiring each untyped object value or record field in the table to be converted to a proper type.

For example, we can parse the following JSON string into an untyped object variable named “untypedVariable”.

[
    { "IndexField" : 1, "Title" : "One" },
    { "IndexField" : 2, "Title" : "Two" }
]

We can now convert this JSON array of records into a table with the Table function:

Table( untypedVariable )

If you wish to use this table in, for example, a collection and gallery, you have two options. Either use the table as-is, and in the gallery convert any property values to specific Power Fx types. Or, convert the table to a typed table with ForAll prior to assigning to the gallery.

To create the collection of untyped objects, you can use the following formula:

ClearCollect( collection, Table( untypedVariable ) )

Inside the gallery using this collection, each field will need to be converted into the correct Power Fx type:

Text( ThisItem.Title )

To convert the untyped object array directly into a typed Power Fx table, you can use ForAll which can return a table:

ClearCollect(
    collection,
    ForAll(
        Table(untypedVariable),
        {
            IndexField: Value(Value.IndexField),
            Title: Text(Value.Title)
        }
    )
)

Now the gallery can use the collection with an entirely typed record.

For more information and examples, see the Working with JSON article on the docs site.

What’s Next

The ParseJSON function with untyped objects provides a very generic way to handle dynamic incoming JSON through Power Fx formulas. We are considering next steps to bridge the scenarios in between dynamic schema capture during authoring, and untyped objects in formulas. Imagine declaring your own schema through Power Fx formulas or YAML definitions, and having connectors or ParseJSON automatically convert the incoming payload to the Power Fx typed schema you defined. This would provide a broad spectrum of options from capture schema from a sample payload, to defining schemas that can automatically convert, all the way to code-first handling with untyped objects.

While we work out what, how, and when we can introduce this spectrum of features, we plan to move the current ParseJSON and untyped object features to preview and GA quickly as we believe the flexibility it provides will allow makers to address their JSON parsing needs.

Additionally, untyped objects provide a basis for other potential features and functions that deal with external data structures, such as CSV, XML, etc.

As always, we value your feedback and suggestions on the currently released features and future roadmap. Join the discussion in our experimental features forum.

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