How to sum text format (hh:mm:mm) ?

[Hello_World]

Hey,

i am new in Power BI  and have following problem.  I have a duration of 120:00:00 hours (text format) and want to sum it with similar values.

example: 120:00:00 + 34:00:12 = 154:00:12. But it doesen't work because it's text format i think. But i cant change the format and don't know how to do it. I tried to change the formar with: Format( 'table'[column], "hh:mm:ss")

I hope my problem is clear.

The column 3 in my picture shall be generated automatically.

Can you tell me how to do it?

Thanks a lot!!

[gooranga1]

Hi @Hello_World

It's a bit convoluted but this worked.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HYwxDgAwCAL/4txBsQ7lK03//42iCcPlINxrAad3bFnuxoC9pSJZR5H34GS8CMkN+ahGTcY7AUb1vk/m530=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Column1Seconds", each Number.FromText(Text.End([Column1],2)) + Number.FromText(Text.Middle([Column1],Text.PositionOf([Column1],":")+1,2))*60
+ Number.FromText(Text.Start([Column1],Text.PositionOf([Column1],":"))) * 3600),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Column2Seconds", each Number.FromText(Text.End([Column2],2)) + Number.FromText(Text.Middle([Column2],Text.PositionOf([Column2],":")+1,2))*60
+ Number.FromText(Text.Start([Column2],Text.PositionOf([Column2],":"))) * 3600),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "TotalSeconds", each [Column1Seconds]+[Column2Seconds]),
    #"Added Custom4" = Table.AddColumn(#"Added Custom2", "HoursTotal", each Number.IntegerDivide([TotalSeconds],3600)),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "RemainderMinutes", each [TotalSeconds]-Number.IntegerDivide([TotalSeconds],3600)*3600),
    #"Added Custom6" = Table.AddColumn(#"Added Custom5", "MinutesTotal", each Number.IntegerDivide([RemainderMinutes],60)),
    #"Added Custom7" = Table.AddColumn(#"Added Custom6", "RemainderSeconds", each [TotalSeconds]-Number.IntegerDivide([TotalSeconds],3600)*3600
-[MinutesTotal]*60),
    #"Added Custom3" = Table.AddColumn(#"Added Custom7", "TotalTimeDisplay", each Text.End("0" & Text.From([HoursTotal]),if Text.Length(Text.From([HoursTotal]))<2 then 2 else -1 + Text.Length("0" & Text.From([HoursTotal]))) & ":" &
Text.End("0" & Text.From([MinutesTotal]),2) & ":" &
Text.End("0" & Text.From([RemainderSeconds]),2))
in
    #"Added Custom3"

[Hello_World]

Thank you both!!!!!
I will try it tomorrow because im not at work anymore.

[BobN]

You will want to do your work in query editor for this. The issue is that your durations are over 24 hours. You will need to change the format to  Days (dot) hh:mm:ss instead of simply hh:mm:ss

First click Edit Queries

Next change your top row to headers

Next add custom column to split hours off the string. column by example will work to get the text before delimitor

Next convert that column to number

Next create custom column and use Divide Integer for the number of days. the formula for the custom column will be similar to this depending on the column name used in the prior step. I called mine "Duration 1 Hours"

= Number.IntegerDivide([Duration 1 Hours], 24,0)

create another custion column for the hours portion

= Number.Mod([Duration 1 Hours],24)

Now break out the mm:ss with Text After Delimiter custon column :

Now use Text.Combine in a new custom column to get the text version of the duration

= Text.Combine({[Duration 1 Days],".",[Duration 1 Partial Hours],":",[Duration 1 Remainder]})

This can be converted to duration now.

Convert Duration 2 in a similar fashion and you can add them together.

Full M script from my solution below. You can view your M script by clicking on Advanced Editor.

