cancel
Showing results for
Did you mean:
Solution Sage

## Duration calculation in query editor produces error message with no error row

Normally, I do my duration calcuation in DAX. But since I wanted to add custom bucket for duration, I attempted the calculation in Query Editor using M.

[closed] and [created] columns contain datetime value.

Used following when custom column is added.

`= Number.From([closed]) - Number.From([created])`

This produced correct result. But produced error message when query is refreshed. When view error option is chosen... result shows empty table.

Next tried below.

`=Duration.TotalDays(Duration.From([closed]-[created]))`

And also just Duration.From... This produced same result and error.

I then combined above calculation with custom bucket function below (with Duration.From).

```(myDur) =>
let closeBucket =
{
{(x)=>x<#duration(1,0,0,0), "<1day"},
{(x)=>x<#duration(2,0,0,1), "1-2days"},
{(x)=>x<#duration(3,0,0,1), "2-3days"},
{(x)=>x<#duration(5,0,0,1), "3-5days"},
{(x)=>x<#duration(7,0,0,1), "5-7days"},
{(x)=>x<#duration(14,0,0,1), "1-2wks"},
{(x)=>x<#duration(28,0,0,1), "2-4wks"},
{(x)=>true, ">1month"}
},
Result = List.First(List.Select(closeBucket, each _{0}(myDur))){1}
in
Result```

This again produced correct result, assigning each row into buckets. But then again, when refreshed, gave error message with no error in table. But also gave warning there's duplicate in id column. When checked, there is no duplicate produced...

I'm stumped. Does anyone have any idea how I should go about solving this?

1 ACCEPTED SOLUTION
Solution Sage

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,

8 REPLIES 8
Solution Sage

I 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

@Chihiro

Below expression works properly for me:

`=Duration.Days(Duration.From([closed]-[created]))`

Regards,

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

Specializing in Power Query Formula Language (M)
Solution Sage

@v-sihou-msft

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).

@MarcelBeug

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.

Community Champion

Please read my post again. Don't use any duration function, just subtract the 2 datetime values and the result will be a duration.

Specializing in Power Query Formula Language (M)
Solution Sage

Ahem, tried that already as stated in my post 😉

"...straight subtraction..."

Community Champion

Ah, sorry. Overlooked, but I don't understand: it works perfectly with me:

Specializing in Power Query Formula Language (M)
Solution Sage

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,

Announcements