Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mart1fio
Frequent Visitor

Simpler way to convert 16 characters text to date

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.

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.