Find not consecutive dates in 2 columns

Hi Francesco,

 

Here's one way to do it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc6xCcAwDETRXVQbcSe5SZ8mMxjvv0YEloPtgJqDh/ityXODUgTUOINZDKfSxujlT3wlvhKfpObwMY4v9SNQciOWxGcLg2BrsbMFV/po6S8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"ID Number" = _t, #"Start Date" = _t, #"End Date" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}}),

// Relevant steps from here ======>
    sortIdStartDate = Table.Sort(chgTypes,{{"ID Number", Order.Ascending}, {"Start Date", Order.Ascending}}),
    addIndex0 = Table.AddIndexColumn(sortIdStartDate, "Index0", 0, 1, Int64.Type),
    addIndex1 = Table.AddIndexColumn(addIndex0, "Index1", 1, 1, Int64.Type),
    mergeOnSelf = Table.NestedJoin(addIndex1, {"ID Number", "Index0"}, addIndex1, {"ID Number", "Index1"}, "addIndex1", JoinKind.LeftOuter),
    expandStartDate = Table.ExpandTableColumn(mergeOnSelf, "addIndex1", {"End Date"}, {"PrevEndDate"}),
    addDateRangeType = Table.AddColumn(expandStartDate, "DateRangeType", each if [PrevEndDate] = null then null else if Duration.TotalDays([Start Date] - [PrevEndDate]) = 1 then "Consecutive"
else "Not Consecutive"),
// <====== Relevant steps to here

    remOthCols = Table.SelectColumns(addDateRangeType,{"ID Number", "Start Date", "End Date", "PrevEndDate", "DateRangeType"})
in
    remOthCols

 

It basically involves merging the table on itself using two offset Index columns to get the end date from the previous row with the same ID Number.

It's maybe not the most efficient, but it's likely the easiest/simplest process to understand.

 

Example output:

 

If you wanted to make it more code-efficient (but maybe not resource-efficient), you could replace the Index/Merge bits with one Index, plus row identification using list position arguments e.g.:

PrevEndDate = PreviousStep[End Date]{[Index] - 1}

 

Pete