Get date from whole text date (Power Query)

[DM_EU]

Hello,

I need to get a date format of this : August 24th 2022, 12:59:22.366 in Power Query is it possible? When I just change the datatype a get an error.

Thank you

[AlexisOlson]

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"

[v-stephen-msft]

Hi @DM_EU ,

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.

[davehus]

Hi @DM_EU ,

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.