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
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.
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