Best practices for working with data in Microsoft PowerApps

The diverse collection of data connectors in PowerApps is impressive. There are over 250 different connectors available, not only from the Microsoft ecosystem but across the entire internet. You can connect to Salesforce, Gmail, Zendesk, Azure and so much more. These connections are great, but I have found that data connectors bring one of the biggest hindrances to a PowerApp’s performance. This is not a necessary evil, we can optimize how we connect and collect the data while providing our users a better experience over long waiting load times.

Clearly Collect it first

When working with data, your first (and maybe only) best practice is to put your data into variables. This is easily done like:

ClearCollect(MyEvents, Events)

Pretty easy right? ClearCollect creates a new collection object, which I am calling MyEvents, and then puts the Events data source into that variable. Now, any control I want to use it with will use MyEvents as its data source.

We can get fancier here, and only pull out future events into my variable, like so. There are data threshold limits, keeping that in mind you may need to perform filters to get a subset of data. Again, put that into a collection variable, like:

ClearCollect(FutureEvents, SortByColumns(Filter(Events, DateValue(Text('Start Time', "[$-en-US]yyyymmdd"),"en-US") >= DateValue(Text(Today(), "[$-en-US]yyyymmdd"),"en-US")),"EventDate"))

 

There are some big benefits by collecting your data.

Using ClearCollect makes one call to the data source and then caches the data in your app, and then all of your controls read that cached data. Otherwise, any controls using the data will query the data source again, every time they’re loaded on the screen. This will slow things way down.

Adding a sorter or filter option? The users will query the cached data instead of triggering another data call to the data source. Yes, that will be much faster.

You normally put these ClearCollect in the OnVisible parameter of a screen. By doing so, your app will only call the data source when the screen loads. Given a PowerApp with a dozen screens and as many data connections, the initial load will be significantly lighter and faster.

Getting a refresh

If you find yourself using a collection variable and need to get a new copy of the data, you will have to refresh the data source and then collect it again, something like:

Refresh(Events)
ClearCollect(MyEvents, Events)

This could happen on the OnVisible of a screen, or add a refresh button and let the user refresh the data when they want to. All depends on what you need.

While the data is loading

Since you can now control when your data is loaded, you can also control your users’ experience by adding some loading screens [click here]. You can provide a cool rotating image or a status providing the user insights into what is taking so long. This method of informing your users something is happening is 100 times better than just letting PowerApps load the data while the user stares off into nothingness.

Do it all concurrently

As you’re collecting and processing your data, you can do multiple calls and functions concurrently, at the same time. There’s a neat little function called, you guess it, Concurrent.

Concurrent(ClearCollect(Table1,myTable1),ClearCollect(Table2,myTable2)

You just need to separate your functions with a comma. The above snippet will get the two data sources and load them asynchronously, which will be a much faster experience for your users. Keep in mind, these are running at the same time, so if you need to filter one data set by a value from another, you can’t run both of those concurrently. However, you can run more than one command within the concurrent, i.e.

Concurrent(
ClearCollect(Table1, myTable1),
ClearCollect(Table2, myTable2);
Set(MyRecord, Filter(Table2, field = value)
)

Note, there is 1 comma, and to run sequential commands inside of concurrent, I use a semicolon to separate the commands. In the above example, it will get data from myTable1 while it’s getting it from myTable2 and finding MyRecord.

Connection.Close()

Just kidding, we don’t have to close connections, PowerApps handles it all.

Check out this Microsoft article for more details into how data connections work.

Any tips you’d recommend?