Forum Discussion
Time format export data from hours to decimal
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
Just as I am about to try this. What format should I leave the [duration] column in?
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?
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
[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)
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
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.