How to convert text column to date or time

[kattlees]

I have two columns in my database for date and time. The issue is they are entered as text.

Examples would be

082520        1258

051620         0700

Is there a way in power query to have these convert to a date and time field automatically? I was trying a custom column but can't figure out the code to use.

Thanks in advance.

[v-lionel-msft · Accepted solution]

Hi @kattlees ,

This is troublesome, maybe you can do this.

Please refer to my .pbix file.

Best regards,
Lionel Chen

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

[pranit828]

HI @kattlees 

In power query you have a function if it helps.

DateTime.FromText()

https://docs.microsoft.com/en-us/powerquery-m/datetime-fromtext

Alternatively you can use the DAX functions DATE() and TIME()

[amitchandak]

@kattlees , in Dax

Date = date(right([col1],2), left([col1],2), mid([col1],3,2))

time = time(left([col2],2),right([col2],2),)