Power query - how to support over 24 hours time

[mlcalves]

Hi people;

I have a table with hours:

When i tried to import this table from powerquery, i get error, even if if change the type to duration:

It puts the date like this:

= Table.TransformColumnTypes(#"Promoted Headers",{{"horas gastas", type duration}})

when i click on Error: it says:
DataFormat.Error: We couldn't convert to Duration.
Details:
31/12/1899 20:00:00


Thank you

Regards

 

[Matt Allington]

Try converting to date/time, then extracting the time.

 

[mlcalves]

Try converting to date/time, then extracting the time.

hi. thank you. but doesnt work. system converts hours to 01 -24h, when have for example: 40::00:00.

i extrated the time, and nex converted to duration, but doens't work. i think:

can anyone help, please?

Thank you

 

[mlcalves]

Hi. i discovered this solution:

Power query, in table, add custom column:

Duration in seconds = Duration.TotalSeconds([horas gastas] - #datetime(1899, 12, 31, 0, 0, 0))

 #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Duration in seconds", each Duration.TotalSeconds([horas gastas] - #datetime(1899, 12, 31, 0, 0, 0)))

Then, close & Apply

then go to report, add measure to convert seconds to time. found this on google.

Duration =
// Duration formatting
// * @konstatinos 1/25/2016
// * Given a number of seconds, returns a format of "hh:mm:ss"
//
// We start with a duration in number of seconds
VAR Duration = sum(Sheet1[Duration in seconds])
// There are 3,600 seconds in an hour
VAR Hours =
    INT ( Duration / 3600)
// There are 60 seconds in a minute
VAR Minutes =
    INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours
VAR Seconds =
    ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number
// These intermediate variables ensure that we have leading zero's concatenated onto single digits
// Hours with leading zeros
VAR H =
    IF ( LEN ( Hours ) = 1,
        CONCATENATE ( "0", Hours ),
        CONCATENATE ( "", Hours )
      )
// Minutes with leading zeros
VAR M =
    IF (
        LEN ( Minutes ) = 1,
        CONCATENATE ( "0", Minutes ),
        CONCATENATE ( "", Minutes )
    )
// Seconds with leading zeros
VAR S =
    IF (
        LEN ( Seconds ) = 1,
        CONCATENATE ( "0", Seconds ),
        CONCATENATE ( "", Seconds )
    )
// Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss"
RETURN
    CONCATENATE (
        H,
        CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )
    )