Expression.Error: We couldn't parse the input provided as a Time value. Details: XXX:XX

[Anonymous]

I am importing data from a JSON source where time is stored as minutes and seconds in the format xx:xx however minutes can exceed 59 (ex:  716:22).  In my query some of the columns are showing this correctly while some columns are converting this to hh:mm:ss AM\PM (ex. 21:48 is showing up as 9:48:00 PM).  As a result some of the rows are reporting errors as the values (I'm guessing its because some of the values exceed time ranges).  My questions are:

1) Why is it working for some columns and not for others.  Is it similar to SSIS when it detects the first few rows and applies a format.

2) Can I change the format?  I've tried changing the data type to text and also using power query functions but to no avail.

3) How can I fix it to show in the correct format?

Any help would be appreciated.  I am somewhat new to Power BI and any help would be appreciated.

[artemus · Accepted solution]

The conversion to time is best effort, and there is no standard time format which goes beyond 59 minutes. Your best bet is to transform the column tusing a custom transform

The time data time is for time of day. The duration data type might be better based on what you do as it is about an amount of time that has passed.

When viewing the time data type in the preview it will display it according to your locale setting. This does not impact the data, only how you view it.

If you load a duration data type into power bi, it will be converted to a number equal to the number of days the duration represents (or a value between 0 and 1 if less than 1 day).

You can use this custom step to convert your data to a duration:

= Table.TransformColumns(Source, {{"Column", each let timeParts = Text.Split(_, ":"), minutes = Number.FromText(timeParts{0}), seconds = Number.FromText(timeParts{1}) in #duration(0, 0, minutes, seconds), type duration}})

Just replace Source with the name of the previous step (which is auto filled in on add custom step) and "Column" with the name of the Column you want to convert 

[Anonymous]

Thanks for your help artemus.  I was actually able to find another way to resolve my issue and that was in the advanced editor where I load my JSON results into a table.  I noticed in there you can set the type and this particular column was set to time where others with similar values were set to text so I set it as text as well and this resolved my errors.  However your reply does help me for another transformation i thought of implementing by taking the result and converting it to a duration.  Thanks