Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Duration time

  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 ta...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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's avatar
ChrisMendoza
Resident Rockstar
6 years ago

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's avatar
parry2k
Super User
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      parry2k
      Super User
      6 years ago

      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.