Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Varela
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
nickneck
New Member

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" 

 

 

View solution in original post

6 REPLIES 6
eduardo
Helper I
Helper I

@Varela check out this video in portuguese

 

 

 

 

abs

eduardo

nickneck
New Member

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" 

 

 

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?

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.

 

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.