DataFormat.Error: The provided input che input provided as a Date value.undefined

[Anonymous]

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

[v-shex-msft]

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

[Anonymous]

@v-shex-msft 

Actually, I might not understand how to use your solution. I am new to Power BI. Can you explain how to use your code? I had not tried that. Thank you so much!

[v-shex-msft]

Hi @Anonymous ,

These are power query formula, you can enter to query edit and create a blank query to paste these code.
If you still confused to use it, can you please share some sample data for test?

Regards,

Xiaoxin Sheng

[Anonymous]

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. 

---

@v-shex-msft 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

---