Duration.ToText producing some incorrect values

[Anonymous55]

I have a spreadsheet which contains data on all phone calls made to my company. There is a column for 'Invite time', which tracks the time the phone rang, and a column for 'Start time', which tracks the time that the phone call was answered. I would like to calculate the difference between these times in a third column called 'Wait time'.

I used the following formula in Power Query to create this column:

= Duration.ToText([Start time] – [Invite time])

It seemed to have worked well, though Power BI had randomly (?) tacked a string of numbers onto the end of each Wait time:

I changed the type of column to Time to remove the extra numbers. When I checked the values though, I noticed that some were not accurate - they seem to have been rounded up or down incorrectly. I've highlighted first few affected rows here:

Has anyone had any experience of this happening? It seems like it's happening randomly but perhaps someone has an explanation or, even better, a solution? 😄 

[Anonymous]

Hi @Anonymous55 ,

These are milliseconds and below:

It is also this data that causes your final data to be rounded.
If you want to ignore data at the millisecond level and below, you can try this code:

let
   Start_Time = [Start Time],
   Invite_Time = [Invite Time],
   Start_Duration = Duration.From(Start_Time),
   Invite_Duration = Duration.From(Invite_Time),
   Time_Diff = Start_Duration - Invite_Duration,
   Total_Seconds =
Duration.TotalSeconds(Time_Diff),
   Floored_Seconds =
Number.From(Number.Floor(Total_Seconds)),
   Result_Duration = #duration(0, 0, 0,
Floored_Seconds)
in
   Result_Duration

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jgeddes]

Is it possible your invite times and start times contain millisecond values that are not being shown in either column but persist into the duration calculation?
Consider this example.

would give a duration of 

[Anonymous55]

Thank you for reply! I've just gone back to my raw data to confirm, and no, there are no milliseconds linked to either column. 

[Anonymous55]

I decided to re-import the data from scratch to see if I could spot anything, and it worked first time! As far as I'm aware I did nothing differently 😄 Thank you for your help nevertheless!