Forum Discussion

Pmorg73's avatar
Pmorg73
Post Patron
6 years ago
Solved

Time format export data from hours to decimal

  I have an interesting query   I am working in time and invoice management web site called workflow max. To get data from the web site I export reports in csv format. For small time entrie...
Need Help
  • Pmorg73's avatar
    Pmorg73
    6 years ago

    In query editor. Select the column and split it by deliminator

    You get two columns. 1 is hours, 1 is mins

    New column = hours + (mins/60) = decimal number and works with values over 24 hours. 

     

    KISS

Anonymous's avatar
Anonymous
Not applicable
6 years ago

You should use the Duration functions in M (Query Editor / Power Query)

 

Here is a link that desribes each of the fuctions.  Note that they all should accept a time value as input.

 

Duration.TotalMinutes( Duration.From( [Time Column] ) )  will convert the entire time duration into minutes.  2 hours 3 minutes = 123 minutes.

 

I'm not sure the exact output you're looking for, but I would do all of your modeling in Query Editor to have a single, consistently formatted column for Power BI to work with.

 

Hope this helps,

 

~ Chris

Pmorg73's avatar
Pmorg73
Post Patron
6 years ago

No link?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Pmorg73 , sorry about that!  Here's the link:

     

    https://docs.microsoft.com/en-us/powerquery-m/duration-functions

     

    What are you looking to calculate from that column?  Do you need an integer column with the total number of minutes?  A decimal column with the total number of hours (with the minutes portion making up the decimal amount)?

     

    Whatever you do, don't create a calculated column in DAX on top of what you're building in M...that will bloat the model.

     

    Let me know how you need the final column formatted, and sample calculations done with that column, and I'll help you write the M.

     

    Cheers,

     

    ~ Chris

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Pmorg73 , I reread your initial post.

       

      Try this:

       

      Duration.TotalHours( Duration.From( [Time Column] ) )

       

      That should make 1 hr 45 min return as 1.75  

      • Pmorg73's avatar
        Pmorg73
        Post Patron
        6 years ago

        I dont know where to put that string?

        crossing threads a bit but back to your earlier post.

         

        What I actually want is a column that has up until now been in decimal hours.  so 2.5 hours etc. 

         

        i think it would still work as hh:mm. But I am getting lost when I have done what you said. My column is now a duration in power query. When I close the editor the first entry is 11 hours and my table value is 0.45

        So that looks to be in a day format. How do I change this. Note I am using a roll back to Dec version as Febs is no good for csv imports