Forum Discussion
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? I knew I couldn't select to make it a Date column in Power Query Editor so I tried to create a custom column using "= Date.FromText" & "Date.FromText Start,Middle, & End" and this also didn't work.
I tried to follow some videos and other forums and still unable to get it to work. Any help would be greatly appreciated!
| CREATE_DATE |
| 2025-05-28-14.36.41.578000 |
| 2020-07-30-09.04.42.618000 |
| 2020-08-12-17.28.19.715000 |
| 2025-05-28-14.38.11.367000 |
| 2025-05-28-14.38.14.932000 |
| 2025-05-28-14.38.12.704000 |
| 2020-08-13-09.29.08.020000 |
| 2020-08-20-10.58.04.431000 |
| 2025-06-11-12.44.25.575000 |
| 2025-06-11-12.44.25.575000 |
| 2020-09-25-11.35.43.008000 |
| 2025-06-11-12.44.25.575000 |
| 2025-06-11-12.44.25.575000 |
| 2025-06-11-12.44.25.575000 |
| 2021-03-15-09.20.35.814000 |
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 nullFYI:
— Yugandhar
Community Support Team.
10 Replies
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
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.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
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 nullFYI:
— Yugandhar
Community Support Team.
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.
Hi nove718 ,
Does the solution provided address your issue, or are you still experiencing the problem? Your feedback is important to the community. If your issue has been resolved, please mark the response as Accepted Solution to help others find it more easily.
Thank you.Hi nove718 ,
I hope my message clarified everything. If everything is resolved, please mark it as Accepted solution. Please let me know if you are facing any issues.
Thank You.
