Log In
Register
Home
July 3, 2022
Offline
Hi Everyone,
I'm looking for ideas to calculate the diference between the time values, at first in power query I calculated using the following by adding a cusotm coloumn.
Dhanil_0-1672921720572.png
The coloumn added in time format showed error, hence it was converted to whole numbers
Dhanil_2-1672921890586.png
Further the difference showing up in the matrix is not matching up to the actual difference
Dhanil_1-1672921813277.png
The time difference is not adding up, is there a measure or any menthod that can help me solve the issue ? - DATEDIFF is not showing up when I type in adding custom column
Trusted Members
Moderators
January 31, 2022
Offline
Hi,
you probably intended to upload some screenshots, but they didn't come through. Please try again by clicking "Attachments", select the pictures to upload and then "Start upload". Even better you can upload a file that includes your data and queries.
Riny
July 3, 2022
Offline
Hi Riny,
This is the images that I intended to attach - it explains the formula used in the custom coloumn , the result of the formula and the desired outcome.
Regards,
Dhanil Sunil 
Trusted Members
Moderators
January 31, 2022
Offline
To calculate time spent in Power Query, first select the End time column, press control and then the Start time column. On the Add Column tab press the Time button and choose Subtract. This will generate the appropriate code that calculates duration for you. It may look something like this:
Table.AddColumn(#"Previous Step", "Time spent", each [End] - [Start], type nullable duration)
Don't use the Duration.Minutes function as it only returns the minutes portion of a duration. So, it the total duration was 3 hours 10 minutes, it returns the number 10. And you should not set the data type to Time as that will return an error, as you have noticed.
With regard to DATEDIF, that function does not exist in Power Query (M-code).
July 3, 2022
Offline
Hi Riny,
Thank you for the solution, this has worked for 70% of the data - except for few.
In the image below the first two columns are the time spread and the last column is the time taken. As you can see 14: 38 to 14: 45 is showing 7 minutes.
Trusted Members
Moderators
January 31, 2022
Offline
Isn't that correct?
14:45 is 7 minutes after 14:38.
July 3, 2022
Offline
Hi Riny,
Pardon the typo, it's showing 0 minutes in the subtraction tab at the right end, not 7 minutes.
Trusted Members
Moderators
January 31, 2022
Offline
Not sure about what that columns represents, but I suspect that you are expressing the total number of minutes as a portion of a whole day, containing 1440 minutes.
3hrs, 19min = 199min / 1440 = 0.138894 = 0.14 rounded
2hrs, 18min = 138min / 1440 0.095833 = 0.10 rounded
Similarly, 7min / 1440 = 0.004861 = 0.00 rounded
1 Guest(s)
