Time format changing to datetime

Hi MLax 

You can save the excel file as csv type and then import it to power bi, then the "Regular Hours" will become "Text" Type in power bi, then you need to transfer the data in power query, because in power bi, it not support the hour over 24 hours,  you need to transfer the time to seconds

You can put the following code to Advanced Editor in power query to refer to.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0tLQyNrEyMFCK1YlWMgKJmJlYGRvARIzBIhZWBsYwEROQiIk5TE0sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, #"Regular Hours" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", Int64.Type}, {"Regular Hours", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Hours", each Text.Start([Regular Hours],3)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Minutes", each Text.Middle([Regular Hours],4,2)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Seconds", each Int64.From(Text.End([Regular Hours],2))+Int64.From([Hours])*3600+Int64.From([Minutes])*60),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Hours", "Minutes"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Seconds", Int64.Type}})
in
    #"Changed Type1"

Output

 

And you can refer to the following link .

excel - Summing time fields over 24 hours in Power Query - Stack Overflow

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.