Duration time

Hi Anonymous ,

We can just add two steps to get the duration time if your excel is format as hh:mm:ss:

[Duration] - #datetime(1899,12,31,0,0,0)

Then just delete the origin column and rename the new column

The full version M Query is here:

let
    Source = Excel.Workbook(File.Contents("D:\Test\2019-10-08\Duration-time.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Custom", each [Duration] - #datetime(1899,12,31,0,0,0)),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type duration}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Duration"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Duration"}})
in
    #"Renamed Columns"

you can use time to show in the field, or keep the origin duration number (the total day)

BTW, pbix and excel as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.