Simpler way to convert 16 characters text to date

[mart1fio]

I use data that come from SQL tables where date is recorded as a 16 character date/time text string.

This is an example of one - 20210630192455

I want a simpler way of converting this to date time than I am currently doing. It initially appears in my table as Text. If I just change the data type to Date/Time it errors with the error being it cant parse the date/time. Right now I do the following;

1. Split the column by character using 8 as my number of characters. I now have 2 columns with 8 digits each - one just the date and the other the time. PBI auto classifies the columns as whole number
2. Delete the auto formatting of the column to whole number. If I don't do this, I have to add a new step to convert the number to text
3. Once the data type is text again, I change the data type for the first column to date and the second column to time
4. If I need it, I will then merge the 2 columns and set the format as date/time. If I don't I will either leave them or delete the time column

I then have to rename all my columns.

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula in DAX should work

Date = date(left(Data[String],4),mid(Data[String],5,2),mid(Data[String],7,2))

Hope this helps.

[mart1fio]

Thank you, I will give it a try. I was hoping to have something in Power Query but this is a start.