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.

V-yubandi-msft's avatar
V-yubandi-msft
Community Support
1 year ago

Hi nove718 ,

Have you had a chance to try the steps I shared earlier? If they worked, please mark it as the accepted answer so others can find the solution more easily.

 

Thank You.