Power Query to Determine Consecutive and Non-Consecutive Dates

[legalhustler]

Hello Everyone,

I'm working with a column called "DateCol Transposed," which has nested table values. The table values contains dates which a process failed for each unique ID (see image below). Note the ID column is not shown in the image.

So as an example, if I expand the "DateCol Transposed" column the table will look like below (I disregard the other columns for now):

ID	Column1	Column2	Column3	Column4	Column5	Column6
123	6/17/2024	6/19/2024	6/20/2024	6/21/2024	6/24/2024	6/26/2024
999	6/17/2024
888	6/18/2024	6/19/2024
777	6/10/2024	6/12/2024	6/13/2024

I need to transform the table above (or the nested table values if I do not expand it) to look like the table below using the following rules:

For non-consecutive/single dates (those with no succeeding date), the correction date is the same as the failed date. For consecutive dates (those with succeeding dates), only the first and last dates should be picked. The duration is the difference between the Correction Date and Failed Date.

ID	Failed Date	Correction Date	Duration (Excluding weekends)
123	6/17/2024	6/17/2024	1
123	6/19/2024	6/24/2024	4
123	6/26/2024	6/26/2024	1
999	6/17/2024	6/17/2024	1
888	6/18/2024	6/19/2024	2
777	6/10/2024	6/10/2024	1
777	6/12/2024	6/13/2024	2

I cross posted on another forum but didn't get any replies. You can disregard it because the example had the wrong info.

Excel Forum

Determine Duration for Consecutive and Non-Consecutive Dates

www.myonlinetraininghub.com

Any help is appreciated. Thanks!

 

[Matt Allington]

What I have done works. There are 2 issues.
1. The first set of data was pivoted but this is not necessary. The sample file you then shared with me without the pivoted data was not the same solution as I previously provided.
2. The second set of data didn't match the first set of data.

I have done it again using your second set of data. Same file

 

[legalhustler]

What I have done works. There are 2 issues.
1. The first set of data was pivoted but this is not necessary. The sample file you then shared with me without the pivoted data was not the same solution as I previously provided.
2. The second set of data didn't match the first set of data.

I have done it again using your second set of data. Same file

Looks great! Will take a look at your code when I’m in front of my computer. I really appreciate your patience with me. Thank you again

 

[Matt Allington]

Here is a new link https://www.dropbox.com/scl/fi/s9br...cel.xlsx?rlkey=c8nnsev838z5yx5ayy0fuqzft&dl=1

 

[legalhustler]

Hi Matt,

I ran into an issue with your solution. Take a look below. ID 777 failed on 6/10, 6/11, and 7/15 (left table) but the output (right table) shows Failed Date 6/10 and Correction Date 7/15. Correction Date should be 6/11 then afterwards a new row should show 7/15 Failed Date and 7/15 Correction Date.

Seems like there is an issue if the ID fails again in different months. Please help.

 

[legalhustler]

FYI: 7/15 is a Monday

 

[Matt Allington]

OK. The logic was a bit hard for me to get my head around late in the day. I had to sleep on it. I think it is correct now, but you know your data better than me.

https://www.dropbox.com/scl/fi/vml0hindqesim2wmwys59/MrExcel2.xlsx?rlkey=uxxsyh3003elcwse08cf7sm4c&dl=1

This is also a good lesson for anyone reading this thread. It is very important to have a set of test data that tests a wide range of scenarios so that you flush out errors early in the process.

 

[legalhustler]

OK. The logic was a bit hard for me to get my head around late in the day. I had to sleep on it. I think it is correct now, but you know your data better than me.

https://www.dropbox.com/scl/fi/vml0hindqesim2wmwys59/MrExcel2.xlsx?rlkey=uxxsyh3003elcwse08cf7sm4c&dl=1

This is also a good lesson for anyone reading this thread. It is very important to have a set of test data that tests a wide range of scenarios so that you flush out errors early in the process.

Matt,

I was working on it and found another way but your solution is more elegant. I was using your Index trick to get the prior date and use it along with your next date column. Once I had those two dates it could also work with some creative nested IF logic formula. Nonetheless, I like your method with the Date.AddDays function. Thanks a bunch!