Duration of an event

[Anonymous]

Hi, I receive data in excel with a duration field in hh:mm:ss:tt (tt-milli second) format. This is not the time but the duration it took to complete an event. This will value never be over couple of hours.

Sample data:

Event id      Duration

=====      ======

123              25:13:14

342              16:19:00

234              41:09:00

344           1:34:45:00

When I load this data into Power BI, it is converting it into date format for ex. 1/1/1900 1:13:14 AM. I tried to convert the data to various formats in excel and also in power BI, but I was not able to store this as a duration. Anyone who has an idea on how to store this as a duration(and not as a date and/or time) in excel or Power BI, please let me know.

Thanks,

SN

[Anonymous · Accepted solution]

@Anonymous , try this M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PcixEcAgDAPAXVxTWJbIXbSKj/3XCFDky+8OFGNETYOGYo0OqnbhMV5n3ipql+D8izoFU9a8uT4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Event id" = _t, Duration = _t])
    ,Transform_Duration = Table.TransformColumns(Source
        ,{{"Duration", each 
            if List.Count(Text.Split(_,":")) - 1 = 3
                then _
            else "0:" & _, type text}})
    ,Split_Duration = Table.SplitColumn(Transform_Duration, "Duration", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Hour", "Minute", "Second", "Millisecond"})
    ,Type = Table.TransformColumnTypes(Split_Duration
        ,{{"Event id", type text}
        ,{"Hour", Int64.Type}
        ,{"Minute", Int64.Type}
        ,{"Second", Int64.Type}
        ,{"Millisecond", Int64.Type}})
    ,Add_Duration = Table.AddColumn(Type, "Duration", each 
        #duration(
            0 // 0 days
            ,[Hour]
            ,[Minute]
            ,([Second] + ([Millisecond] / 1000) )
        ), type duration)
    ,Keep = Table.SelectColumns(Add_Duration
        ,{"Event id"
        ,"Duration"})
in
    Keep

Transform_Duration essentually looks to see if there are 2 or 3 colons ":".

If there are 3, then that means there are 4 numbers (hh:mm:ss:tt).  nothing needs to happen to this value.

If there are 2, that means there are only 3 numbers (mm:ss:tt).  We need to add 0 hours to the beginning of the text.

Now we can split it into 4 separate columns safely, knowing that we have 0 hours when necessary.

A quick rebuild into an actual duration (adding the seconds and milliseconds together).

Keep the 2 necessary columns and you're good to go.

[Moya_Novaes]

I sugest you altering the data type before you import the table into power bi, throught Query Selector. There you could change the type to text which it may not alter the visual of the duration.

Another alternative is to replace the datatype into the excel sheet itself. If you would be making operations such as average of durations, I sugest you converting the duration to the smaller unity (which in your case is seconds). After a correct import into powerbi, after any calculation you should call a function to convert an integer to the string format you like