Power Query Transform from string to date time

Question

I'm trying to convert a string which is actually a date with the format such as Sep 10 2005 12:03:34:716 PM to a datetime without losing any characters in the string.

I've been trying to use the detect data type and modify data type functions in the Power Query Editor but each of the results cause all values to change into Errors. I am using the correct Locale.

I also tried adding a custom column with the following formula which also results in error values:

= DateTime.FromText(Text.Start([MyTextColumnNameGoesHere],27), [Format="MMM dd yyyy HH:mm:ss:000 tt"])

Can anyone lend me a hand in resolving this? I've been at it for hours.

Kind Regards,

Updated, this the formula I used:

DateTime.FromText([Column8], [Format="MMM dd yyyy HH:mm:ss:fff tt"])

Advanced Editor:

let
    Source = Csv.Document(File.Contents("D:\Users\rubhi\Downloads\access_control_t.bcp"),[Delimiter="#(tab)", Columns=10, Encoding=20127, QuoteStyle=QuoteStyle.None]),
    #"Replaced Value" = Table.ReplaceValue(Source,"P"," P",Replacer.ReplaceText,{"Column8"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","A"," A",Replacer.ReplaceText,{"Column8"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1"," Au","Au",Replacer.ReplaceText,{"Column8"}),
    #"Changed Type with Locale1" = Table.TransformColumnTypes(#"Replaced Value2", {{"Column8", type text}}, "en-US"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale1", "Custom.8", each DateTime.FromText([Column8], [Format="MMM dd yyyy HH:mm:ss:fff tt"]))
in
    #"Added Custom"

horseyride · Accepted Answer · 2024-04-26 18:13:23Z

0

Try

= DateTime.FromText([MyTextColumnNameGoesHere], [Format="MMM dd yyyy HH:mm:ss:fff tt"])

see https://learn.microsoft.com/en-us/powerquery-m/datetime-fromtext

sample code

let Source = #table({"Column8"},{{"Sep 10 2005 12:03:34:716 PM"}}), 
x= Table.AddColumn(Source, "Column8.1", each DateTime.FromText([Column8], [Format="MMM dd yyyy HH:mm:ss:fff tt"]),type datetime) 
in x

edited Apr 26, 2024 at 18:13

answered Apr 25, 2024 at 19:44

horseyride

21.6k3 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

RSU Over a year ago

Thanks, but it didn't work. I'll post some screenshots.

RSU Over a year ago

Done - hope that's good.

RSU Over a year ago

I still can't get it to work. Input is exactly the same in your response. The original file is a bcp imported into excel where I'm then using the Power Query Editor to create a custom column. Also, the bcp file comes from a Sybase db where datetime formats are formatted differently: mon dd yyyy hh:miAM (or PM). I added a space after the milliseconds for the AM/PM but dates still don't convert or parse properly.

horseyride Over a year ago

If above works, and yours does not, then your actual data is not similar to the data I am using as input which you provided

RSU Over a year ago

tested with that and it still doesn't work. I am not sure why the data is different. I'll investigate more, but leave the thread open. Thanks for your help.

Collectives™ on Stack Overflow

Power Query Transform from string to date time

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related