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,

Thank you for being a part of the Microsoft Fabric Community. You're correct the issue arises because the create date  values are in a non standard timestamp format (YYYY-MM-DD-HH.MM.SS.MICROSECONDS), which Power Query doesn't automatically recognize as a valid datetime.

 

The method suggested by ryan_mayu , using the  Add Column from Examples  feature, is a straightforward and effective solution. I recommend trying  ryan_mayu  approach. If you need further assistance or clarification, feel free to ask we’re here to help.

 

Special thanks to ryan_mayu , for the quick and insightful contribution to the community.

 

— Yugandhar
Community Support Team.

 

nove718's avatar
nove718
New Member
1 year ago

Hello, 

I responded to his message because I'm having a hard time recreating the solution, not sure what I'm doing wrong? V-yubandi-msft 

  • V-yubandi-msft's avatar
    V-yubandi-msft
    Community Support
    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.