OLE DB or ODBC error: [Expression.Error] We couldn't parse the Duration literal..

[jerrywalk1]

Hi. I am trying to figure out this error. There are no errors when i do a preview in query editor and data converts to duration with no issues. But when I go to apply it gives the error but no details, just the 2 dots at the end. Any help on trying to determine where the error is happening would be appreciated.

[edhans]

Duration is not a recognized data type by DAX or the Power BI Model @jerrywalk1. That shouldn't cause the error, but you should explicitly convert it in Power Query. For example:

This Duration of 1 hour:

is converted to this fraction of a day on import.

It could be in PQ trying to automatically convert it in the import through Duration to Decimal it is crashing. So explicitly convert that duration field to a decimal number and see if that fixes it.

[jerrywalk1]

In my current query the very next step after changing type to duration is changing type to number. This was already step before the error.

[edhans · Accepted solution]

Then there is an error in the conversion. After that step, go to the home ribbon, Keep Rows, Keep Errors. See what that returns. If you see a yellow "error" field, click next to the word error, not on the word. At the bottom of your screen it should give you a larger error message and the data it is having a problem with.

You need to post screen shots, link to a sample data file or something. It is very difficult to troubleshoot many transformation errors without specific info. 

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[jerrywalk1]

The above helped alot. I was able to see the error and i have about 12 rows that have 24:00:00 and the duration cannot parse that. Now I just need to figure that out. Whether to just remove those or figure out how to keep them.

Thanks,

Jerry

[edhans]

I suspect those should be changed. That is going to be a tricky decision. Replace with #duration(0,0,0,0) or do you need to add a day? 

If you could mark one or more answers as the solution so others know the technical side has been solved it would be appreciated. The business side (what to do with those 24 hr durations) is up to you @jerrywalk1 . 😁