Forum Discussion
duration
I have a csv which has a few duration columns as native text.
when I convert to duration it works in most cases but the one that have more than 24 hours like "47:30:56"
I have this as text only and can't calculate it using start date and end date, because this duration is calculated with lots of things in consideration, business hours, SLA, etc.
Can someone help?
It is because the right duration format should be "1.27:30:56" (note that the seperator for the day is ".")
You have have to manually bring your data in the right format (d.h:m:s) to convert it to duration.For example:
If you have a soruce table like this:
The code could look similar to this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrIyNrAyMVOK1YlWMjSyMjSxMoBwjIDCplaG5mCOibkVSBLIiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Duration = _t]), #"Split Column by Delimiter" = Table.SplitColumn(Source,"Duration",Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv),{"Duration.1", "Duration.2", "Duration.3"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Duration.1", Int64.Type}, {"Duration.2", Int64.Type}, {"Duration.3", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Days", each Number.RoundDown([Duration.1]/24)), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Hours", each [Duration.1]-[Days]*24), #"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"Duration.1", "Days", "Hours", "Duration.2", "Duration.3"}), #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Days", type text}, {"Hours", type text}, {"Duration.2", type text}, {"Duration.3", type text}}), #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Duration Format", each [Days]&"."&[Hours]&":"&[Duration.2]&":"&[Duration.3]), #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom2",{{"Duration Format", type duration}}) in #"Changed Type2"
6 Replies
It is because the right duration format should be "1.27:30:56" (note that the seperator for the day is ".")
You have have to manually bring your data in the right format (d.h:m:s) to convert it to duration.For example:
If you have a soruce table like this:
The code could look similar to this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrIyNrAyMVOK1YlWMjSyMjSxMoBwjIDCplaG5mCOibkVSBLIiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Duration = _t]), #"Split Column by Delimiter" = Table.SplitColumn(Source,"Duration",Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv),{"Duration.1", "Duration.2", "Duration.3"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Duration.1", Int64.Type}, {"Duration.2", Int64.Type}, {"Duration.3", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Days", each Number.RoundDown([Duration.1]/24)), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Hours", each [Duration.1]-[Days]*24), #"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"Duration.1", "Days", "Hours", "Duration.2", "Duration.3"}), #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Days", type text}, {"Hours", type text}, {"Duration.2", type text}, {"Duration.3", type text}}), #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Duration Format", each [Days]&"."&[Hours]&":"&[Duration.2]&":"&[Duration.3]), #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom2",{{"Duration Format", type duration}}) in #"Changed Type2"since mine is in portuguese i had to manually do the steps..
Worked perfectly.
Varela, como consegui fazer?
Estou com o mesmo problema...
Tenho uma tabela com 48:00 horas, e quando levo como duração para o Power BI ele dá erro.
E preciso somar os valores.
In the report when I do average it shows 0.1 (days.hours), so most of the report shows 0.0.
is there a work around?
When you are converting to duration, you are doing that in Power Query?
