Forum Discussion

bbrauer100's avatar
bbrauer100
New Member
10 years ago

Time to Duration conversion throws an error.

I'm trying to convert time of day to duration in order to perform summary calculations.  However, when I try to convert time to duration in Query Editor the following error occurs:     DataFormat....
BAWard's avatar
BAWard
New Member
10 years ago


I have the exact same problem.  The column of input is formatted as date/time, and represents durations in hours:minutes. 

let
    Source = Excel.Workbook(File.Contents("C:\Users\Me\Documents\Test_Duration_Format.xlsx"), null, true),
    TestColumn_Table = Source{[Item="TestColumn",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(TestColumn_Table,{{"TestDuration", type duration}})
in
    #"Changed Type"

with the following input:

 

  • BAWard's avatar
    BAWard
    New Member
    10 years ago

    Forum didn't accept screen shot in my original reply, and would not let me edit it after it  was  submitted, so here is the sample input.  Hopefully it woks this time.

     

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      I have the exact same problem.

       

      Did you find anything that worked?

       

      Thank you

      J

      • wonga's avatar
        wonga
        Continued Contributor
        10 years ago

        BAWardbbrauer100Anonymous You need to ensure the format of the cells is "Text" first. This can be done either in Excel by highlighting the cells -> Format Cells -> Text, or in Power BI Query Editor by highlighting the column and going to the ribbon -> Transform -> Data Type -> Text. Then you can add a "calculated column" in Power BI Query Editor using the following:

         

        CustomColumn = Duration.FromText([Duration])

         

        You can then extract your desired duration from that new custom column such as: total minutes, total hours, total seconds, etc.

         

        In my experience, I've found the easiest way to use duration is by subtracting an end time from a start time and then converting it to the desired duration. Hope this helps, thanks.