Error While converting to date format

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.