Forum Discussion
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 (start time, end time, etc.) and are in the (admittedly odd) format:
"ddMMMyy:HH:mm:ss" (e.g. 01SEP22:22:15:00)
At the moment, Power BI imports these columns as text. I would like these columns to be recognised as DateTime so I can use them in reports.
I have found a method from this forum to add a new column (EVENT_TIME_PBI) by approprately parsing the original column (EVENT_TIME):
#"Added Custom1" = Table.AddColumn(#"Added Custom", "EVENT_TIME_PBI", each DateTime.FromText([EVENT_TIME], [Format="ddMMMyy:HH:mm:ss"])),
and have confirmed this works to provide the EVENT_TIME_PBI as DateTime type.
What I want to have, however, is no extraneous columns and the EVENT_TIME column containing the correctly-formatted DateTime data, as the column name is well-known to report users.
Is there a way to either: specify the format of the DateTime data for the import; or perform the post-import conversion 'in place'?
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
2 Replies
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 Custom1Thanks 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?
