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 have uploaded the BI file with limited data in it.

 

The report has 2 tables and 2 queries. "06 cut" was a direct csv import. query1 was a sharepoint link where I deliberately had a value greater than 24 hours in it. This is where I think Power BI get confused.  data and BI file  

https://batchelarmcdougall-my.sharepoint.com/:f:/g/personal/phil_bmconsult_co_nz/ErYJ-1YpWJpMqS8V3oa1t1MB-J95-_kJTm1D3QNTNWy1CA?e=5v57ai

 

[edit] - I have decided to get rid of the two imports and do everything from sharepoint. i hope this will then result in a single combined table straight away and only one format applied to the column. I will update once I have tried this. (I am busy doing my day job at the same time)

Pmorg73's avatar
Pmorg73
Post Patron
6 years ago

to come back to this thread. 

 

For entries that are less than 24 hours 24:00 the following works. 

 

import the data as a combined sharepoint csv load. 

change column format to Date/Time

Make a new column = 24 * [time column]

 

you get a column of decimal values.

 

HOWEVER: If the entry is 24:00 exactly or greater it falls over and BI will not parse it.

In my data set I currently have 23 entries that are like this. I do not see a way for me to fix these 23 errors. Any help greatly appreciated

  • Pmorg73's avatar
    Pmorg73
    Post Patron
    6 years ago

    https://community.powerbi.com/t5/Desktop/Problems-with-hours-greater-than-24-hours/td-p/112680

     

    I think I need a combination of this thread, plus the good advice about using text.length.

     

    My problem boils down to (I think) that I have a column that can have up to ###:00 hours. I dont believe I have greater than 500 hours in any one cell.

    So I have a removed Colons column. Then text.length that column.

    From that I can work out how many digits are hours (I hope) to the left of the colon and the use the other thread solution. I will update how this goes.

  • Pmorg73's avatar
    Pmorg73
    Post Patron
    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