Forum Discussion
Solved
PowerQuery | Duration more than 24 hours
As shown above the duration 85h 14m 16s is represented in the background as 1/3/1900 1:14:16 PM. PowerQuery captures the 1/3/1900 1:14:16 PM value, now I'm having trouble converting it...
Thanks for that note, I'll keep that in mind. Good thing I didn't convert it immediately I converted the data while it is in an unknow datatype state(text)
=(Date.Day([#"MyData"]) * 8640) + (Time.Hour([#"MyData"]) * 360) + (Time.Minute([#"MyData"]) * 60) + Time.Second([#"MyData"])
I basically converted to seconds!
Alternatively you can use:
= Duration.TotalSeconds([MyData] - #datetime(1899,12,31,0,0,0))
Notice that you will get wrong results from your solution for durations > 31 days, as the day number will become 1 for February 1, 1900. This is also the case when durations from Power Query are loaded into an Excel table.
You may be interested in this video:
DAX Mode ( In this mode you can agregate for any dimension)
QtSegundos = SUM([Seconds])
Time= VAR Horas = INT([QtSegundos]/3600) VAR Minutos = INT(([QtSegundos] - (Horas * 3600))/60) VAR Segundos = MOD([QtSegundos];60) RETURN Horas&":"&FORMAT(Minutos;"00")&":"&FORMAT(Segundos;"00")