How to change data type text to date direct query mode

Hi SBC ,

DirectQuery has limitations in the data transformations that can be applied within Power Query Editor.

First, when connecting to an OLAP source like SAP Business Warehouse, no transformations can be defined at all, and the entire external model is taken from the source. For relational sources, like SQL Server, it's still possible to define a set of transformations per query, but those transformations are limited for performance reasons.

Any such transformation will need to be applied on every query to the underlying source, rather than once on data refresh, so they're limited to those transformations that can reasonably be translated into a single native query. If you use a transformation that is too complex, you receive an error that either it must be deleted or the model switched to import.

You can create a measure to replace the date column

Here is an example:

Here is the measure:

Measure = 
var _y = VALUE(LEFT(MAX('Table'[Column]),4))
var _m = VALUE(LEFT(RIGHT(MAX('Table'[Column]),5),2))
var _d = VALUE(RIGHT(MAX('Table'[Column]),2))
return DATE(_y,_m,_d)

Then change the format of the measure:

Final output:

Kind Regards,

Bubble

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.