Settings - What's the purpose of the "Explicit Column Selection" Setting?

Many users often discover this setting only when they encounter a problem where data doesn’t load or appear correctly. In such cases, disabling this setting can magically fix the problem.

What exactly is the purpose of this setting and how does it work?

In this post, we’ll explore how this setting works against a SQL Server data source.

What does "Explicit Column Selection" do?

When an app carries out a data retrieval operation, “Explicit column selection” improves performance by retrieving only the data in the rows and columns that are necessary. It is enabled by default and it is good practice to retain this setting, because it improves app performance.

This feature works only supported data sources, which includes SQL Server and Dataverse.

Demonstration - Data Retrieval with "Explicit Column Selection" Enabled

We now turn off “Explicit column selection” and repeat this exercise. As we expect, Power Apps issues a query that returns all columns. This query is far less efficient because we return more columns than is necessary.

What Practical Impact does this have on Performance?

With this example data, the data transfer size of all columns in 2,000 rows is 456KB. With just the PropertyID, Address1, and City fields, this falls to 152KB. With this specific example therefore, “Explicit column selection” can reduce the gallery load time by around 60%.

What problems can “Explicit Column Selection” cause?

Most commonly, this setting can prevent data loading or appearing correctly in an app. If we experience some of these problems, we should investigate the “Explicit column selection” setting:

• Certain fields appear blank, particularly in cases where we populate controls based on collections that are pre-populated from SQL Server
• Drop-down fields fail to show the correct data
• Controls populate correctly at design time, but not at runtime.

Example of a Typical Problem

Here’s an example of a typical problem. Here, we have a table of issues that are linked with the properties table through the PropertyID field.


Let’s say we want to populate a collection of issue records that match PropertyID 35. To do this, we write the following formula:

ClearCollect(colIssues, Filter('[dbo].[Issue]', PropertyID=35))

When we run this and inspect the colIssue collection, we see that the formula returns only data for the IssueID and PropertyID fields. The values in all other fields are blank. 

In practical terms, this means that we cannot use this collection to display the issue descriptions, because these values have not been loaded into the collection.

Fixing this Problem

The fix for this problem is to explicitly define the columns that we want to return when we build the collection. We can do this by calling the ShowColumns function.

ClearCollect(colIssues, 
             ShowColumns(Filter('[dbo].[Issue]', PropertyID=35), "IssueID", "Description")
)

As we can see, the collection will now include just the IssueID and Description fields.
 

Conclusion

In summary, "Explicit Column Selection" is a setting that improves performance with SQL Server and Dataverse data sources. The best practice is to keep this setting enabled.
This setting can cause unexpected behaviour with data not appearing as expected. Often, the easiest fix is to turn off “Explicit column selection” however, the best way to solve these types of issue is to explicitly define the columns that we want to return with the ShowColumns function.