Transform Text Column into Date Column

[nove718]

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

[V-yubandi-msft]

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]

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]

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.

[ryan_mayu]

@nove718 

the easiest way is to use Custom From Examples. you just need to type the first 2 expected output. Then PQ will automatically generate the rest for you.

[nove718]

Hey! Thanks for the quick response, I tried to follow your formula down to the letter but im not sure what I'm doing wrong? @ryan_mayu 

[ryan_mayu]

@nove718 

If you are not familiar to the formula, just try to type in the output by using custom column example. The first icon of the menu (not the custom column)

[SamsonTruong]

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