Get date from whole text date (Power Query)

Hi Anonymous ,

 

Try right clicking the column>Change Type and Use Locale and see if this works for you. Had similiar issues before and it did the job.

 

HTH

 

Did I help you today? Please accept my solution and hit the Kudos button.

Hi Anonymous ,

 

It is possible if your number of seconds does not have decimals.

If you insist on such a format, it seems that it can only be rendered in text format.

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

The annoying part is the ordinal day ("24th" instead of "24"), so you may need to strip that out first before parsing the date and time.

 

Here's one possible approach that works for me:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcixNLy0uUTAyKclQMDIwMtJRMDSyMrW0MjLSMzYzU4rViVbyTaxUMASpAUsbGFoZGFkZm+iZmpkrxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,each Text.AfterDelimiter([Column1], " "), each Text.Remove(Text.AfterDelimiter([Column1], " "), {"a".."z"}),Replacer.ReplaceText,{"Column1"}),
    #"Format as Date" = Table.TransformColumns(#"Replaced Value",{{"Column1", each DateTime.FromText(_, [Format="MMMM d yyyy, HH:mm:ss.fff"]), type datetime}})
in
    #"Format as Date"