How to resolve a data parsing error for time in Power BI
Troubleshoot expression error: We couldn't parse the input provided as a Time value
Today on the blog, I'm going to explain how to handle an error that can come up in Power BI when trying to process data in the "Time" format.
Have you ever run into this error when trying to convert a column to Time format in Power BI?
Expression error: We couldn't parse the input provided as a Time value
Context
The dataset in the report is from an applications performance monitoring tool. It records data on each request that the application receives. In the table, there is a timestamp column
The report requirements included specific time analysis so I had to separate date and time into two separate columns. As can be seen in the image above, the time column has additional figures to adjust the time values for each time zone.
The wrong way to separate a Timestamp column
To create separate columns, I initially used the "Split Column" function to split the Timestamp column into two, then I changed the data type to Date and Time for each column respectively
I would receive these errors when I tried to load data into the model
The problem with this method is that splitting the column converts each column to text format as can be seen in the image above.
This was the source of the error. When Power BI tries to convert some time/text values such as 23:00:00 +00:00, it is unable to determine the time.
The better way to separate a Timestamp column
To remove all time parsing errors, a better way to separate and change the format of the columns is to Duplicate the Timestamp column and change each column to Date and Time formats respectively.
It would be much easier for Power BI to convert a value from type Timestamp to type Time than it is to convert type Text to type Time.
and Voila! no more errors :)
A simple solution that eluded me for many weeks. If you run into this kind of error, try to start troubleshooting from inspecting data formats and conversions.
Please share if you found this useful and leave a comment if you have any.
Thank you.
Yours, Kiite