Finding the Date of the Next Record in Power Query

Today I’m going to share my solution to a question I found on the MrExcel forum. It’s an interesting problem.

https://www.mrexcel.com/board/threads/power-query-to-determine-consecutive-and-non-consecutive-dates.1261496/

Let me briefly explain the problem.

The Problem

We have the following table, which contains the source data.  Each row contains a unique ID (maybe a piece of equipment) and failure dates; each failure date in a new column.

So, for ID 123, a failure occurred on the 17th of June and again on 19^th June. Because the dates are not consecutive (week days), they are treated as 2 events.  Then the event on 19^th June is followed by an event on 20^th, 21^st (Friday), 24^th (Monday).  All these dates were the same event.  Failure first occurred on the 19^th and the last failure was the 24^th.

The Required Output

The objective from the OP is to transform the table above into the below format.

The Solution

I have recorded the following video showing you how I solve this problem.  I’ve used many tricks that I have learnt over my years using and teaching Power Query.

This solution is very similar to a previous video I created here, if you are interested. https://www.youtube.com/watch?v=xN2IRXQ2CvI