Extract DateTime from YYYYMMDDHHMMSS

[joshua1990]

Hi experts!

I have a calumn of that format:

DateTime

202201311529530000

202201311829530000

000000

000000

Now I would like to transform that into this:

DateTime

31.01.2022 15:29:53

31.01.2022 18:29:53

How is this possible ussing Power Query? 

Since there are a lot of columns with that kind of format I would like to use the solution as a function.

[AlB · Accepted solution]

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.

[AlB · Accepted solution]

@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.

[AlB · Accepted solution]

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]

 @AlB : THanks a lot! It works perfectly well. How can I switch this into a individual function / query? I would like to use this functionn for multiple columns

[AlB]

Hi @joshua1990 

Are the last four zeros to be ignored? If not, please show an example in which those positions are not zero with the expected output

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.