How to change data type text to date direct query mode

[SBC]

Hi ,

 DirectQuery mode in PowerBI, can we change data type of a column  from text to date ?

Example:

i have column which contains all dates in text data type,i want to change to date data type in the report.

Metaparttionvalue

2022/07/21

2022/07/22

2022/08/02

.........................

below is the error while changing data type from text  to date

error:

[Apayo]

Hi! I have the same problem in DirectQuery but from text to numeric.

As can be seen, I have some scores as text and I want to convert them to numeric so I can make aggregations. I am getting the following error:

Thanks!

@Bubble4502 

[Bubble4502 · Accepted solution]

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.

[Gayathri_vinay]

I have used same formula but I am getting same value for all the columns and that is with wrong date value.

Could you please tell me how I can solve this.

  

 

 

[Anonymous]

I want to ask why do we need to use the MAX function, when I applied the same function on my table, my new column show the latest date of the [Column] , instead of converting the text on the left side.  However, when I remove MAX function, it doesn't work, and it showed error as Direct Query doesn't support DATE function.  Any solution ??? Thanks.

[DataInsights]

The limitation of a date measure is that you can't use it in a date slicer or in a relationship with the date table. If possible, use the SQL functions CAST or CONVERT, which will enable you to have a date column (as opposed to a measure).

[DataInsights]

@SBC,

Have you tried custom SQL using the CAST or CONVERT functions?