Grouping rows by min start data and max end date, only with consecutive dates (Episode)

[CJLewis87]

Hi All

I'm new to the forum and am currently enrolled in a Data Analyst apprenticeship with my employer (due to finish in the next few months). I have been using Power Query for the past couple of years and have recently started my journey with Power BI/DAX. I am running into an issue where i want to group service lines together from our system into proper "episodes". In the test example I've attached below, the same ID number has had 4 service lines with the same provider, giving me 2 actual episodes (1st April to 14th June and then again from 18th June to 30th June). Thus, I want to group the service lines together by ID number/name/provider with the min start date and max end date, but only where there are no gaps between one service lines end date and the next lines start date. 

I am hitting a bit of a brick wall trying to get my head around how I can go about this. I have done some googling and plenty of searches on YouTube, but haven't managed to find a solution so far.

Any help would be much appreciated 

Thanks

Cal

[CJLewis87]

Brilliant, works as needed. Thanks so much

[slorin · Accepted solution]

Hi

let
Source = YourSource,
#"EndDate+1" = Table.AddColumn(Source, "End Date+1", each Date.AddDays([End Date],1)),
Join = Table.NestedJoin(#"EndDate+1", {"ID Number", "Start Date"}, #"EndDate+1", {"ID Number", "End Date+1"}, "NextRow?", JoinKind.LeftOuter),
Test_Group = Table.TransformColumns(Join,{{"NextRow?", each Table.IsEmpty(_), type logical}}),
LocalGroup = Table.Group(
   Test_Group,
   {"ID Number", "Name", "Provider", "NextRow?"}, 
   {{"Start Date", each List.Min([Start Date]), type date}, {"End Date", each List.Max([End Date]), type date}},
   GroupKind.Local,
   (x,y) => if y[#"NextRow?"] then 1 else 0)
in
LocalGroup

 Stéphane

[CJLewis87]

Many thanks for your response. I will have a play with this later on and will accept solution if all working 😀