Extract DateTime from YYYYMMDDHHMMSS

A possible solution (ignoring those last 4 zeros)

Place the following M code in a blank query to see the steps.

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjIwNDY0NDWyNDU2AAKlWB0kYQsUYQMDDGYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateTime = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if [DateTime]= "000000" then null else #datetime(Number.From(Text.Start([DateTime], 4)), Number.From(Text.Middle([DateTime], 4,2)), Number.From(Text.Middle([DateTime], 6,2)), Number.From(Text.Middle([DateTime], 8,2)),Number.From(Text.Middle([DateTime], 10,2)), Number.From(Text.Middle([DateTime], 12,2))), type datetime),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"DateTime"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "DateTime"}})
in
    #"Renamed Columns"

 

 

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

joshua1990 

You could use a function like this and create a custom column using the function to do the conversion, then delete the original column. See it all at work in the attached file.

(input_ as text) => 
if input_= "000000" then null else #datetime(Number.From(Text.Start(input_, 4)), Number.From(Text.Middle(input_, 4,2)), Number.From(Text.Middle(input_, 6,2)), Number.From(Text.Middle(input_, 8,2)),Number.From(Text.Middle(input_, 10,2)), Number.From(Text.Middle(input_, 12,2)))

 

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.