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

Accepted Solutions
Chihiro Solution Sage
Solution Sage

Re: Duration 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,

View solution in original post

8 REPLIES 8
Chihiro Solution Sage
Solution Sage

Re: Duration calculation in query editor produces error message with no error row

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
Microsoft

Re: Duration calculation in query editor produces error message with no error row

@Chihiro

 

Below expression works properly for me:

 

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

 4.PNG

 

Regards,

Highlighted
MarcelBeug Community Champion
Community Champion

Re: Duration calculation in query editor produces error message with no error row

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)
Chihiro Solution Sage
Solution Sage

Re: Duration calculation in query editor produces error message with no error row

@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

Re: Duration calculation in query editor produces error message with no error row

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)
Chihiro Solution Sage
Solution Sage

Re: Duration calculation in query editor produces error message with no error row

Ahem, tried that already as stated in my post 😉

"...straight subtraction..."

MarcelBeug Community Champion
Community Champion

Re: Duration calculation in query editor produces error message with no error row

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)
Chihiro Solution Sage
Solution Sage

Re: Duration 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,

View solution in original post

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors