convert general number to days hours minutes and seconds

[vat2do]

Greetings to all,

After downloading service now report in excel i receive a random number for the column "business elapsed time" which should be in format of days-hours-minutes-seconds.

Is there any way in power bi to convert this random number to days-hours-minutes-seconds format. Thanks in advaced

[danextian]

Hi @vat2do 

What is that elapsed time? Are they in minutes, seconds, hours?

[danextian · Accepted solution]

Assuming that those numbers are in seconds, try this custom column in the query editor

Duration.From(0) + #duration(0, 0, 0, [Seconds])

Full sample M Code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjM2MjJRitUBsgyMjUwhLCMLC2Mwy9IACJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Seconds = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Seconds", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Days", each let 
SecondsInADay = 60 * 60 * 24
in [Seconds]/ SecondsInADay, type number),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Duration", each Duration.From(0) + #duration(0, 0, 0, [Seconds]), type duration)
in
    #"Added Custom1"

Please note that although the data type is duration, it will be loaded as number into the model so the two custom column in the query editor will look the same in the designer.

You can create a calculated column to format it but please note that the result cannot be summed as it will be a text string.

Duration Time format = 
VAR DayOnly =
    INT ( 'Table'[Duration] )
VAR TimeOnly = 'Table'[Duration] - DayOnly
RETURN
    DayOnly & "."
        & FORMAT ( TimeOnly, "HH:MM:SS" )

[vat2do]

Thanks @danextian for asking,

Yes the elapsed time is in "Days Hours Minutes Seconds" format

[danextian]

I did reply to my initial response and assumed that those numbers are in seconds.