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