We cannot convert the column automatically to Date Type

[Anonymous]

I have a date column with values as 2014-11-08 08:58:30.3 format. I am trying to convert the value as 2014/11/08 because i need to use Power bi custom Timeline slicer and it takes date in this format. When I am trying to change the Format under Modelling tab for the column, i am getting the error on We cannot convert the column automatically to Date Type

Can anyone give me steps how to approach to my solution. I have date for different years. Thanks

[v-jiascu-msft]

Hi @Anonymous,

Please do it in the Query Editor. 

Best Regards,

Dale

[McCow · Accepted solution]

Hi @Anonymous

you can try convert the data column manual, as text field in Query Mode like this

 First split column, then convert. If you don't need a time - remove it at last.

And HERE is full example

You can see, yours text date field was correct converted to DATE

Best regs

[prasa]

The link provided is broken.. Can you please update the link ?

[McCow]

Actually not, but I duplicate the file yet HERE

[prasa]

@MC - Thanks for the link 🙂 Actually in my case the text column is in this format: 

2018-03-07 10:07:04 EST

 and need to convert to date time. I am able to split the column to 2 and convert the first part to date. How do I convert the second one to time? 

Advanced thanks for the help. 

[McCow]

Hi @prasa!

Actually, once what you need is split your column twice. Or better one column to three.

Please, look on example. PQ make this semiauthomatic.

And will be nice if you create new ticket with new relevant solution.

Best regs.