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!

 

[AlienSx]

Hello, @legalhustler . This function transforms your nested table with dates into the table with 3 columns (2 dates and duration) providing that dates in nested tables don't fall on Sat and Sun.

(tbl as table) as table => 
let
    dates = List.Buffer(Table.ToRows(tbl){0}), 
    next_wd = (d as date) as date => Date.AddDays(d, if Date.DayOfWeek(d, Day.Monday) = 4 then 3 else 1),
    collect = (i, s as list, c as list, next as date) => 
        [current_date = dates{i},
        okay = (next = current_date),
        next_c = if okay then c & {current_date} else {current_date},
        current_c = {{List.Min(c), List.Max(c), List.Count(c)}},
        next_s = if okay then s else s & current_c,
        next_step = 
            if i = List.Count(dates) 
            then s & current_c 
            else @collect(i + 1, next_s, next_c, next_wd(current_date))]
        [next_step],
    run = collect(1, {}, {dates{0}}, next_wd(dates{0})),
    z = #table(type table [Failed Date = date, Correction Date = date, Duration = Int64.Type], run)
 in
    z

Create blank query, put this code inside, name this function and apply it using Table.TransformColumns(your_table, {"DateCol Transposed", function_name}). Then expand this column.

 

[Matt Allington]

I also have a solution. I don’t know if it delivers the same or different results from what AlienSx posted. My approach is to use the UI to solve the problem. I delayed posting a reply because I created a video showing how I solved it, for learning purposes. Here is the workbook. Video will be posted in the next few days after editing. https://www.dropbox.com/scl/fi/s9br...cel.xlsx?rlkey=c8nnsev838z5yx5ayy0fuqzft&dl=1

 

[legalhustler]

I also have a solution. I don’t know if it delivers the same or different results from what AlienSx posted. My approach is to use the UI to solve the problem. I delayed posting a reply because I created a video showing how I solved it, for learning purposes. Here is the workbook. Video will be posted in the next few days after editing. https://www.dropbox.com/scl/fi/s9br...cel.xlsx?rlkey=c8nnsev838z5yx5ayy0fuqzft&dl=1

Really elegant and easy for me to understand. Will also check AlienSXpost.

It's almost there but one small issue. Third row below is incorrect. See my result post above.

Range dates 6/19 - 6/24 should be one instance and is considered consecutive business days because although 6/24 falls on a Monday it would still be succeeding from the previous fail weekday date of 6/21 (a Friday) and since the process did not fail on 6/25 then the process was resolved for that instance.

 

[Matt Allington]

Really elegant and easy for me to understand. Will also check AlienSXpost.

It's almost there but one small issue. Third row below is incorrect. See my result post above.

Range dates 6/19 - 6/24 should be one instance and is considered consecutive business days because although 6/24 falls on a Monday it would still be succeeding from the previous fail weekday date of 6/21 (a Friday) and since the process did not fail on 6/25 then the process was resolved for that instance.
View attachment 114050

I will post the video this morning. I did call this line out in the video; I assumed you had made a mistake as I wasn’t aware of the weekend point you raise. I’m not sure how to deal with that using my approach. I will think about it.

I don’t want to detract from the solution from AlienSX. Clearly a very good solution. The “issue” I have with the approach is AlienSX is using his/her coding skill to use Power Query in a way that the average business user will likely never be able to replicate. Great if you have that skill!

Video coming shortly.

 

[Matt Allington]

I have applied a fix. The original link above should still work. Just editing the fix in the video and then I will post that, too

 

[legalhustler]

I have applied a fix. The original link above should still work. Just editing the fix in the video and then I will post that, too

Thank you so much! It looks great. I added one more step by adding a custom column to calculate the Duration between correction and failed date (excluding weekends) using this formula:

List.Count(List.Select(List.Dates([Failed Date], Duration.Days([Correction Date]-[Failed Date]) + 1, #duration(1, 0, 0, 0)), each Date.DayOfWeek(_, Day.Monday) < 5))

I agree your code is much easier for me to follow and replicate as I'm no PQ guru, especially with M (I can probably slowly decipher AlienSx code but my brain is not the fastest), I just know enough to be dangerous. Thank you both

 

[legalhustler]

Delete****

 

[legalhustler]

I have applied a fix. The original link above should still work. Just editing the fix in the video and then I will post that, too

I realized you did a grouping step in your solution and I need to include a column called Amount. For each ID I need to extract the last consecutive amount (excluding weekends like before) based on the Date column or if there is non-consecutive/single date for an ID then just the amount shown. Once the Amounts are the same for each date then your grouping step will work for me.

I need to do this transformation before I apply your solution. My apologies if this deserved if it's own thread. Can you help me? Thank you in advance.

Date	ID	Amount
6/20/2024​	123​	$10​
6/21/2024​	123​	$20​
6/24/2024​	123​	$30​
6/10/2024​	777​	$40​
6/11/2024​	777​	$20​
6/18/2024​	888​	$50​
6/17/2024​	999​	$100​

The output should look like this:

Date	ID	Amount
6/20/2024​	123​	$30​
6/21/2024​	123​	$30​
6/24/2024​	123​	$30​
6/10/2024​	777​	$20​
6/11/2024​	777​	$20​
6/18/2024​	888​	$50​
6/17/2024​	999​	$100​

 

[Matt Allington]

I don’t see any “amount” in the sample data you provided.