Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Converting datetime from text data type in UTC format

Hi,   I am cracking my head trying to convert the data type from text to datetime using the DateTime.FromText function in Power Query. Basically, I have the following text format: Oct 23, 2022...
Need Help
  • jennratten's avatar
    jennratten
    3 years ago

    Hello - this will make the conversion:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k8uUTAy1lEwMjAyUrCwMjAI8FUIDXFWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    DateTimeUTC = Table.AddColumn(Source, "DateTimeUTC", each DateTime.AddZone(DateTime.FromText(Text.BeforeDelimiter([Column1], " UTC")),0)),
    #"Changed Type" = Table.TransformColumnTypes(DateTimeUTC,{{"DateTimeUTC", type datetimezone}})
in
    #"Changed Type"

     

jennratten's avatar
jennratten
Super User
3 years ago

Hello - this will make the conversion:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k8uUTAy1lEwMjAyUrCwMjAI8FUIDXFWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    DateTimeUTC = Table.AddColumn(Source, "DateTimeUTC", each DateTime.AddZone(DateTime.FromText(Text.BeforeDelimiter([Column1], " UTC")),0)),
    #"Changed Type" = Table.TransformColumnTypes(DateTimeUTC,{{"DateTimeUTC", type datetimezone}})
in
    #"Changed Type"

 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

This is great! Thank so much jennratten