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

 

 

polymathy2017's avatar
polymathy2017
Advocate III
4 years ago

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 Text
2. Added a column with MCode using Duration.From
3. Added a column with MCode using Duration.FromText

all produced the same.