Power Query: Timings
30 December 2020
Welcome to our Power Query blog. This week, I look at an example where I need to sum time.
My imaginary salespeople have been having problems with a supplier. This has taken a lot of time and effort by several salespeople, and I need to work out the cost to the company. I need to know what days they worked, and for how long. I also need to know the total time each salesperson has spent on this supplier.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/213/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
I start by uploading my data to Power Query by using ‘From Table’ on the ‘Get & Transform’ section of the Data tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/213/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
I don’t want to take the whole default area, I want to start from cell A2, so I adjust the range.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/213/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
I start by filling the salesperson name, by selecting salesperson, right-clicking and selecting Fill and then Down.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/213/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
I want to add a new column, so I go to the ‘Add Column’ tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/213/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
If I select both time columns, I have the option of creating a column which is the difference between my columns, using ‘Subtract’ on the ‘Time’ dropdown. I must select End Time before Start Time (keeping the CTRL key pressed down), to get positive results.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/213/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
I can now see how much time each salesperson spent on the supplier in each interaction, but I want to view it more clearly, as a number of hours (which I can then subtotal). I am going to add a duration column.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/213/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
I choose ‘Total Hours’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/213/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
This is clearer, but there are too many decimal places. I am only interested in two (2), so I transform the column using the Transform tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/213/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
I choose to round my data.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/213/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
I can specify the number of decimal places.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/213/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
This is now much easier to work with. I can also see when these interactions are taking place by adding a column from the ‘Add Column’ tab, which tells me what day of the week they started.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/213/image12.png/a1537847463e660a31158c8032525438.jpg)
This will help me to see if any overtime has been involved.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/213/image13.png/917da985be13220165c8d2823e95344f.jpg)
I can now remove the columns I don’t need by right-clicking on them and choosing Remove.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/213/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
Once I have done this, I rearrange the columns to show the time each salesperson spent on each interaction. I ‘Close & Load’ my data to the Excel worksheet.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/213/image15.png/e63c0a4c21afc9afb438aacc09a59317.jpg)
I place it next to the first table.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/213/image16.png/d082e3477129350b8a2a589156028e63.jpg)
I’d also like to see the total time spent per salesperson on this sheet, so I take a copy of my query.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/213/image17.png/a468c9c28195e8f18fdedfa59a0cf042.jpg)
I am going to use a reference query, because my new query extends the work I have done to get the first table, and I want any further changes to my first query to change this query too. For more on reference queries, please see Power Query: Reliable References.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/213/image18.png/61935f470d8b9f21b7b7683ffca40895.jpg)
I can now use ‘Group By’ on the Transform tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/213/image19.png/06909e3fbfc8faf87de7555c0b99e9d3.jpg)
I choose to sum the Total Hours column by salesperson.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/213/image20.png/173ef895b62ce391674d271b99c4d178.jpg)
I have my results, which I could show as hours and minutes; I can do this by changing the data type to duration on the Transform tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/213/image21.png/bc31e9359fd69b42bc104de457d8f9f8.jpg)
I can ‘Close & Load’ this to my worksheet.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/213/image22.png/059b7a0f2b398df09f943b811a5b3126.jpg)
Come back next time for more ways to use Power Query!