Forum Discussion
Solution SageDuration calculation in query editor produces error message with no error row
Finally figured out what the issue was, though I still don't know "why".
Turns out there is [SLA_updated_date] column in the table. Though this column isn't used in any calculation/measure/relationship etc... This column contained single invalid datetime value. I've asked DB admin to take care of this.
Oddly enough, this did not show up in any of query steps in PowerBI and only found when I used another tool to query the database using JDBC and checked log.
While I wait for the admin to fix the record, I excluded [SLA_updated_date] column from the query, and I was able to use all previously mentioned methods to calculate duration buckets.
Regards,
Solution SageI worked around it for now, by using Native MySQL Query and using TIMESTAMPDIFF().
But I'm still interested in what's causing the odd behaviour mentioned in the first post. Along with solution within query editor.
Microsoft Employee
Below expression works properly for me:
=Duration.Days(Duration.From([closed]-[created]))
Regards,
- MarcelBeug
Community Champion
In general, the odd behavior - errors but empty error report - occurs when actual values do not correspond with column types.
I suspect your duration column is typed as duration, but you have normal number values.
If you want a duration from 2 datetime values, you can just subtract those values without any conversion (no Number.From or Duration.From or...).
Example:
= #datetime(2017,8,7,11,39,00) - #datetime(2017,8,3,21,32,43)
Returns:
3.14:06:17
- Chihiro
Thanks. Actually, it does for me as well. Up to that point.
Error message appears only when I load the table back into model or when refreshed. But strangest thing is that there aren't any errors found in the table itself (though error message states every row contains error).
Both [closed] and [created] are datetime value. Duration.TotalDays calculation returns duration in number (decimal) and formatted as such. I've also used various other construct (separating time value out, straight subtraction, Duration.From only etc), as well as changing column types to various types, but all resulted in above behaviour. Tested DAX variant and that worked without issue.
All attempt in Query Editor returned correct value in the sample and no error was returned until it's loaded to model. Error as stated, only seems to happen when data is put back into model or refreshed.
I'll find some other table that I have 2 datetime columns elsewhere in MySQL (never had this issue on one of my SQL server tables/views) and will do some testing later this week.
- MarcelBeug
Please read my post again. Don't use any duration function, just subtract the 2 datetime values and the result will be a duration.
