Forum Discussion
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.
Anonymous -
Remove the Applied Step(s) in Power Query that changed the Data Type to 'Time'? It will be a Text value though so I am unsure if that is your desired result.
I just tried to do this and this changed it to 12/31/1899 7:40:00 AM. The desired outcome would be to only have 7:40:00
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.Anonymous convert your time column data type to duration which you can do by clicking the data type on column header. Form here you can add custom column for total hours, total minutes or Hours and Mintures. basically all duration functions available in power query
So Excel already converted it to 'Time'; I'm guessing the formula bar shows something like 7:40:00 AM already but with custom formatting it displays as 7:40:00 in the cell? Do you have the flexibilty to do the calculation within Power Query rather than in your Excel file for duration? Another option is just to remove the date, delimit by space, then convert to Text the visual representation of duration.
Anonymous change data type to duration in power query in Power BI and rest calculations will be super easy. No need to do that in Excel.
ah ok it worked now when I changed to duration ! Thanks ! Another question for items that were less then a hour so 0:20:00 are displaying as 12:20:00 with the duration conversion. Is there a DAX formula to change the 12 to 0 ?
