Group rows only where end and start dates are consecutive

Hello carrl284 

check out this approach. First a new column is added to check if the rows are connected. If there are connected null is written otherwise the prior added Index-value. After a fill-down is made and this data is used to group them. In the function of the group a min for start and a max for end is applied

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc/RCcAgDATQXfwWEs9odRZx/zUaSyNVCwY5eRyxNRcQxXkXdMaNNAKIE4FDHSHN0P3j0+GFuJhHJK6Lzz/9el6v5Rw0gM3X3XOeRF+uzQvvHuXrtR+Lx7GP9ot5++/w/QY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, JobId = _t, SiteId = _t, PersonId = _t, Start = _t, End = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"JobId", Int64.Type}, {"SiteId", Int64.Type}, {"PersonId", Int64.Type}, {"Start", type date}, {"End", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    AddGroupColumn = Table.AddColumn
    (
        #"Added Index",
        "Group",
        (row)=> try if Date.AddDays(row[Start],-1)= #"Added Index"{[Index = row[Index]-1]}[End] then null else row[Index] otherwise row[Index]
    ),
    #"Filled Down" = Table.FillDown(AddGroupColumn,{"Group"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"JobId", "SiteId", "PersonId", "Group"}, {{"Start", each List.Min([Start]), type date}, {"End", each List.Max([End]), type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Group"})
in
    #"Removed Columns"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Hi carrl284 

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc/RCcAgDATQXfwWEs9odRZx/zUaSyNVCwY5eRyxNRcQxXkXdMaNNAKIE4FDHSHN0P3j0+GFuJhHJK6Lzz/9el6v5Rw0gM3X3XOeRF+uzQvvHuXrtR+Lx7GP9ot5++/w/QY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, JobId = _t, SiteId = _t, PersonId = _t, Start = _t, End = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"JobId", Int64.Type}, {"SiteId", Int64.Type}, {"PersonId", Int64.Type}, {"Start", type date}, {"End", type date}}),

    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "BlockStart", each let 
        diffNext_ = try Duration.Days(#"Changed Type"[Start]{[Index]+1} - #"Changed Type"[End]{[Index]}) otherwise -1,
        diffPrev_ = try Duration.Days(#"Changed Type"[Start]{[Index]} - #"Changed Type"[End]{[Index]-1}) otherwise -1
    in 
       if diffPrev_ = 1 then null else [Start], type date),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"BlockStart"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"JobId", "SiteId", "PersonId", "BlockStart"}, {{"End", each List.Max([End]), type date }}
),
    #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"BlockStart", "Start"}})
in
    #"Renamed Columns"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

try this one, change Query3 to the right table name in you query.

let
    Source = Excel.CurrentWorkbook(){[Name="Query3"]}[Content],
    res = Table.FromRecords(List.Accumulate(Table.ToRecords(Source),{},(x,y)=>if List.Last(x)[End]?+#duration(1,0,0,0)=y[Start] then List.ReplaceRange(x,List.Count(x)-1,1,{List.Last(x)&[End=y[End]]}) else x&{y}))
in
    res