Writing Power Fx formulas with natural language
With the announcement at this year’s Build conference, you may have already heard about Power Apps Ideas, an AI-powered assistance to help anyone create apps using natural language. Now we’re excited to tell you that this feature is now in public preview.
Overview
We know to make an app powerful, most time you have to deal with Power Fx formulas so you could bind objects together, e.g. saving forms, search, updating UI based on button clicks or text typed in a text control, etc. And it doesn’t require you to become a pro-developer to start writing a Power Fx formulas. However, certain amount of time is still requested when you’re trying to write a formula, e.g. finding which function to use and what is the right syntax, tuning and refining.
In the example, which is to enable user to use the search box to search customers by name and sort results, experienced makers can easily write the following formula.
That means they need to at least understand
- The Search() function, what can it do and the syntax of it
- The Sort() function, what can it do and the syntax of it.
- How to use Text input value in Power Fx
- Knows the logical name for Full Name in Dataverse.
Today, with Power Apps Ideas, one can just type in their thoughts in plain English “search Contacts with ‘Full Name’ in TextSearchBox1 and sort results by ‘Created On’ latest on top”. No need to worry about which function to use, no need to learn the syntax of the Search() and Sort() function, no need to learn how to use a Text Input control in formula, no need to go back to Dataverse table and check for logical name, and the same formula will be generated for use.
Transforming natural language to Power Fx formula using GPT-3
Trained with 175 billion parameters, GPT-3 is an advanced natural language AI model that implements deep learning to be able to both understand and produce human-like text based on a prompt in natural language. Microsoft has a strategic collaboration with OpenAI, the developers of GPT-3, to be able to apply the model in products like Power Apps.
By fine-tuning GPT-3 to understand how Power Fx formulas are constructed, we can leverage the model’s existing strengths in natural language input to give Power Apps makers the ability to describe logic just like they would to a friend or co-worker, and end up with the right formula for their app.
Now let’s take a deeper dive and explore how the above example works. After you finish typing and click Get ideas, your input will be sent to the model. Based on your input and context from your app, like which tables you have, controls on the screen, the fine-tuned model can generate a list of most relevant Power Fx formulas that may address your business needs. PowerApps will also do a post-check to filter out results that cannot pass formula syntax check. In addition, to de-mystify the formula and explain what it means, we also generate a natural language description for each formula suggestion to make it easier for both no-code and pro-dev makers to pick from.
Get started with Power Apps Ideas
If you have an environment created in the “United States” region, you will find a new Ideas tab on the right pane after you select on a gallery or data table. Click on that and follow instructions to type in your thoughts. For more details about how to use this feature, please check this document.
The feature is only available in US region and “en-us” language due to the limitation of GPT-3. We will investigate how to expand it to more regions and languages and keep you posted.
Limitations
To begin with, the preview capability is optimized when you are working with Search(), Sort(), SortByColumns(), Filter(), FirstN(), LastN() formulas on Gallery and Data table controls. It also includes some basic functions for conditions like StartsWith(), EndsWith(), DateDiff(), DateAdd(), etc. The simpler and clearer your requests, the better the generated outputs will be. Take a look at the default queries on Ideas pane to see examples of what is supported now. Follow best practices and use intellisense to complete your typing. For example, if you have a table called “Accounts” and you want to find records created before last week. You can type “find ‘Accounts’ whose ‘Created On’ is before last week”.
For preview, this feature will initially be available in the United States region and support English only. Check this document for full list of the limitations.
- Data sources: only supports Dataverse now, we will soon expand to support other data sources
- Data type: Text, Whole Number, Date and Time, Date Only, Decimal Number.
- Relationships: Single table only. We are also working on supporting lookup fields.
Next steps
We understand this is a very limited scope and we do plan to expand the capabilities to support more functions, controls, data types, regions and languages. We look forward to any feedback you have on this feature.
Try it out and let us know your feedback and ideas in the Power Apps community forum.