Determine Duration for Consecutive and Non-Consecutive Dates|Power Query|Excel Forum|My Online Training Hub

Hi Everyone,

I'm working with a column called "DateCol Transposed," which contains transposed date values of the "Date" column for unique IDs (see attached). 

As you can see, each table value is a nested record for an ID, and the dates are in ascending order. I need to determine the duration it took to fix a process based on these failure dates. A non-consecutive/single date (has no succeeding date) is considered one instance of failure.  A consecutive date range (has subsequent succeeding dates excluding weekends) is considered just one instance of failure also.

For example, a given ID has the dates 6/17/2024, 6/19/2024, 6/20/2024, 6/21/2024, and 6/26/2024, the process failed in three instances:

1. 6/17/2024 (non-consecutive), corrected by 6/18/2024.
2. 6/19/2024 to 6/24/2024 (consecutive), corrected on 6/24/2024.
3. 6/26/2024 (non-consecutive), corrected by 6/27/2024.

I want to add a column that captures each failure instance, correction date, and duration (excluding weekends). The result should look like this:

For consecutive dates, only the first and last dates should be picked. For non-consecutive dates, the correction date is the day after the failed date.

I've written a formula to extract the first and last dates, but it doesn't handle mixed consecutive and non-consecutive dates correctly (see attached screenshot which follows the prior screenshot example and the formula below):

Table.RenameColumns(Table.SelectColumns([DateCol Transposed],
if Table.ColumnCount([DateCol Transposed]) = 1 then {Table.ColumnNames([DateCol Transposed]){0}} else {Table.ColumnNames([DateCol Transposed]){0}, Table.ColumnNames([DateCol Transposed]){Table.ColumnCount([DateCol Transposed])-1}}),

if Table.ColumnCount(Table.SelectColumns([DateCol Transposed], if Table.ColumnCount([DateCol Transposed]) = 1 then {Table.ColumnNames([DateCol Transposed]){0}} else {Table.ColumnNames([DateCol Transposed]){0}, Table.ColumnNames([DateCol Transposed]){Table.ColumnCount([DateCol Transposed])-1}})) = 1 then {} else {{Table.ColumnNames(Table.SelectColumns( [DateCol Transposed],

if Table.ColumnCount([DateCol Transposed]) = 1 then {Table.ColumnNames([DateCol Transposed]){0}} else {Table.ColumnNames([DateCol Transposed]){0}, Table.ColumnNames([DateCol Transposed]){Table.ColumnCount([DateCol Transposed])-1}})){1}, "Column2"}})

Any help would be greatly appreciated.

Thanks!