I’ve been using the Power Apps Ideas OpenAI GPT-3 natural language to Power Fx query generation capability for canvas apps since the private preview stage… and I must say I am impressed with the experience!
Disclaimer, I am a big fan of home automation and natural language processing and I use it on a daily basis with both Alexa and Google Assistant. My expectations were high.
In this post I’ll elaborate on my findings and describe the different gotchas when using this new feature. Note that when I first wrote this blog closer to the private preview stage, many of the queries did not work. I have since updated the post and added an (*) next to the queries that didn’t work back then but are fine now. This is a very good example on how machine learning evolves over time.
Setting the Expectations – Is it as Good as HAL or KITT?
The first thing to note is that the feature is in its infancy. As such it is still at the learning stage. Don’t expect a HAL or a KITT, as it’s not designed to know it all, but it is learning fast.
Learning How to Talk to it
One of my feedback points to the Product Team was that I had to learn yet another “language” or way of talking to get the results I am after. I felt like I was conversing with Yoda.
It wasn’t too bad. Through trial and error it didn’t take long to realise it was written by engineers like me. Given that I am familiar with the T-SQL query structure, my brain was wired to write requests the way I write SQL queries
Give top N of X where Y order by Z
However, when I asked my wife who’s trained as a lawyer to try to query information, most of her requests didn’t return the correct results, if any. For example, she would ask “give me accounts sorted in alphabetical order”. It was obvious to me that she is asking for accounts ordered by the account name (or primary field), but the AI did not pick that up.
Supported Functions
At the time of writing, the feature works with Canvas Apps on galleries and data tables and it supports Search(), Sort(), SortByColumn(), Filter(), FirstN(), LastN() and some sub-functionals like “contains”. But some of the common sub-functions (such as starts with, ends with etc) don’t work yet.
Queries are meant to be executed on the one table without joins (e.g. give me accounts where the primary contact emails address is x). Joins are not supported yet.
What Worked
Requests created following the right order with the right keywords covering the supported functions worked perfectly. For example:
give me bottom 10 accounts sorted by ‘Account Name’ in reverse alphabetical order
give me top 5 accounts sorted by ‘Created On’ date from newest to oldest
give me bottom 5 accounts where created ‘Modified On’ is before today
give me top 10 where ‘Account Name’ contains “(sample)” order them by ‘Modified On’
Even using formulas outside of the currently supported scope sometimes worked:
give me accounts where ‘Address 1: City’ contains “Red”
give me accounts where created ‘Modified On’ is before yesterday? (today)
Data Types
At the time of writing, the feature mainly supports text fields filtering, but I also had some success with simple date queries. For example:
give me accounts where created ‘Modified On’ is before today
Even if the date query is more complicated, the results were correct
give me accounts where ‘Modified On’ is before July (*)
Other data types that are currently not supported are options sets (including binary values and status) and lookups. I would love to be able to filter by active records, or where a status is X. A common ask for lookups is to filter based on owner being the current user, for example retrieving my records: give me accounts where Owner is me or get me my accounts.
Some Gotchas
Syntax
Ensure you add double quotes when filtering by string. Using single quotes or no quotes will return undesirable results:
give me accounts where ‘Address 1: City’ contains Red
Position your Filtering and Ordering
Make sure you get the order of your filtering and ordering correct. Filtering comes before your ordering:
give me accounts ordered by ‘Address 1: City’ and where ‘Address 1: City’ is “Santa Cruz” does not work.
However give me accounts where ‘Address 1: City’ is “Santa Cruz” and sort by ‘Address 1: ZIP/Postal Code’ works.
Combine Multiple Filtering or Ordering Request
Combining filters will work even if you don’t repeat the field name you are comparing to.
give me accounts where ‘Address 1: City’ is Redmond or Renton (*) will give you the same result as
give me accounts where ‘Address 1: City’ is Redmond or where ‘Address 1: City’ is Renton.
Also, when it comes to using intent keywords ensure you can mix and match keywords when combining ordering, for example:
sort accounts by ‘Address 1: City’ and order by ‘Address 1: ZIP/Postal Code’ (*) does work even though we are matching both sort and order keywords in the same sentence.
What I would like to see
The most obvious request would be to improve accuracy to generate results in line with intent. Something that will certainly improve with time given its AI learning capability.
Second, I would like to see the introduction of table joins. I always find it difficult to query across multiple tables. If we filter based on other related tables (lookups or 1:N N:N relationships) that would add so much value. For example, give me accounts with more than 10 phone calls over the last week. Or give me accounts where the primary contact’s email address is X.
Third is to accept more data types, especially option set (e.g. give me active accounts).
Ask number 4 is to make the natural language a bit more… well… natural, rather than prescriptive. E.g. Give me accounts created yesterday – as opposed to – Give me accounts where ‘created on’ is yesterday.
My bonus request was to improve the authoring experience. I loved the intellisense but it felt too eager to complete my sandwiches even if I didn’t want it to. Would even be better if we can replace typing with a voice command.
Documentation
Here is the original announcement
https://powerapps.microsoft.com/en-us/blog/introducing-power-apps-ideas-ai-powered-assistance-now-helps-anyone-create-apps-using-natural-language/
and some Microsoft offical documentation
https://devblogs.microsoft.com/premier-developer/getting-started-with-gpt-3-in-power-platform/
Feedback
Hope you enjoyed this please, please leave some feedback and share your opinion on the Power Apps Ideas.