Forum Discussion
Transform Text Column into Date Column
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.
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 null
FYI:
— Yugandhar
Community Support Team.