cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Devjour_239
Frequent Visitor

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

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.

 

IDStatusStart DateEnd Date
1A01/04/201528/05/2015
1A28/05/201515/06/2016
1B15/06/201619/06/2016
1B19/06/201631/07/2016
1B31/07/2016 
2B01/03/201703/06/2018
2A03/06/201807/08/2018
2A07/08/201831/12/2018
2C31/12/201801/09/2019
2C01/09/201903/05/2020
2A03/05/2020

 

3C01/02/2020

03/05/2020

3C03/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. 

IDStatusStart DateEnd Date
1A01/04/201515/06/2016
1B15/06/2016 
2B01/03/201703/06/2018
2A03/06/201831/12/2018
2C31/12/201803/05/2020
2A03/05/2020 
3C01/02/202005/08/2020

 

Is this possible? 

 

Many Thanks for your help.

 

1 ACCEPTED SOLUTION
Payeras_BI
Super User I
Super User I

Hi @Devjour_239 ,

Payeras_BI_0-1627025705705.png

 

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"

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

View solution in original post

3 REPLIES 3
Payeras_BI
Super User I
Super User I

Hi @Devjour_239 ,

Payeras_BI_0-1627025705705.png

 

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"

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

View solution in original post

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. 

IDStatusStart DateEnd Date
1A01/04/201528/05/2015
1A28/05/201515/06/2016
1A01/05/201731/03/2018
1A01/04/201903/04/2019
1B15/06/201619/06/2016
1B19/06/201631/07/2016
1B31/07/201610/09/2016
1A10/09/2016 

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

IDStatusStart DateEnd Date
1A01/04/201515/06/2016
1A01/05/201731/03/2018
1A01/04/201903/04/2019
1B15/06/201610/09/2016
1A10/09/2016 

 

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

Many Thanks.

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors