Power Query Converting field with Time

[STIBBS_807]

I have a Excel report that has the Duration entered with the following format below.  When I pull the data into my power query to add up the time, Power Query changes the data to decimal point.  I just want the duration to appear in Power Query.  The only way I can see doing this is to create a table in excel not using Power Query to create a lookup.  Any ideas how I can do this using formula?

Duration		Duration		Would like
0:25		0.017361		25
0:25		0.017361		25
1:00		0.041667		60
0:45		0.03125		45
0:20		0.013889		20
0:10		0.006944		10
0:30		0.020833		30
1:00		0.041667		60
8:00		0.333333		480
1:00		0.041667		60
0:30		0.020833		30
0:15		0.010417		15
1:00		0.041667		30
0:30		0.020833		30
1:30		0.0625		90
1:00		0.041667		60
1:00		0.041667		60
0:15		0.010417		15
0:40		0.027778		40

[Fowmy · Accepted solution]

@STIBBS_807 

If you need to get the seconds from decimal then add the following custom column:

Number.Round ( [Duration] * 1440  , 0 )

If you need to convert it to duration then you can change the type to decimal and add the following column

then change type to Duration

Duration.From([your decimal])

[jennratten]

Hello!  Here you go.  If the Duration column should is of type duration (changing the type to duration should should make the column appear as in the screensnip below with hour, min and sec) then you can use Duration.TotalSeconds([Duration]) function.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjCwAiIjUyUdJSARq4NLxBAoCBQxM0BSYwJSY4KiC6TGCFmNIUjEEFnEGCRibIDHZAuIiIkFPkWYBgEtAznIEMPRxvitB4tY4rMLm+3odgGDA+xooJpYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Duration = _t, #"Would like" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Duration", type duration}, {"Would like", Int64.Type}}),
    #"Inserted Total Seconds" = Table.AddColumn(#"Changed Type", "Total Seconds", each Duration.TotalSeconds([Duration]), type number)
in
    #"Inserted Total Seconds"