Find not consecutive dates in 2 columns

[Fra19]

Hi there 

I am facing this challenge

Have a set of data with start and end date and with multiple records for each unique ID

And I need to find  which Start and End dates   are not consecutive per single record

Is there a way to achieve this 8n Power query Excel or Power BI?

I am loosing my wits.

Example

ID Number  Start Date    End Date

D01              01.01.2022   31.12.2022

D01.             01.01.2023   31.12.2023

D02.             01.03 2022  10.10.2022

D02              01.01.2023.  09.03.2023

For ID01  start and end dates are consecutive  but for ID02 they are not.

Is there a way to write a formula  that checks this?        

Thank you in advance for the help and thoughts

Francesco 

[BA_Pete · Accepted solution]

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