Duration time

[Anonymous]

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 ?

[v-lid-msft]

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.

[ChrisMendoza]

@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.

[Anonymous]

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 

[v-lid-msft · Accepted solution]

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.

[ChrisMendoza]

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.

[parry2k]

@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.

[Anonymous]

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 ? 

---

---

[parry2k]

@Anonymous well if you use duration function to get minutes, it will give you 20 minutes, not sure if that is wht you are asking.

[parry2k]

@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