Combine contiguous or overlapping date ranges

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