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.Error: We couldn't convert to Duration. Details: 1:11:55 AMSeems pretty straight forward, so what am I missing?

Converted to text a few times in the PQ editor. Didn't work in any scenario. Tried each of the following and all results in Duration errors:1. Duplicated values column, changed format to Text2. Added a column with MCode using Duration.From3. Added a column with MCode using Duration.FromTextall produced the same.

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.

Hey Anonymous, See if you can access this Google Drive documents: https://drive.google.com/open?id=0B-bZzUsHzLUnZ3ZUY05PQ1gwVmM I made an edit to the queries, made a mistake where it's not getting the total minutes or hours and only extracting the minutes or hours part of the overall duration. Look at page 2 of the PBIX file and see if it's what you are looking for. Thanks.

Can you post sample data and your formula? Basically impossible to give you any meaningful feedback otherwise.

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:

Time to Duration conversion throws an error.

15 Replies

Greg_Deckler
Community Champion
10 years ago
Can you post sample data and your formula? Basically impossible to give you any meaningful feedback otherwise.
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
  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
    Not applicable
    10 years ago
    I have the exact same problem.
    
    Did you find anything that worked?
    
    Thank you
    J

Forum Discussion

Time to Duration conversion throws an error.

15 Replies

Related content

Duration conversion issues

Duration Conversion

decimal to duration conversion

Duration Conversion and Calculation

FX Conversion Logic