Group records - keeping first Start Date (oldest) and last End Date (most recent)

[Anonymous]

Hey there,

I'm new to Power Query, so I hope someone can help me with this query. 

I have a dataset with ID, Status, Start Date and End Date. There are multiple rows for each ID with different start and end dates. Here's a sample of my dataset.

ID	Status	Start Date	End Date
1	A	01/04/2015	28/05/2015
1	A	28/05/2015	15/06/2016
1	B	15/06/2016	19/06/2016
1	B	19/06/2016	31/07/2016
1	B	31/07/2016
2	B	01/03/2017	03/06/2018
2	A	03/06/2018	07/08/2018
2	A	07/08/2018	31/12/2018
2	C	31/12/2018	01/09/2019
2	C	01/09/2019	03/05/2020
2	A	03/05/2020
3	C	01/02/2020	03/05/2020
3	C	03/05/2020	05/08/2020

I want to group consecutive rows (End Date same as the Start Date of next row) with same status for each ID and take the oldest Start Date and the most recent End Date. Here is the the output I'm looking for. 

ID	Status	Start Date	End Date
1	A	01/04/2015	15/06/2016
1	B	15/06/2016
2	B	01/03/2017	03/06/2018
2	A	03/06/2018	31/12/2018
2	C	31/12/2018	03/05/2020
2	A	03/05/2020
3	C	01/02/2020	05/08/2020

Is this possible? 

Many Thanks for your help.

[Payeras_BI · Accepted solution]

Hi @Anonymous ,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZBLDoUgDEW3Yjom6YeHwFDfMoz734YtEKXRAYN7esItHAcwBNj0ECP9UIiTBilIqYczPM6EA3BCWi2st7N7rKF+O3VyohbntzPhAEsbyRjZqtFG2UIcd5Xb2TzWkJHKh/Pg1sfinb/HvbhaqM6ZcC+2PxJ67TNwe855AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Status = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Status", type text}, {"Start Date", type date}, {"End Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "Status"}, {{"Start Date", each List.First([Start Date]), type nullable date}, {"End Date", each List.Last([End Date]), type nullable date}},GroupKind.Local

)
in
    #"Grouped Rows"

[Anonymous]

Hi @Payeras_BI,

Related to the above question,  if there is a break in dates for the same ID and status, then that should be kept as a different group. 

ID	Status	Start Date	End Date
1	A	01/04/2015	28/05/2015
1	A	28/05/2015	15/06/2016
1	A	01/05/2017	31/03/2018
1	A	01/04/2019	03/04/2019
1	B	15/06/2016	19/06/2016
1	B	19/06/2016	31/07/2016
1	B	31/07/2016	10/09/2016
1	A	10/09/2016

For e.g rows 3 and 4 in the table above, should be as in the table below,

ID	Status	Start Date	End Date
1	A	01/04/2015	15/06/2016
1	A	01/05/2017	31/03/2018
1	A	01/04/2019	03/04/2019
1	B	15/06/2016	10/09/2016
1	A	10/09/2016

Is this possible at all? Please can you/someone help me with this?

Many Thanks.

[Payeras_BI]

Hi @Anonymous ,

In this case try with this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY5LDsAgCAWv0rA2AWr9Les1jPe/RkHTVsOCxbwMPFoDBge3DDHShSdxEDgzUpjQ3e8ssQMOSFEhbo7eGU4S8AJeIRtndBUF/8Hr1P28QDFddY9nV7LOEssCIRX78xI7OKD3Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Status = _t, #"Start Date" = _t, #"End Date" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Status", type text}, {"Start Date", type date}, {"End Date", type date}}),
    #"Added Index" = Table.AddIndexColumn(ChangedType, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "GroupBy", each let 
     Check1 = try Duration.Days(ChangedType[Start Date]{[Index]} - ChangedType[End Date]{[Index]-1}) otherwise 1,
     Check2 = try ChangedType[ID]{[Index]} = ChangedType[ID]{[Index]-1} otherwise false,
     Check3 = try ChangedType[Status]{[Index]} = ChangedType[Status]{[Index]-1} otherwise false
     in 
     if Check1 = 0 and Check2 =true and Check3 = true then null else [Start Date], type date),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"GroupBy"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"ID", "Status", "GroupBy"}, {{"Start Date", each List.Min([Start Date]), type nullable date}, {"End Date", each List.Max([End Date]), type nullable date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"GroupBy"})
in
    #"Removed Columns"

[Anonymous]

Thanks so much @Payeras_BI, exactly what I needed. Much appreciated..