Value need to convert time in Q Editor

[Anonymous]

Hello Team,

Need help to convert hh:mm:ss format in query editor for cycle duration column (these values are considered as seconds), This column is in text format

[AiolosZhao · Accepted solution]

Hi @Anonymous ,

An easy way to solve that:

Duration.From([Orderid] / 86400)

Please try.

Aiolos Zhao

[amitchandak]

@Anonymous , Try like this

Duration(Number.IntegerDivide(Number.FromText([Cycle:duration]),86400) ,Number.IntegerDivide(Number.Mod(Number.FromText([Cycle:duration]),86400),3600),
Number.IntegerDivide(Number.Mod(Number.Mod(Number.FromText([Cycle:duration]),86400),3600),60),
Number.Mod(Number.Mod(Number.Mod(Number.FromText([Cycle:duration]),86400),3600),60)
)

[AllisonKennedy]

Sorry, I don't understand, have you already got the seconds and need HH:MM:SS or do you need to calculate the seconds?

If you have HH::MM:SS and need to get seconds, Split Column by delimiter :
Then rename columns to HourPart, MinutePart, SecondPart
Then do a calculated column:
[HourPart]*3600 + [MinutePart]*60 + [SecondPart] and call this calculated column Seconds

If you have Seconds and need HH:MM:SS, then use Number.Mod() and Number.IntegerDivide() to do the opposite

[HourPart]= Number.IntegerDivide{[Seconds], 3600)
[Seconds2]= Number.Mod([Seconds], 3600)
[MinutePart]= Number.IntegerDivide([Seconds2], 60)
SecondsPart = Number.Mod([Seconds2], 60)

Then merge columns with : as delimiter.

[Greg_Deckler]

@Anonymous Well, if it were DAX you could do it this way: https://community.powerbi.com/t5/Quick-Measures-Gallery/Seconds-2-HHMMSS/m-p/195615#M18

Should be able to follow a similar technique in Power Query though.