I have time stamp data I'm working with. I have data I get in a excel file that I'm uploading to Power BI that has Production Hours field. This field is the amount of time it took to do a task. I have it as hh:mm:ss in excel so ex) 7:40:00 or 0:20:00 means they worked for 7 hours and 40 mins or 20 min. When I bring this into Power BI it turns it into a time. So example becomes 7:40:00 AM and 12:20:00 AM
How can I turn this into a duration and not a 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.
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.
Hi Anonymous ,
How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?
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.