simplest of queries results in "step results in a query not supported in DirectQuery mode"

[dedelman_clng]

I have a single query against a single table, and I am getting the error "This setp results in a query that is not supported in DirectQuery mode." I do not understand what the issue might be.

The data source is a SQL Server Cluster (2014), and I am selecting all columns from a single table. The Query Editor code is this (actual names replaced with "myserver"/"mydb"/"my_table"):

let
    Source = Sql.Database("myserver", "mydb"),
    dbo_my_table = Source{[Schema="dbo",Item="my_table"]}[Data]
in
    dbo_my_table

I am on Version: 2.71.5523.941 64-bit (July 2019). This is a table, not a view, so there shouldn't be any underlying complex code.

Any help appreciated.

Thanks

David

[parry2k]

@dedelman_clng so you are saying these are the only steps in your power query, and you are using direct query to get data from sql and you are getting error message. this doesn't make sense. Can you confirm on steps?

[dedelman_clng · Accepted solution]

Hi @parry2k -

You are correct.  Here are the steps with screen shots to show what I'm doing:

1) New report, Get Data -> SQL Server

2) Enter server and db name, select "Direct Query", press OK

3) Choose the table I want and click "Edit"

4) In the query editor, highlight the "Source" step and see the error message

That's all I am doing. 

ETA: once I hit "Close and Apply" it says that the table is in Direct Query mode, and when I publish it to the service it has the data cache refresh option. The reason I had gone on this path in the first place is that a report I had created I thought was Direct Query but it didn't have the correct options in the service. It had the same error message as creating the connection in a new report, so I thought it might be connected. Maybe the other one got corrupted or something.

ETA2: Going through different iterations, it looks like the presence of CALENDARAUTO() in the report is what kept that dataset from acting like Direct Query and giving the correct refresh options. Still odd that the error message appears in the Query Editor like that. Thanks for reading my post and getting me to do more investigation on my side.

Thanks

David

[LauraMay]

Hi @dedelman_clng 
I have exactly this issue, what was your solution?

thank you

[dedelman_clng]

Hi @LauraMay - this was long ago, but I believe that I brought in a calendar from SQL instead of using CALENDARAUTO() and it allowed DirectQuery to work properly.