Filter on SQL data source no longer working

[Interlock]

I have a Power App I've been using for at least a year, maybe two, and it has a button which performs the equivalent of the following.  I've simplified it a bit for the purposes of this forum post, but this simplified version exhibits the same problem:

ClearCollect(MyCollection, Filter('[dbo].[Tasks]', ID = 209 )

The "[dbo].[Tasks]' table has 3 columns - ID, ID_Client, and Task.  The problem is that only the first two of these are being loaded into my Collection - the "Task" column is being missed out.  If I remove the Filter, and just do:

ClearCollect(MyCollection, '[dbo].[Tasks]')

...then it works fine - all three columns are returned.  So it looks like something weird to do with the filtering operation.  If I use the PowerApps Monitor tool to see what's going on under the hood, the "x-ms-request-url" is being set to the following (have redacted the server and database names):

/apim/sql/23cd4685c08249129ca710527d3ee0df/v2/datasets/servername.database.windows.net,databasename/tables/%255Bdbo%255D.%255BTasks%255D/items?%24filter=ID_Client+eq+209&%24select=ID%2CID_Client&%24top=500

The "%24select=ID%2CID_Client" part suggests to me that only the ID and ID_Client columns are being requested from Azure SQL.  As I say, this has been working for well over a year, and it's not exactly complicated!  So I'm wondering why it's not working.  Am I missing something? Is it just plain broken at the Microsoft end?

The original Power App used v1 of the SQL connector.  But I get the same problem with v2 of the SQL connector, and a completely new test Power App created from scratch.

[timl · Accepted solution]

Hi @Interlock 

Out of interest, can you confirm if you've disabled 'explicit column setting'?

Although I can't explain why it used to work, but is no longer working, if you were to specify the columns with the ShowColumns function, does that make a difference?

ClearCollect(MyCollection, 
             ShowColumns(Filter('[dbo].[Tasks]', ID = 209 ),
                         "ID", "ID_Client", "Task")
)

[Interlock]

@timl, truly thou art a superstar!  You have nailed it.

• If I switch off "Explicit Column Selection" and relaunch the app, it works.  
• If I keep "Explicit Column Selection" on, and wrap my filter in a "ShowColumns", it also works.

I see that the description for "Explicit column selection" is "Optimizes load times and reduces memory consumption by only fetching columns used in your app.  Target data source must support this feature".  The thing is, the "Task" column IS used in my app, it's just that it wasn't explicitly specified with ShowColumns in the formula as per your suggestion.  Hoping that this behaviour will change before this feature comes out of Preview, because it seems to me that Power Apps ought to have seen references to this column elsewhere in the app, and requested it from SQL Server.

Anyway, I now know what the problem is, and I can make things work.  Thank you so much for saving my sanity.

[timl]

Thanks @Interlock! I'm glad to have helped.