Addition of multiple Times formatted as HH:MM:SS

[AaronRogers3]

Hi,

I have 3 fields which have times stored in them as HH:MM:SS.

I would like to create a measure/column which gives me a total of these 3 times formatted as HH:MM:SS.

[RolandsP · Accepted solution]

Here is the M code I used in Power Query editor to do the calculations.

Basically, this is what I do:

1) convert Time to Seconds for all 3 fields

2) Add Seconds for all 3 fields

3) Convert back seconds to format "HH:MM:SS"

let
    Source = Excel.Workbook(File.Contents("C:\Users\rolan\Downloads\Data1.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Batch_Number", type text}, {"Time1", type time}, {"Time2", type time}, {"Time3", type time}}),
    #"Inserted Hour" = Table.AddColumn(#"Changed Type", "Hour", each Time.Hour([Time1]), Int64.Type),
    #"Inserted Minute" = Table.AddColumn(#"Inserted Hour", "Minute", each Time.Minute([Time1]), Int64.Type),
    #"Inserted Second" = Table.AddColumn(#"Inserted Minute", "Second", each Time.Second([Time1]), type number),
    #"Added Custom2" = Table.AddColumn(#"Inserted Second", "Seconds1", each [Hour]*3600+[Minute]*60+[Second]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Hour", "Minute", "Second"}),
    #"Inserted Hour1" = Table.AddColumn(#"Removed Columns", "Hour", each Time.Hour([Time2]), Int64.Type),
    #"Inserted Minute1" = Table.AddColumn(#"Inserted Hour1", "Minute", each Time.Minute([Time2]), Int64.Type),
    #"Inserted Second1" = Table.AddColumn(#"Inserted Minute1", "Second.1", each Time.Second([Time2]), type number),
    #"Added Custom3" = Table.AddColumn(#"Inserted Second1", "Seconds2", each [Hour]*3600+[Minute]*60+[Second.1]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom3",{"Hour", "Minute", "Second.1"}),
    #"Inserted Hour2" = Table.AddColumn(#"Removed Columns1", "Hour", each Time.Hour([Time3]), Int64.Type),
    #"Inserted Minute2" = Table.AddColumn(#"Inserted Hour2", "Minute", each Time.Minute([Time3]), Int64.Type),
    #"Inserted Second2" = Table.AddColumn(#"Inserted Minute2", "Second.2", each Time.Second([Time3]), type number),
    #"Added Custom4" = Table.AddColumn(#"Inserted Second2", "Seconds3", each [Hour]*3600+60*[Minute]+[Second.2]),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom4",{"Hour", "Minute", "Second.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns2",{{"Seconds1", type number}, {"Seconds2", type number}, {"Seconds3", type number}}),
    #"Inserted Sum" = Table.AddColumn(#"Changed Type2", "Addition", each List.Sum({[Seconds1], [Seconds2], [Seconds3]}), type number),
    #"Inserted Division" = Table.AddColumn(#"Inserted Sum", "Division", each [Addition] / 3600, type number),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Division",{{"Division", "HH"}}),
    #"Rounded Down" = Table.TransformColumns(#"Renamed Columns",{{"HH", Number.RoundDown, Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Rounded Down", "Custom", each ([Addition] - [HH]*3600 )/60),
    #"Rounded Down1" = Table.TransformColumns(#"Added Custom",{{"Custom", Number.RoundDown, Int64.Type}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Rounded Down1",{{"Custom", "MM"}, {"Addition", "Total Seconds"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns1", "Custom", each [Total Seconds] - [HH]*3600 - [MM] *60),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom1", {{"HH", type text}, {"MM", type text}, {"Custom", type text}}, "en-GB"),{"HH", "MM", "Custom"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged", type time}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Changed Type1",{{"Merged", "Total Time"}}),
    #"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns2",{"Seconds1", "Seconds2", "Seconds3", "Total Seconds"})
in
    #"Removed Columns3"

And here is the source data I used for this sample.

[AaronRogers3]

Hi @RolandsP,

wow thats alot!

How do I work with that then do I just copy and paste that somewhere?

Thanks

[v-piga-msft]

Hi @AaronRogers3,

If you want to use the solution of RolandsP, you could copy and past his M Query to your Advanced Editor in Query Editor, but you need to change the data source to your data source.

You also could have a reference of this thread which use DAX. You'd better convert your time to seconds first, then it will be more easier to calcualte.

If you still need help, you could create a dummy data similar with your data sample and your desired output, so that we can help further investigate on it?

Best  Regards,

Cherry

[Interkoubess]

Hi @AaronRogers3,

Did you add these 3 columns in a new one and change the format ( use function Format).

Let us know if it does not work, Please share sample data easy to manipulate and I can make a try.

Ninter.

[AaronRogers3]

Hi @Interkoubess,

Thanks for the reply, em no im quite a beginner when it comes to PowerBI.

What would i change the format of the fields too? thing is i need to keep them as HH:MM: SS

All of my data is in a database so i dont have samples sorry.

Thanks