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

    Thanks for the guidance. And I will work further with this idea.

     

    However when I close the query editor I get a column that is in a decimal looking format. Should I then do a DAX measure to format it in to hours etc?

  • 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