cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chihiro
Solution Sage
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

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,

View solution in original post

8 REPLIES 8
Chihiro
Solution Sage
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.

@Chihiro

 

Below expression works properly for me:

 

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

 4.PNG

 

Regards,

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)

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

MarcelBeug
Community Champion
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)

Ahem, tried that already as stated in my post 😉

"...straight subtraction..."

MarcelBeug
Community Champion
Community Champion

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

 

Closed minus created is duration.png

Specializing in Power Query Formula Language (M)

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,

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors