Duration conversion issues

[Anonymous]

Hi All, 

I faced a problem while generating a report and thought you can help. I have a report that includes 8 tables. The query is pulled from microsoft sql server. I have a column called Time elapsed - which is how long did it take the engineer to complete the ticket starting from the created time. 

When I pull the data through the query - it identifies the data as "Text" and when I change it to duration I get the below error:

Expression.Error: We couldn't parse the Duration literal.

Then I searched for a fix and I found out that i can use as an extra after I change the data type 

= Table.TransformColumns(PreviousStep,{{"ColumnName", each let parsed = Text.Split(_, ":") in #duration(0, 0, Int64.From(parsed{0}), Int8.From(parsed{1})), type duration}})

However, when this happens my data becomes in the form of 0.00:00:00 and when I go to my data model - this happens to my Time Elapsed Column (please see below)

[Anonymous]

In DAX, just take the 2.01947111 and do this:

Elapsed=VAR dur= SUM(Table[Time Elapsed])VAR d=INT(dur)*24 VAR h=HOUR(dur)VAR m=MINUTE(dur) VAR s=SECOND(dur) VAR totalh = d+h VAR Result = totalh&":"&m&":"&s RETURN 
Result

--Nate

[Icey]

Hi @Anonymous ,

Please refer to these posts to get what you want:

Chelsie Eiden's Duration - Microsoft Power BI Community;

Solved: Seconds to Hours Minutes sum - Microsoft Power BI Community;

Solved: How to get the total time in HH:MM:SS - Microsoft Power BI Community;

Solved: Calculate Working Hours per Week - Microsoft Power BI Community.

In the last three posts, my replies are all about converting seconds into "HH:MM:SS".

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @edhans @Icey @mussaenda ,

Thank you for your suggested solutions however the issue isn't just about changing it to hours! 

I have added the time table - fixed the datatype and created the relation between the column from DB (0.00 00 00) and the timetable (00 00 00) - however, it still doesn't accept mathmatical operations! I would like to do avg min and max functions.

Please do let me know if you can help.

[mussaenda]

Hi @Anonymous,

so your expected output from you supplied screenshot is?

[edhans]

Your data isn't in a valid duration @Anonymous 

The data thread is over here for reference.

Duration hours must be 0-23. Anything over 23 must be converted todays. So my "New Duration" column does that. 77 hrs becomes 3 days and 5 hours. Now it will import into DAX like this:

So now you have the proper fractions.

This is the necessary M code to do this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZQ9jgIxDIWvgqZGwn/JJG73AttshSipV2LvL62TgcQODRJIaDQffn5+9vW6IRBlou28AV6ALwSEJ6gKYI++fh/30zfbr31Xzgq43c5PRuobgyUyqAKaYDBCaI+pXICejKikwTQRmVVE0z6YkiXWwazS3vz5uz+OOkmUSWEwbFBjeGpDhRS0WRHrB7Nj0gcMZWV0jHzCWEvgGP6AwaqUBmPfxtD0wJytbwwoVVeHPqjTBi3D653T4jW1kb+8bn9oA+WiOBgh6v2kydgbOTC8WwgU6cUkyJ3Jk7F0SciBDUey08bmQvSATBuHHPSBKk2vua45sHZLZCxsqMijH5G1n6rdFseQJdDljSSXmGubD+2RMQOKyswOHNrCTEECQ6V7MBjhUhdt5huHmVpA7QM868ge60CPlp9PY7gt1cjBwYQccLwHlDTZZ+4PHLtAPgcCsU5bbJ9r6AfDa7N+KOTAGCktUC+mpu6b+PmQrAwnfw+wm+S18ZqdZkByOeCy01KHFeMuGNNCWF0OYLlv9fAtMGYmTm2w7ja0REavTW1RoKmtpkWbHPfA1Wmb2+/b7R8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Request ID" = _t, #"Created Time" = _t, Priority = _t, #"Time Elapsed" = _t]),
    #"Added Custom" = 
        Table.AddColumn(
            Source, 
            "New Duration", 
            each
                let 
                    varHours =  Number.From(Text.Start([Time Elapsed], 2)),
                    varDays = Number.IntegerDivide(varHours, 24)
                in
            if varHours >= 24
            then Text.From(varDays) & "."
                & Text.PadStart(Text.From(varHours - varDays * 24), 2, "0")
                & ":" & Text.AfterDelimiter([Time Elapsed], ":", 0)
            else [Time Elapsed],
            Text.Type
        ),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"New Duration", type duration}})
