Forum Discussion

Pmorg73's avatar
Pmorg73
Post Patron
7 years ago
Solved

Convert hour format data to decimal

Hi   I have seen advice on going the other way. But I have a large data set that exports the data in hours (Example below). I intend to sum this time so need to convert to decimal. In Excel this is...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Pmorg73 

     

    I see that the format for time taken as a long date and hence the formula gives different value.

     

    Going through your file, assuming the time is shown in longdate format then I tweeked the formula

     

    Time*24 = 'Table1'[[Time]] Time]*24 - 24
     
    By doing so I got the result the same as Time*24 done in excel and ported
    over.
     
     
    Cheers
     
    CheenuSing
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Pmorg73 

 

Can you please load sampledata , output expected  to Google / One Drive and share the link here.

 

Cheers

 

CheenuSing 

Pmorg73's avatar
Pmorg73
Post Patron
7 years ago

Excel sheet here with basic data. I thinned it down to two elements, with mutliple entries. I have made it anonimous so it should be good to go. My actaul data is several thousand entries from 2013 to now.

 

https://batchelarmcdougall-my.sharepoint.com/:x:/g/personal/phil_bmconsult_co_nz/Ee_FqH7f1zlDmU1FlN8pHm0BOoF2e-8vpRfNod7_qVrvbQ?e=q2l92X

 

Our database spits out the time column as hours. Then in Power BI it makes it a long date format (orignal image). In excel I would add a column and simply =Time *24 and get the decimal version. This is what I need to do in Power BI

 

I have seen people discussing going the other way around on here (ie decimal to time format)

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Pmorg73 

     

    Have you tried createing a calculated column

     

    DecimalHours = Table[Time] * 24

     

    replace Table by your tablename

     

    Cheers

     

    CheenuSing

    • Pmorg73's avatar
      Pmorg73
      Post Patron
      7 years ago

      Yeah i tried that before I posted the query on here, but it does not work correctly. The formatting of the data is the issue I think. That converts it in to completely wrong answers. (see image)

       

      I have uploaded a BI file with just the data inserted. I did add a column in the excel sheet before I brought it in of the Excel *24 answers that we would need to achieve. I then added a column and did the calc as suggested. See the image the numbers are not correct. I assume it is related to the format of the time entry when I bring it from excel to Power BI, as it is showing as a long date format, but in excel it is an hours format. (blue arrow in image)

       

      link to BI file

      https://batchelarmcdougall-my.sharepoint.com/:u:/g/personal/phil_bmconsult_co_nz/EZPLVTavypxPhdui_CjlEEcBxFGgO21wYUnR2vHfdnJFbw?e=uVABGQ

       

      Link to excel data

      https://batchelarmcdougall-my.sharepoint.com/:x:/g/personal/phil_bmconsult_co_nz/Ee_FqH7f1zlDmU1FlN8pHm0BOoF2e-8vpRfNod7_qVrvbQ?e=1aZFW7

       

       

      Any suggestions greatly appreciated

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Pmorg73 

         

        I see that the format for time taken as a long date and hence the formula gives different value.

         

        Going through your file, assuming the time is shown in longdate format then I tweeked the formula

         

        Time*24 = 'Table1'[[Time]] Time]*24 - 24
         
        By doing so I got the result the same as Time*24 done in excel and ported
        over.
         
         
        Cheers
         
        CheenuSing