Change/Convert SQL column having datetime stored as Text to DateTime in PowerBi Desktop

[anikettakalkar]

I am trying to visualize performance counters data stored in SQL tables using Power Bi. 

It has two tables 1. CounterData 2. CounterDetails

In CounterData table datetime is stored as text format, i want to use that data as DateTime in Power Bi Desktop. 

But unable to do so,  How can I convert the text column into dateTime using both query mode - Import & Direct Query ? 

[v-yuezhe-msft · Accepted solution]

@anikettakalkar,

I make a test using the sample table below. 

In DirectQuery mode, create the following columns.

Left = LEFT(CounterData[RecordTime],FIND(" ",CounterData[RecordTime],1,0)-1)

Right = RIGHT(CounterData[RecordTime],LEN(CounterData[RecordTime])-LEN(CounterData[Left])-1)

After creating the above columns, change data type of Right column to Time. Then create the following columns.

Year = LEFT(CounterData[Left],4)

Day = RIGHT(CounterData[Left],LEN(CounterData[Left])-FIND("/",CounterData[RecordTime],6,1))

Month = MID(CounterData[Left],6,LEN(CounterData[Left])-6-LEN(CounterData[Day]))

Date = DATE(CounterData[Year],CounterData[Month],CounterData[Day])

DateTime = CounterData[Date]+CounterData[Right]


In Import mode, you can directly split the RecordTime column in Query Editor and the splited columns can be changed to Date and time format.

Then you can create a calculated column using DAX: DateTime=CounterData[RecordTime.1]+CounterData[RecordTime.2]

Regards,
Lydia