Combine contiguous or overlapping date ranges

[GeoffreyC]

Hello,

I would like to combine date ranges to show a maximum range that encompasses any overlapping ranges or any ranges that are contiguous.

The data is about hosts and their guests and I need to identify continuous periods of hosting for each host.  Sometimes a guest leaves the host but then returns at a later date leaving a gap between hosting periods (e.g. HostID 15) and this should result in 2 separate hosting periods. Some hosts have guests that did not stay with them at the same time but were contiguous, i.e. the StartDate of the second guest was within 24 hours of the first guest's EndDate (e.g. HostID 17) and this would result in 1 period.  Other hosts have guests where the stays overlapped (e.g. HostID 45), resulting in 1 period.  If a guest is currently staying with a host the EndDate is null (e.g. HostID 47).

Here is an example of the data I am working with.

<!-- tblMatches -->
HostID	GuestID	StartDate	EndDate
15	2	01/02/2023	12/03/2023
15	2	20/06/2023	Null
17	4	04/04/2023	17/08/2023
17	6	18/08/2023	02/11/2023
45	8	02/05/2022	05/01/2023
45	10	30/12/2022	Null
45	12	13/07/2022	01/09/2022
47	14	14/10/2023	Null

And this is the result I am hoping for:

HostID	StartDate	EndDate
15	01/02/2023	12/03/2023
15	20/06/2023	Null
17	04/04/2023	02/11/2023
45	02/05/2022	Null
47	14/10/2023	Null

Any help would be greatly appreciated.

[GeoffreyC]

Thank you so much for this, spinfuzer.  It does exactly what I need.
When I looked at the source data again, I found there are some records such as:

HostID	GuestID	StartDate	EndDate
49	16	15/04/2022	02/06/2022
49	18	15/04/2022	10/09/2022
49	20	15/04/2022	02/06/2022
49	22	15/04/2022	10/09/2022

 but, as you say, sorting the hosts by ID and then by StartDate, plus also by EndDate fixes it.

Amazing! Thank you!

[spinfuzer · Accepted solution]

You might need to make sure the Hosts are sorted by ID and start date first.

If the end date + 1 of the previous row is equal to or greater than the start date of the currennt row then update the end date to the end date of the current row.  Is the previous end date ever greater than the next end date?  Compare and take the greater end date and account for nulls.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY9RDsMgDEPvwnclOwEKO0vVG1T92/2XEMG6SRDJzsMkx5Gkpi2pXQqoUGo2IQrmEOf2gJTgPqH7fV3RbqaKZxTYmRkN7I8Mh3b3+/LthULkCxX/qIfP6v4YrYL/kNBKJkQntcaJvluSwbZSbMFXiEH5PFJGgfB3qfMD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [HostID = _t, GuestID = _t, StartDate = _t, EndDate = _t]),
    convert_dates = Table.TransformColumns(Source,{{"StartDate", each Date.FromText(_, [Format="dd/MM/yyyy"]), type date},{"EndDate", each Date.FromText(_, [Format="dd/MM/yyyy"]), type date}}),
    
    consecutive_dates =
        (tbl) => 
        let
            rows = List.Buffer(Table.ToRecords(Table.SelectColumns(tbl,{"HostID","StartDate","EndDate"})))
        in
            Table.FromRecords(
                List.Accumulate(
                    {1 .. List.Count(rows)-1},
                    {rows{0}},
                    (acc,curr) => 
                    try
                        if Date.AddDays(List.Last(acc)[EndDate],1) >= rows{curr}[StartDate] 
                        then List.RemoveLastN(acc,1) & {Record.TransformFields(List.Last(acc), {"EndDate", each rows{curr}[EndDate]})}
                        // update end date if consecutive
                        else acc & {rows{curr}} // add new row if not consecutive
                    otherwise
                        acc
                )
            ),
    #"Grouped Rows" = 
        Table.Group(
            convert_dates, 
            {"HostID"}, 
            {
                {"group dates", each consecutive_dates(_), type table [HostID=nullable text, StartDate=date, EndDate=date]}
            }
        ),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"group dates"}),
    Expanded = Table.ExpandTableColumn(#"Removed Other Columns", "group dates", {"HostID", "StartDate", "EndDate"}, {"HostID", "StartDate", "EndDate"})
in
    Expanded