Converting Date of Type: Text to Date but not changing to Import Mode

[SachinC]

My report uses Direct Query connecting to a MS SQL Server.

My datevalue is in the format: 2019-12-30T00:00:00

My datevalue is of type: Text

I want to convert this to type: Date.

When I do this; it comes up with: "This step results in a query that is not supported in DirectQuery mode." Button: 'Switch all tables to Import mode'

I do not want to make this Import Mode or Mixed Mode (Import Mode and Direct Query). I want to remain in Direct Query mode.

Please help.

 

[JustynaMK]

As you already discovered, lots of transformations and functionalities are not supported in Direct Query mode. If your query was converted to an Import Mode, you could easily split the column by "T" delimiter in order to retrieve the Date.
Some date columns can still be transformed while in Direct Query mode, like "2019-12 30 00:00:00", but in your case "T" delimiter is the culprit.

I suspect that the easiest solution would be to take a step back and transform the date on SQL query level. In order to adjust your SQL statement, you can go to Power Query Editor > Applied Steps > Source > press the "cog" icon > SQL statement:

 

[SachinC]

Hi, sorry I could not see your SQL statement it was cut off. Please copy/paste. Thank you.

 

[JustynaMK]

Ahh I didn't post any SQL statement, sorry for the confusion I am just showing where you can adjust your existing SQL statement. If you need any help with adjusting it, paste it here and I'll try to convert your Date column into a proper format.