Having a hard time with Power Apps formulas? Use Power Apps Ideas to transform your natural language/ SQL based queries to Power Apps formulas.

The introduction of Power Apps have encouraged professionals from various sectors to jump into the world of application development. Someone who were expert in business processes but no past experience in programming, are now finding feet in the world of application development.

While this is encouraging, it can be a steep learning curve for citizen developers. And specially when we use Galleries/ tables in our application, the formula to filter a gallery or a table can become really complex.

Let’s take an example. Below is the formula to filter and sort a gallery.

SortByColumns(Filter(Accounts, 'Created On' > DateValue("2022-01-01")), "createdon", Descending, "accountnumber", Ascending)

What does this formula do? A simple formula which first filter all accounts where the account is created later than 1st January, 2022. Then order the filtered set based on ‘Created On’ descending followed by ‘Account Number’.

For a developer like me, it’s quite easy to term the formula as simple. For a citizen developer? Not that easy.

Is there a way Power Apps can suggest them the formula? What if we could express our desired outcome in natural language or SQL query syntax? What if Power Apps construct the formula for that?

With the Power Platform 2022 Release Wave 1, we do have the feature now. The feature is Ideas and is available for Table and Gallery controls.

Using the Ideas feature, a maker can specify their requirement in natural language or in a language similar to SQL. Power Apps framework will try it’s best to convert it to a PowerFx formula.

Let’s see the ideas tab in action.

We will now try to frame the same query in natural language. Let’s do it. Below is my query in natural language

Get 'Accounts' where 'Created On' is greater than "1st January 2022" order by 'Created On' desc, 'Account Number'

And here is the video in action.

As you can see in the video above, the ideas section have intellisense using which you can select the field names of the datasource. After that, once you press the Get Ideas button, the suggested formula shall show in the Answer area.

Use the apply button to apply the formula. The formula is an interpretation of your query in natural language. Hence you might need to do a few tweaks to achieve your desired result. Nonetheless, it is a very good starting point.

How about some SQL syntax? Let’s see if Ideas can resolve that too.

Below is the result of my experiment. As you can see, Power Apps Ideas have transformed this pretty well.

That’s about query formation. How about formatting individual items in the gallery? Ideas help you there as well.

I am displaying the Zip Code of an account as one of the values in Gallery Item. I want to display it in a different format. I select the ZIPCode field and then enter the desired output I need.

Now when I click Get Ideas, I get the below formula as a suggestion.

And if I apply this formula, I get the desired output for all the rows.

Wonderful isn’t it? I am pretty sure this shall be extended to other controls in Power Apps.

Hope this helped! You will also like the below posts.

Debajit Dutta
Business Solutions MVP