Power Query: Mind the Overlap
17 June 2020
Welcome to our Power Query blog. This week, I look at a method to spot overlapping periods.
My salespeople have recorded time spent with suppliers, but there seem to be some discrepancies!
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/185/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
Clearly, there is some overlap, and I intend to show this using Power Query. To begin, I extract my data to Power Query using ‘From Table’ in the ‘Get & Transform’ section of the Data tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/185/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
I will name the headings once I get into Power Query.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/185/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
I need to calculate the overlapping time for each salesperson. To do this, I create a new column based on the source so that I may compare rows.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/185/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
I access ‘Custom Column’ from the ‘Add Column’ tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/185/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
Next, I expand GetSource and just for once, I want to use the column name as a prefix in my new columns.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/185/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
I click OK to create my new columns.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/185/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
I now have a product of my columns, so instead of five rows I now have 25. Next week, I will look at a way of doing this where I don’t create all 25 rows. However, let’s continue for this week.
The columns GetSource.Column3 and GetSource.Column4 should be type DateTime, so I change the type on the Transform tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/185/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
I need to create a filtering step to pick those rows I need to make my comparison (i.e. those where the supplier is different so I may compare site times). To get the frame for my filter logic, I create a standard filter. I will then amend the M code.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/185/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
I choose to filter on ‘Amazing Awnings’ and click OK.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/185/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
The M code for this step is
= Table.SelectRows(#"Changed Type1", each ([Supplier] = "Amazing Awnings"))
I need to change this to
= Table.SelectRows(#"Changed Type1", each ([Supplier] <> [GetSource.Column1] and
[Salesperson] = [GetSource.Column3]))
This will result in rows where the supplier is different, but the salesperson is the same
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/185/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
I click on the tick icon to enter my changed step.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/185/image12.png/a1537847463e660a31158c8032525438.jpg)
I can see that John and Derek have some overlapping time on site.
In order to calculate the overlapping time on site, I will create functions to find the MinSiteTime and MaxSiteTime to apply to my ‘on site’ columns. I close and load my current query, and create a new blank query from the ‘New Query’ dropdown:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/185/image13.png/917da985be13220165c8d2823e95344f.jpg)
In the Advanced Editor of my new blank query, I add some M code:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/185/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
The M code I have added is
(date1, date2)=>
if date1 < date2 then date1 else date2
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/185/image15.png/e63c0a4c21afc9afb438aacc09a59317.jpg)
I save my function as MinSiteTime and create a new blank query.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/185/image16.png/d082e3477129350b8a2a589156028e63.jpg)
This time, the M code is:
(date1, date2)=>
if date1 >= date2 then date1 else date2
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/185/image17.png/a468c9c28195e8f18fdedfa59a0cf042.jpg)
I save MaxSiteTime.
Going back to my original query, I can now create a new custom column from the ‘Add Column’ tab. I call my new column Overlap.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/185/image18.png/61935f470d8b9f21b7b7683ffca40895.jpg)
The M code I have used is:
= Duration.TotalHours(
MinSiteTime([On site to], [GetSource.Column4]) -
MaxSiteTime([On site from], [GetSource.Column3]))
This formula will give me the number of hours overlapping: if the result is negative, then there is no overlap.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/185/image19.png/06909e3fbfc8faf87de7555c0b99e9d3.jpg)
As expected, both salespeople have overlapping time on supplier sites. I can add another supplier for John with no overlap to show that this will not give a positive value in the overlap column.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/185/image20.png/173ef895b62ce391674d271b99c4d178.jpg)
If I refresh my query, I can see the results:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/185/image21.png/bc31e9359fd69b42bc104de457d8f9f8.jpg)
The rows involving ‘New Supplier’ have a negative value in Overlap,
showing that there is no overlapping time on site. I can filter on Overlap to remove the negative values:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/185/image22.png/059b7a0f2b398df09f943b811a5b3126.jpg)
I choose values greater than zero (0).
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/185/image23.png/287595eacf557e9bf11ea5283a715ce1.jpg)
I can see the filter applied:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/185/image24.png/c20c20624705a0da5ec0bb162b062eab.jpg)
I only see those rows where there’s an overlap. If I wish, I can show the overlap in more detail by creating columns to show the beginning and end of the overlap. To do this, I add two custom columns and use my MaxSiteTime and MinSiteTime functions. I use the ‘Invoke Custom Function’ button on the ‘Add Column’ tab to do this.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/185/image25.png/d1f447eaaef4f12aad7eba0759ff10c1.jpg)
I take the On Site From and GetSource.Column3 (i.e. the linked On Site From), and find the later of the two.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/185/image26.png/c4be63c37c731d35ebe71055df24b261.jpg)
I create another column for the end of the overlap using a similar method.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/185/image27.png/a28c3d756a6785e68daad8f55186300c.jpg)
I take the On Site To and GetSource.Column4 (i.e. the linked On Site To), and find the earlier of the two.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/185/image28.png/b8e752cc441321a35196a6559f63ba46.jpg)
I can now remove the ‘GetSource’ columns to see my results:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/185/image29.png/d0df8c90b5a7daae74aba80275bf8ae6.jpg)
I can now see the overlapping site time in the records for my salespeople and can chase up to see what the recorded times should have been. Next time, I will look at a method of doing this where I don’t have to create so many rows during my calculations.
Come back next time for more ways to use Power Query!