in
    #"Changed Type"

It is really just a bit of text manipulation @Anonymous but that is why your conversions weren't working.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[Anonymous]

Hi @edhans 

Thank you a lot for the M code - but I had informed you earlier this isn't the problem to begin with. 

I was able to solve the issue with a simple code (found in my first post) and it was fixed to the same fraction type you are talking about and with no errors. However, I wasn't able to replicate this in the demo file I gave you because the forum wouldn't allow me to upload a file. Please see below the power query shot after I apply my M code.

However, my issue lies in the data model where it is formated to fractions! 

I want a solution to apply so it would be possible for me to perform Avg Time Lapsed for an engineer or a customer  .. the max and min time lapsed etc. 

Do you know how I can apply this? 

[edhans]

See if this is what you want @Anonymous . Again, this is not a bug in Power BI or a deficiency. The data model does not hold durations, it holds fractions. If you want to do averages, etc, just do an average of the number it returns, then you have to do some work to convert it to text that looks like days, minutes, hours, seconds. For example:

Avg Duration Formatted =
VAR varDuration = [Average Duration]
VAR varDays =
    INT( varDuration )
VAR varFractionInSeconds = ( varDuration - varDays ) * 86400
VAR varHours =
    INT( ( varDuration - varDays ) * 24 )
VAR varMinutes =
    INT( DIVIDE( varFractionInSeconds, 60 ) - varHours * 60 )
VAR varSeconds =
    ROUND( varFractionInSeconds - varMinutes * 60 - varHours * 3600, 0 )
RETURN
    FORMAT( varDays, "#" ) & "."
        & FORMAT( varHours, "00" ) & ":"
        & FORMAT( varMinutes, "00" ) & ":"
        & FORMAT( varSeconds, "00" )

It returns this data over the sample dataset provided:

 I did my calculation a different way, but you can also use the logic at a site like this to convert numbers to durations.

[edhans]

@Anonymous can you please let us know if this is resolved by marking one or more as the solution, or post back with some good info that clarifies where we need to help close the gap for you? Thanks.

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.

[edhans]

This is not an error @Anonymous - this is how it works. Power Query supports several data types that Power BI's Data Model does not, and one of them is Duration. It is converted to fractions of a day, which what you are seeing. So a duration of 12 hrs would be .5.

The .03 would be 43 min and 12 seconds.

[Anonymous]

Hi @edhans 

I know this is not an error - I fixed the error I got in power query using the mentioned fixed. But in the model I still get the results in the image. is there a workaround? 

I have durations longer than days - often weeks and months and I need to do an arithmetic operations on the column .. such as Avg, Max and Min. Can you help please? 

[edhans]

You are going to have to post some data and code. Those values are accurate. If you have this duration in Power Query, which is 1 day, 2hrs, 3min, and 4 seconds:

It will load into the DAX model like this:

To convert that back to time, you'd have to use a formula. That .08546 is a fraction of a day. Every 0.00001157 is a second, so .08546 is 7,387 seconds, which is 123.11 minutes (divided by 60, so 2hrs, 3min, and 11 sec. I am off 7 seconds due to rounding since I did this with a calculator. If you have durations longer than a day, they will be larger than 1 when the conversion happens. If they are coming in at fractions, something else is happening.

Share data @Anonymous via instructions below.

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.