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
Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
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