Jaz,
Power Query is sometimes smart enough to recognize a format for a datetime value. However, in cases where it fails, it can be helpful to be very specific what format it should expected.
For example, if you add a new column and try to extract a datetime value from the DISP column you could write:
Table.AddColumn(
#"Promoted Headers",
"DateTimeValue",
each DateTime.FromText( [DISP], [Format = "yyyy-MM-dd h:mm:ss tt"]),
DateTime.Type
)
Since you already have existing column, it may be easier to use a format like:
Table.TransformColumns(
#"Promoted Headers",
{{"DISP", each DateTime.FromText( _, [Format = "yyyy-MM-dd h:mm:ss tt"]), DateTime.Type }}
)
To extend this for other columns you can add additional lines.
Table.TransformColumns(
#"Promoted Headers",
{{"DISP", each DateTime.FromText( _, [Format = "yyyy-MM-dd h:mm:ss tt"]), DateTime.Type },
{"ARRIVED", each DateTime.FromText( _, [Format = "yyyy-MM-dd h:mm:ss tt"]), DateTime.Type }}
)
The formatting strings can be a bit tricky, but you can find documentation on the different expected strings for this at:
https://powerquery.how/datetime-totext/
https://powerquery.how/date-totext/
And here's a working example that you can paste into the advanced editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvEMDlCK1YlWMjIwMtY1MNU1NlQwtjI1sTIxVXD0RZcxtTIwsTIwBMvEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Added Custom" = Table.AddColumn(#"Promoted Headers", "DateTimeValue", each DateTime.FromText( [DISP], [Format = "yyyy-MM-dd h:mm:ss tt"]), DateTime.Type ),
Custom1 = Table.TransformColumns( #"Added Custom", {{"DISP", each DateTime.FromText( _, [Format = "yyyy-MM-dd h:mm:ss tt"]), DateTime.Type }} )
in
Custom1
Hope that helps!
Regards,
Rick
--------------------------------------------------
@ me in replies or I'll lose your thread
Master Power Query M? -> https://powerquery.how
Read in-depth articles? -> BI Gorilla
Youtube Channel: BI Gorilla
If this post helps, then please consider accepting it as the solution to help other members find it more quickly.
