Forum Discussion
DataFormat.Error: The provided input che input provided as a Date value.undefined
Hello!
I have a JSON file full of data (timestamp, power, voltage, current). This data is the electric consumption of a device and it saves new values into the file every 5 seconds. The file then gets uploaded at the end of the day. This means when I load the file into Power BI, I get a table with a lot of rows. My problem is that I am having trouble converting the timestamp into "Time Date" format. I get errors in all the rows where the timestamp hast only one digit for the hour, minute or seconds. For example, the error below shows because the seconds are 5.488 instead of 05.588. If I change the timestamp manually to 2019-6-16T20:58:05.488, I will not get any errors. But I cannot do that with all the rows that have errors, because there are too many. Does anyone know how to do this automatically? Or any other solution? Thanks in advance!
DataFormat.Error: The provided input could not be analyzed as a DateTime value.
Details
2019-6-16T20:58:5.488
4 Replies
HI Anonymous ,
You can try to use split column function to split your field to 'date','time','millisecond' fields. I attached my sample query table below:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ10zU0CzEysDK1sDLVM7GwUIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [TS = _t]), #"Split Column by Delimiter" = Table.SplitColumn(Source, "TS", Splitter.SplitTextByDelimiter("T", QuoteStyle.Csv), {"Date", "Time"}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Time", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Time", "Milliseconds"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Date", type date}, {"Time", type time}, {"Milliseconds", Int64.Type}}) in #"Changed Type"Regards,
Xiaoxin Sheng
Hello, thanks for your reply.
Unfortunately, it does not work. If I split it, I still have errors in the "time" column, as the time is still
20:58:5.488
Therefore, it is not recognized as a time format.
Anonymous wrote:HI Anonymous ,
You can try to use split column function to split your field to 'date','time','millisecond' fields. I attached my sample query table below:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ10zU0CzEysDK1sDLVM7GwUIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [TS = _t]), #"Split Column by Delimiter" = Table.SplitColumn(Source, "TS", Splitter.SplitTextByDelimiter("T", QuoteStyle.Csv), {"Date", "Time"}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Time", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Time", "Milliseconds"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Date", type date}, {"Time", type time}, {"Milliseconds", Int64.Type}}) in #"Changed Type"Regards,
Xiaoxin Sheng