Group rows only where end and start dates are consecutive

[carrl284]

Hi

This is a sample of my data.  Can anyone show me how to combine the rows in power query but only where the dates are consecutive.

This table 

Id	JobId	SiteId	PersonId	Start	End
1234	1	4	25	12/05/2019	15/05/2019
1235	1	4	25	14/08/2019	23/09/2019
1236	1	4	25	12/12/2019	05/01/2020
1239	1	4	25	06/01/2020	27/01/2020
1240	1	4	25	28/01/2020	25/02/2020
1242	1	4	25	16/04/2020	15/05/2020

becomes this table

JobId	SiteId	PersonId	Start	End
1	4	25	12/05/2019	15/05/2019
1	4	25	14/08/2019	23/09/2019
1	4	25	12/12/2019	25/02/2020
1	4	25	16/04/2020	15/05/2020

Many Thanks

[Jimmy801 · Accepted solution]

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

[AlB · Accepted solution]

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 

[carrl284]

All 3 approaches worked a treat.  I don't know if I can mark them all as the solution but I tried all 3 and they all worked perfectly.  Thanks

[shaowu459 · Accepted solution]

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

 

[AlB · Accepted solution]

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 

[Jimmy801 · Accepted solution]

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