Forum Discussion

jknottUOW's avatar
jknottUOW
Frequent Visitor
3 years ago
Solved

Import csv column with an odd-but-known DataTime format as DateTime type (not text)

I am looking to import a list of transaction records from a CSV. There are 100+ columns with various parameters, some of the columns represent specific DateTime values related to the transaction (sta...
Tips and Tricks
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    3 years ago

    For in-place conversion, insert this step

    = Table.ReplaceValue(Source, each [EVENT_TIME], each DateTime.FromText([EVENT_TIME], [Format="ddMMMyy:HH:mm:ss"]), Replacer.ReplaceValue, {"EVENT_TIME"})

    Code for testing

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIwDHYNMDKyAiJDUysDA6VYnWilJKCEkaG/c4iRsZUhskQyUMLQ0MvRD6jcAKgJIhELAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, EVENT_TIME = _t]),
    Custom1 = Table.ReplaceValue(Source, each [EVENT_TIME], each DateTime.FromText([EVENT_TIME], [Format="ddMMMyy:HH:mm:ss"]), Replacer.ReplaceValue, {"EVENT_TIME"})
in
    Custom1
jknottUOW's avatar
jknottUOW
Frequent Visitor
3 years ago

Thanks very much for the quick and detailed response Vijay_A_Verma .

 

As seems to always be the case, I also managed to build an alternate solution via:

#"Parsed Dates" = Table.TransformColumns(#"Promoted Headers", {
        {"EVENT_TIME", each DateTime.FromText(_, [Format="ddMMMyy:HH:mm:ss"]), type datetime},
        {"EVENT_TIME_END", each DateTime.FromText(_, [Format="ddMMMyy:HH:mm:ss"]), type datetime}
        })

 

Would there be a functional difference in the two approaches?