Dataverse, Power Apps

Using AI suggestions for formula columns
Posted by Heidi Neuhauser

AI suggestions for formula columns is a powerful preview feature available to your Power App environment. Since it is in Preview, so it’s a great time to play around with it and get to know how it works. This is an environment-level setting, so if you’re using more than one environment, you will need to turn it on anywhere you are ready for it.

In the end, we will use natural language prompts to create this new column on the Opportunity table using Power fX:

New formula column using AI suggestions.

We create this column using AI suggestions (and a few modifications to get it right), resulting in this final fX formula:

If(
    ThisRecord.Status = opportunity_opportunity_statecode.Won || ThisRecord.Status = opportunity_opportunity_statecode.Lost,
    DateDiff(ThisRecord.'Actual Close Date', UTCNow()),
    Blank()
)

Turning it on

If you want to get started using this preview feature, head over to the Power Platform Admin Center. Once you’ve selected the preferred environment, go to the Settings, then select Features.

In the Power Platform Admin Center, go to Environment Settings > Features

Scroll down a bit until you see the heading AI suggestions for formula columns. Toggle “Allow users to get AI suggestions when creating formula columns” to ON.

Turn AI suggestions for formula columns on

Reminder: This is a preview feature.  You should turn it on in a Sandbox environment, but Preview items are not recommended for use in a Production environment.

Using AI Suggestions

Now, let’s head over to the Maker Portal to show this preview feature off! I’ve gone into a Solution file that has my Opportunity table included. I have added a new column and selected a data type of formula. Since I have toggled this feature on in the Power Platform Admin Center, I now have up and down arrows next to the “Type a formula” header:

Clicking that menu icon expands these two options:

  • Type a formula (the standard way to create a formula column)
  • Get formula suggestions (using AI)


After selecting the second option, you will now see this:

Now, you can enter your formula criteria using natural language queries and it will generated a formula you can use.

**Reminder: Always check what AI returns here. Say it with me friends “trust, but verify!”**

In this example, I’ve entered the following prompt: Days since Opportunity was Won or Lost.

It provided me with the following Suggested Formula:

If(ThisRecord.Status = opportunity_opportunity_statecode.Won || ThisRecord.Status = opportunity_opportunity_statecode.Lost, DateDiff(Now(), ThisRecord.'Won Date'), Blank())

It’s close but not completely correct. I’ve modified it in the following ways:

  • Switched the order on DateDiff to fit the proper syntax I need (otherwise, I would get negative values instead of what I am looking for: Today – Record Won Date).
  • Removed “Won Date” and replaced with the correct field “Actual Close Date.

The revised formula becomes:

If(
    ThisRecord.Status = opportunity_opportunity_statecode.Won || ThisRecord.Status = opportunity_opportunity_statecode.Lost,
    DateDiff(ThisRecord.'Actual Close Date', Now()),
    Blank()
)

)

Troubleshooting Errors using ChatGPT

This looked great… until I went to implement it. Then I got this error:

I wasn’t completely sure how to troubleshoot this one, but I am a big fan of using ChatGPT as part of my toolkit. So, I copied the above image, and pasted it into ChatGPT, which told me that this is my problem:

And gave me 3 options to fix it. Since my users all are in different time zones, I used the simple fix of using UTCNow() instead of Now(), which let me save my field in Dataverse.

The new, revised formula I entered into Dataverse is:

If(
    ThisRecord.Status = opportunity_opportunity_statecode.Won || ThisRecord.Status = opportunity_opportunity_statecode.Lost,
    DateDiff(ThisRecord.'Actual Close Date', UTCNow()),
    Blank()
)

The new field!

And with no further ado or additional troubleshooting, we were able to add the new formula column to a view for Closed Opportunities and voila!

Apologies on the terrible Sandbox data…. 😉

Closing Thoughts

Like all AI tools, this one is designed to save time and maximize efficiency. Like many things in preview, it’s still getting polished.  The key here, as with all things AI in the Power Platform stack, is to always verify results before using them in a system.  And I still continue my love of using ChatGPT to troubleshoot issues and errors.

Related Post

Leave A Comment