let
    Source = Csv.Document(File.Contents("\\spgntap2\COMMON1\BI\Support\Lease Management\Power BI\duration.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Duration 1 Hours" = Table.AddColumn(#"Promoted Headers", "Duration 1 Hours", each Text.BeforeDelimiter([Duration 1], ":"), type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Duration 1 Hours",{{"Duration 1 Hours", Int64.Type}}),
    #"Duration 1 Days" = Table.AddColumn(#"Changed Type1", "Duration 1 Days", each Number.IntegerDivide([Duration 1 Hours], 24,0)),
    #"Changed Type2" = Table.TransformColumnTypes(#"Duration 1 Days",{{"Duration 1 Days", type text}}),
    #"Duration 1 Partial Hours" = Table.AddColumn(#"Changed Type2", "Duration 1 Partial Hours", each Number.Mod([Duration 1 Hours],24)),
    #"Changed Type3" = Table.TransformColumnTypes(#"Duration 1 Partial Hours",{{"Duration 1 Partial Hours", type text}}),
    #"Duration 1 Remainder" = Table.AddColumn(#"Changed Type3", "Duration 1 Remainder", each Text.AfterDelimiter([Duration 1], ":"), type text),
    #"Added Custom" = Table.AddColumn(#"Duration 1 Remainder", "Duration 1 Final", each Text.Combine({[Duration 1 Days],".",[Duration 1 Partial Hours],":",[Duration 1 Remainder]})),
    #"Changed Type4" = Table.TransformColumnTypes(#"Added Custom",{{"Duration 1 Final", type duration}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type4",{"Duration 1", "Duration 1 Hours", "Duration 1 Days", "Duration 1 Partial Hours", "Duration 1 Remainder"}),
    #"Duration 2 Hours" = Table.AddColumn(#"Removed Columns", "Duration 2 Hours", each Text.BeforeDelimiter([Duration 2], ":"), type text),
    #"Changed Type5" = Table.TransformColumnTypes(#"Duration 2 Hours",{{"Duration 2 Hours", Int64.Type}}),
    #"Duration 2 Days" = Table.AddColumn(#"Changed Type5", "Duration 2 Days", each Number.IntegerDivide([Duration 2 Hours], 24,0)),
    #"Changed Type6" = Table.TransformColumnTypes(#"Duration 2 Days",{{"Duration 2 Days", type text}}),
    #"Duration 2 Partial Hours" = Table.AddColumn(#"Changed Type6", "Duration 2 Partial Hours", each Number.Mod([Duration 2 Hours],24)),
    #"Changed Type7" = Table.TransformColumnTypes(#"Duration 2 Partial Hours",{{"Duration 2 Partial Hours", type text}}),
    #"Duration 2 Remainder" = Table.AddColumn(#"Changed Type7", "Duration 2 Remainder", each Text.AfterDelimiter([Duration 2], ":"), type text),
    #"Added Custom1" = Table.AddColumn(#"Duration 2 Remainder", "Duration 2 Final", each Text.Combine({[Duration 2 Days], ".", [Duration 2 Partial Hours],":",[Duration 2 Remainder]})),
    #"Changed Type8" = Table.TransformColumnTypes(#"Added Custom1",{{"Duration 2 Final", type duration}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type8",{"Duration 2", "Duration 2 Hours", "Duration 2 Days", "Duration 2 Partial Hours", "Duration 2 Remainder"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns1", "Total Duration", each [Duration 1 Final]+[Duration 2 Final])
in
    #"Added Custom2"

[LivioLanzo]

In the Query Editor (Power Query), you have a whole set of functions for this scenario:

https://msdn.microsoft.com/en-us/query-bi/m/duration-functions

[Hello_World]

Okay thanks.

So i have to download Power Query for Excel an change the format there ?

Is it not possible to do it only with Power BI?

[LivioLanzo]

Power BI comes with Power Query.:

[Hello_World]

Ok i tried all Duration Functions and it's always error.

What do i wrong?

[LivioLanzo]

try like this

let
    Source = #table( type table [Column1 = Text.Type, Column2 = Text.Type], {{"120:00:00", "34:00:00"}} ),
 
    
    AddCol = Table.AddColumn( Source, "Column3", each 
                                                     let
                                                        S1 = List.Transform(Text.Split([Column1], ":"),Number.From),
                                                        S2 = List.Transform(Text.Split([Column2], ":"),Number.From)
                                                     in
                                                        //S2,
                                                        #duration(0, S1{0}, S1{1}, S1{2}) + #duration(0, S2{0}, S2{1}, S2{2}),
                                                 type duration
    )
    
in
    AddCol