Feedback / Example of PowerFx usage to generate computed columns

[Felx-B]

Hi !

Here at Divalto we build a platform to provide products such as CRM and ERP.

We aim to let user extend our products through advanced settings, and low code capabilities. So I started to explore PowerFx to evaluate how it can help us.

Now I have a functional scenario I want to share, to illustrate PowerFx capabilities and to give some feedback on the integration.

Scenario / Gif

In this example we let user create computed fields, and display them on among other data in the grid

• Field created : Duration
• Formula used : DateDiff(ThisItem.'Start date', ThisItem.'End date', TimeUnit.Minutes)/60 & " h"

How it is made

Stack :

• Angular 13 for client
  • Used Felx-B/ng-power-fx-formulabar for the formula bar
• .Net 6 for server

In this view, user choose which column to display, so we build dynamicly the SQL query to populate grid columns.
With computed columns, user can choose to build

1. parse powerfx formula to extract used field
   • with regex for now, until a proper parser is exposed
   • regex : ThisItem\.(?<field>\'.[^']+\'|\w+);
2. un-translate fields to retrieve column names
3. build query

Conclusion / Thoughs

It is very easy to integrate PowerFx in existing solution, to boost some features with low code !

Translated columns

I think we need a stronger model to store formulas.
For now we use translated column names, to be user friendly, be this come with some issues :

• If translation change, all formulas will be broken
• If user wants to change display language, formula used will be broken
• If 2 columns have same translation, formula execution will try to guess the right column to use

Maybe we need to store raw formula instead of user formula

ThisItem.'My Field Translated' => ThisItem.myField

SQL Limitations

For now, computed columns are populated server side, after sql query, so user won't be able to sort / filter on these columns, due to pagination.

Maybe with a "PowerFx => SQL Translator" we would be able to make them sortable/filterable

Depending on the level of complexity of the formula, we would be able to translate them and enable these kind of features to user.

Circular references limitations

For now, only direct columns of the main table are exposed to user, this limits user possibilities. In further work I should add foreign tables but I didn't manage to get it worked. It seems PowerFx can't handle circular references

ex : ThisItem.Parent.Children

This kind of scenario force me to duplicate entire table definition for each references




That's all for the feedback.

Thank you for this amazing library and thank you for reading 🙂

[MikeStall]

This is great! The scenario makes a lot of sense and the screen recording is great.

A few comments...

[1] "parse powerfx formula to extract used field" -
We definitely want to avoid regex parsing - I see this issue came up here:
#198

We do expose top-level identifiers, but I see from the other issue that you need one level deeper. We'll need to add support for that.

[2] We're doing more display name support to aide in localization. The idea is that you'd store in an invariant format, and then the formula bar control would translate to displaynames on-demand.

[3] We would like to release a PowerFx-2-sql translator. Stay tuned...

[Felx-B]

Hi !
Unfortunatly I didn’t had much time lately to explore more use cases 😕

But I managed to implement an invariant version of formula storage, and I have some feedbacks about this :

I used GetInvariantExpression and GetDisplayExpression to acheive this,
This leads to this kind of transformations

DateDiff(ThisItem.'Start Date', ThisItem.'End Date') ⇒ DateDiff(ThisItem.startDate, ThisItem.endDate)
DateDiff(ThisItem.'Date de début'; ThisItem.'Date de fin') ⇒ DateDiff(ThisItem.startDate; ThisItem.endDate)

This is great for "translation-proof" storage

But in french, the separator is ; (because of the decimal separator)

And the GetInvariantExpression do not handle this transformation.
We would need a way to store a formula without locale specificity

Use case for this feature :

• We want to provide a set of invariant formulas with our product, and let user choose and manipulate some.
• We want to let power user to set formula for a set of users (possibly with different locale)

Other Remarks

• Is there any way to remove items from the suggestion list provided ?
  We do not need (yet?) Notification methods, Errors or Display styles
  This would be easier for user to understand the list of valid methods without unsupported methods

• It seems enums values are available without prefix, which could disturb user
  
  

• Is there any way to create our own enums ?
  Use case : We have our own color palette, and would like to let user choose

Next explorations

• Try out the parser 🙂
• Implement new feature to let user colorize cell/lines with custom rules
  • ex : make line red if resource is not available (Availability can be defined by dates and stock values)
  • ex : make cell red if value is negative (in financial tables)

I'll be glad to chat by email for further collaboration, I'll contact you soon 🙂
Thank you

[MikeStall]

Yes, please do email me. These are great comments:

1. GetInvariantExpression should be handling the tokens as well. Filed GetInvariantExpression should also translate tokens.  #359 for this.
2. We do now have support for both removing enums Although some of the surface here is still internal. We'll get a sample for this.
3. We have support for adding your own enums: https://github.com/microsoft/Power-Fx/blob/main/src/tests/Microsoft.PowerFx.Interpreter.Tests/DisplayNameOptionSetTests.cs

[Felx-B]

Hi,

I had some time to test my use case with cell colorization through PowerFx, and I have some feedbacks.

Intellisense

Latest release is neat ! Intellisense is much clearer now
But enums seems to still be accessible without prefix, I assume #375 should resolve this

OptionSets

I tried to add our own color palette with OptionSet and it seems not working
When using OptionSets in simple formulas it returns error Runtime type mismatch
Here is a simple repro scenario

CultureInfo.CurrentCulture = new CultureInfo("en-US");

var config = new PowerFxConfig();
var displayNames = DisplayNameUtility.MakeUnique(new Dictionary<string, string>
{
    { "option_1", "Option1" },
    { "option_2", "Option2" }
});

var option = new OptionSet("OptionSet", displayNames);

config.AddOptionSet(option);

var engine = new RecalcEngine(config);

string expression = "If(true, OptionSet.Option1)";
var value = engine.Eval(expression);

weirdly engine.Eval("OptionSet.Option1") works fine.
Which leads me to my second point:

I encountered a behavior I was not expecting.
OptionSetValue.ToObject() returns the DisplayName, I think it should return the underlying value

Similarly if I check another language like TypeScript :

enum TestEnum {
    Value1 = "v1",
    Value2 = "v2",
}
  
console.log(TestEnum.Value1);
// [LOG]: "v1"

I understand this point is arguable, but I think the real information we want when returning an enum value, is the value, not the display name (which can depend on user language).

Félix

[MikeStall]

For OptionSet.ToObject(), there are explicit Option and DisplayName properties (

Power-Fx/src/libraries/Microsoft.PowerFx.Core/Public/Values/OptionSetValue.cs

Line 19 in 749ba83

public string Option { get; private set; }

) to give you logical and display names. Can you cast to OptionSet and call the one you need?

One challenge is that in Dataverse, the logical name for an option set is just a number, so it's much more convenient to see display names in that case.

I'll check on the error issues...

[MikeStall]

Re error for "If(true, OptionSet.Option1)", see fix in #379 .