Check if dates are continuous using Power Query

[faustusxanthis]

Hello!

I apologize for probable unclear questions. I do not know exactly what to ask so I can't easily search the forums or online. 

I have this set of data

Person Name	Start Date of activity	End Date of activity	Type of activity	Result
Person 1	09/26/2023	09/26/2023	Activity 1	HAS GAPS
Person 1	08/05/2023	09/01/2023	Activity 1	HAS GAPS
Person 1	07/07/2023	08/05/2023	Activity 1	HAS GAPS
Person 1	09/26/2023	09/26/2023	Activity 2	NO GAPS
Person 1	08/05/2023	09/26/2023	Activity 2	NO GAPS
Person 1	07/07/2023	08/05/2023	Activity 2	NO GAPS
Person 2	09/26/2023	09/26/2023	Activity 1	HAS GAPS
Person 2	05/02/2023	09/26/2023	Activity 1	HAS GAPS
Person 2	02/01/2023	04/04/2023	Activity 1	HAS GAPS
Person 2	04/03/2023	04/04/2023	Activity 1	HAS GAPS

I would need to know which person name and corresponding type of activity has gaps in the start and end dates

Eg: Person 1 doing Activity 1 has gap from 09/01/2023 to 09/26/2023

Person 1 does not have gaps doing Activity 2 which started from 07/07/2023 to 09/26/2023

Person 2 has gaps doing Activity 1 from 04/04/2023 to 05/02/2023

The result should be in the Result tab on the right. And if possible, identify the date ranges that has gaps. 

I need this to be done in Power Query. 

Thank you in advance!

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PUzBU0lEysNQ3MtM3MjAyRuc4JpdklmWWVAKVxeqg6rHQNzBF0mNgSIQec30ggulBNgCPHsJuMyLkNqL0EHYbih4jMsINrMdU38CIVD1GSMFrYKIPRIT1AJUZE9QTCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Person Name" = _t, #"Start Date of activity" = _t, #"End Date of activity" = _t, #"Type of activity" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person Name", type text}, {"Start Date of activity", type date}, {"End Date of activity", type date}, {"Type of activity", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Person Name", "Type of activity"}, {{"From", each List.Min([Start Date of activity]), type nullable date}, {"To", each List.Max([End Date of activity]), type nullable date}, {"Rows", each _, type table [Person Name=nullable text, Start Date of activity=nullable date, End Date of activity=nullable date, Type of activity=nullable text]}}),
    GapFinder = (tbl)=>
        let
            #"Sorted Rows" = Table.Sort(tbl,{{"Start Date of activity", Order.Ascending}}),
            #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
            #"Added Custom" = Table.AddColumn(#"Added Index", "Gap", each if [Index]=0 then 0 else  Int64.From([Start Date of activity]-#"Added Index"{[Index]-1}[End Date of activity]) ,Int64.Type)
        in
            #"Added Custom",
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each GapFinder([Rows])),
    #"Aggregated Custom" = Table.AggregateTableColumn(#"Added Custom", "Custom", {{"Gap", List.Sum, "Sum of Gap"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Aggregated Custom",{"Person Name", "Type of activity", "From", "To", "Sum of Gap"})
in
    #"Removed Other Columns"

or like this  (which also highlights that you have overlaps)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PUzBU0lEysNQ3MtM3MjAyRuc4JpdklmWWVAKVxeqg6rHQNzBF0mNgSIQec30ggulBNgCPHsJuMyLkNqL0EHYbih4jMsINrMdU38CIVD1GSMFrYKIPRIT1AJUZE9QTCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Person Name" = _t, #"Start Date of activity" = _t, #"End Date of activity" = _t, #"Type of activity" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person Name", type text}, {"Start Date of activity", type date}, {"End Date of activity", type date}, {"Type of activity", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Person Name", "Type of activity"}, {{"From", each List.Min([Start Date of activity]), type nullable date}, {"To", each List.Max([End Date of activity]), type nullable date}, {"Rows", each _, type table [Person Name=nullable text, Start Date of activity=nullable date, End Date of activity=nullable date, Type of activity=nullable text]}}),
    GapFinder = (tbl)=>
        let
            #"Sorted Rows" = Table.Sort(tbl,{{"Start Date of activity", Order.Ascending}}),
            #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
            #"Added Custom" = Table.AddColumn(#"Added Index", "Gap", each if [Index]=0 then 0 else  Int64.From([Start Date of activity]-#"Added Index"{[Index]-1}[End Date of activity]) ,Int64.Type)
        in
            #"Added Custom",
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each GapFinder([Rows])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Start Date of activity", "End Date of activity", "Gap"}, {"Start Date of activity", "End Date of activity", "Gap"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom",{"Person Name", "Type of activity", "Start Date of activity", "End Date of activity", "Gap"})
in
    #"Removed Other Columns"