Forum Discussion

caseyatdrake's avatar
caseyatdrake
New Member
4 years ago
Solved

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

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

 

 

 

Need Help
Show and Tell
Tips and Tricks
  • tamerj1's avatar
    tamerj1
    4 years ago

    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"

5 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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"
    • caseyatdrake's avatar
      caseyatdrake
      New Member
      4 years ago

      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

    • caseyatdrake's avatar
      caseyatdrake
      New Member
      4 years ago

      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.