Power Query : Convert 2013021208352200 to DateTime

[KNO]

Hi, how to I convert string "2013021208352200" to a DateType?

Thanks

[Anonymous · Accepted solution]

@KNO 
Power Query cannot read this Text when there is extra strings others than Date and TIme. With "2013021208352200", I assume you want to get 2013.02.12 08:35:22. To do so, you need to remove the "00" at the end, and add a letter "T" before time to distinguish strings.

You may just change the text datetime to "20130212T083522" in the datasource. Or do some transformations before using DateTime.FromText function:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNDYwMjQysDA2NTIyMFCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date Time" = _t]),
    #"Split Column by Positions" = Table.SplitColumn(Source, "Date Time", Splitter.SplitTextByPositions({0, 14}), {"Date Time.1", "Date Time.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Positions",{"Date Time.2"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each Text.Insert(Text.From([Date Time.1]),8,"T")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each DateTime.FromText([Custom])),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.1", type datetime}})
in
    #"Changed Type"

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[SushainKoul]

Hi @KNO 

Please check detailed steps in the PBIX file below about how to get time part from the column and convert it to Time.

https://1drv.ms/u/s!AhsotbnGu1NolB_GPuXRV5NCay0r