Forum Discussion

nove718's avatar
nove718
New Member
1 year ago
Solved

Transform Text Column into Date Column

Hello, I've searched the forum and reviewed other examples of this issue but I'm not sure if the timestamp down to the microseconds is giving me a unique issue that may require an altered solution? ...
Need Help
Show and Tell
  • V-yubandi-msft's avatar
    V-yubandi-msft
    1 year ago

    Hi nove718 ,

    I tried reproducing your scenario using sample data, and I was able to achieve the desired output successfully.  I've attached a .pbix  file for your reference  please take a look and review the details.

     

    I created a custom column in Power Query using the following M code to convert your Create date values into proper DateTime format.

    let
    rawText = [CREATE_DATE],
    formattedText = Text.Start(rawText, 10) & " " &
                    Text.Middle(rawText, 11, 2) & ":" &
                    Text.Middle(rawText, 14, 2) & ":" &
                    Text.Middle(rawText, 17, 2) & "." &
                    Text.Middle(rawText, 20, 3)
in
    try DateTime.FromText(formattedText) otherwise null

     

    FYI:

     

    — Yugandhar
    Community Support Team.

SamsonTruong's avatar
SamsonTruong
Super User
1 year ago

Hi nove718 ,

Please try the following Power Query (M) Code to transform you data to dates:

= DateTime.FromText(Text.Replace(Text.Replace([CREATE_DATE], "-", " "), ".", ":"))


This formula replaces the dash between the date and time with a space, and then replaces the periods with colons to match a standard datetime format. Finally, it applies the DateTime.FromText function to convert the cleaned text into a datetime value.

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson

 

Connect with me on LinkedIn

Check out my Blog

Going to the European Microsoft Fabric Community Conference? Check out my Session