cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Varela Frequent Visitor
Frequent Visitor

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?

1 ACCEPTED SOLUTION

Accepted Solutions
nickneck Frequent Visitor
Frequent Visitor

Re: duration

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:

foto.png

 

 

 

 

 

 

 

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 6
Super User
Super User

Re: duration

When you are converting to duration, you are doing that in Power Query?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


nickneck Frequent Visitor
Frequent Visitor

Re: duration

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:

foto.png

 

 

 

 

 

 

 

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" 

 

 

Varela Frequent Visitor
Frequent Visitor

Re: duration

since mine is in portuguese i had to manually do the steps..

 

Worked perfectly.

Varela Frequent Visitor
Frequent Visitor

Re: duration

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?

eduardo Regular Visitor
Regular Visitor

Re: duration

@Varela check out this video in portuguese

 

 

 

 

abs

eduardo

Highlighted
tiagotas Regular Visitor
Regular Visitor

Re: duration

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.