Time into duration - text not working, excel/PBI both issuing errors

[caseyatdrake]

Hey I'm working on a fun project around half marathon races. Problem is my race data is showing at time of day rather than duration. I've tried converting to text, multiplying out into total minutes, and such within both Bi and excel but neither is working. there are 4 columns split 1, 2, 3, and total time but all are throwing error and messing everyhing up - any help or thoughts on what i can do? 

Thanks!

CC

[tamerj1 · Accepted solution]

Hi @caseyatdrake 
Here is a sample file with the solution https://www.dropbox.com/t/gyhXd49lugDwVccw

let
    Source = Excel.Workbook(File.Contents("C:\XXX\XXX\XXX\XXX\XXX\Community 324.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",{{"Split 1", type datetime}, {"Split 2", type datetime}, {"Split 3", type datetime}, {"Overall Time", type datetime}}),
    #"Added Split1" = Table.AddColumn(#"Changed Type", "Split1", each Time.Second([Split 1]) + Time.Minute([Split 1]) * 60 +  Time.Hour([Split 1]) * 3600 ),
    #"Added Split2" = Table.AddColumn(#"Added Split1", "Split2", each Time.Second([Split 2]) + Time.Minute([Split 2]) * 60 +  Time.Hour([Split 2]) * 3600 ),
    #"Added Split3" = Table.AddColumn(#"Added Split2", "Split3", each Time.Second([Split 3]) + Time.Minute([Split 3]) * 60 +  Time.Hour([Split 3]) * 3600 ),
    #"Added Total" = Table.AddColumn(#"Added Split3", "Total Time", each Time.Second([Overall Time]) + Time.Minute([Overall Time]) * 60 +  Time.Hour([Overall Time]) * 3600 ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Total",{"Split 1", "Split 2", "Split 3", "Overall Time"})
in
    #"Removed Columns"

[tamerj1]

Hi @caseyatdrake 

please provide an example of what should be the expected result. 

[caseyatdrake]

When I change to text (i can somehow send the file) it removes the seconds. I've tried to extract or split the column but it always removes the seconds. The other issue with extracting is that due to the fact some of the race times are 49 minutes and 33 seconds and some are over 1 hour 12 minutes and 33 seconds the time that is showing up makes it impossible to extract. 

[tamerj1]

@caseyatdrake 

Please share a sample file

[caseyatdrake]

I'd accept either of: 

Split 1                     Split 2               Split 3           Overall Time

15:29                      30:33                 49:12             1:04:26           Essentially removing the "12" and "AM" on the row one example from above. 

929                         1833                  2952              3866          = this would be converting everything to seconds so I could perform various data measurements etc. 

Thanks!

CC