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

Pmorg73's avatar
Pmorg73
Post Patron
6 years ago

I had interogated my csv file. My issue is I have two different formats in my output. Once the output value is large than 24 hours it get confused. So my issue is how todeal with two different formats that are time based?

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

OK, so the basic problem is that you have a column with different formats that  you need to handle different ways. So, you need to identify which format each row is in so that you can later handle it differently. So, I would suggest adding these two columns to your Power Query:

 

ReplaceColons
=Text.Replace([Duration],":","")

NumberOfColons
=Text.Length([Duration]) - Text.Length([ReplaceColons])

 

Here Duration is your column with your Duration. Again, this is Power Query code. So, now you have your identifier. 

  • Pmorg73's avatar
    Pmorg73
    Post Patron
    6 years ago

    Just as I am about to try this. What format should I leave the [duration] column in? 

    • Pmorg73's avatar
      Pmorg73
      Post Patron
      6 years ago

      I deleted all of the queries and started again. When I import my csv file I get the column as Time format (clock face) AM/PM

       

      I have tracked through one known invoice task to see what may be going on.

       

      I have a Workflowmax invoice with a task that has a summed time of 15:18 (hh:mm). so 15 hours and 18 mins of timesheet entries summed up against an invoiced $ value. (Will be doing average hourly rate later etc)

       

      In the csv file the entry is ",13:18,"    Randomly looking at my csv they are all in that format as far as I can see

       

      Now when freshly imported to BI it thinks its a time of 3:18:00PM

       

      I would like this as a decimal hours. I know I have some invoices with task sums of up to 300 hours

       

      In Excel it was a fix of  (24 * TIME = decimal number) but that was with it converted in to a table first.

       

      In my data silo version of this (using an excel table) I made a column to do this step but made the column a DATE/TIME format after advice from this forum. It has worked well for 6 months like this. But I am rebuilding using csv for sharepoint auto updating so going this problem again haha.

       

      [edit] And another thing. The direct csv import is in Time format as described, but the sharepoint csv link is in Text format.

       

      Thoughts?