Forum Discussion

ovetteabejuela's avatar
ovetteabejuela
Impactful Individual
8 years ago
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 back to 85H 14m 16s.

 

Any ideas?

 

Hi MarcelBeug, have you done anything like this?

 

Thank you.

 

Need Help
  • ovetteabejuela's avatar
    ovetteabejuela
    8 years ago

    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!

  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    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:

     

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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")
  • ovetteabejuela's avatar
    ovetteabejuela
    Impactful Individual
    8 years ago

    Okay, I already have an idea, 1/1/1900 actually represents 24:00:00 so I can start from there.

    • MarcelBeug's avatar
      MarcelBeug
      Community Champion
      8 years ago

      Be careful: Excel and Power Query have different zero bases for date/times.

       

      In Excel, 0 represents 31/12/1899 0:00; in documentation also refered to as 1900/1/0 (zero January 1900).

      In Power Query, 0 represents 30/12/1899 0:00.

       

      Why?

      Because Excel regards 1900 as a leap year. so 2/29/1900 is day 60 according to Excel and 3/1/1900 is day 61.

      In Power Query this is corrected (1900 is not a leap year): all dates from 3/1/1900 (March 1) have the same number in Excel and Power Query, so counting back to zero takes you to 12/30/1899 0:00 as zero base in Power Query.

       

      If you import Excel 1/3/1900 1:14:16 PM as datetime in Power Query, it becomes 1/2/1900 1:14:16 PM.

       

      • ovetteabejuela's avatar
        ovetteabejuela
        Impactful Individual
        8 years ago

        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!