Forum Discussion

CJLewis87's avatar
CJLewis87
Frequent Visitor
3 years ago
Solved

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

 

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

 

Need Help
Tips and Tricks
  • slorin's avatar
    slorin
    3 years ago

    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

3 Replies

  • slorin's avatar
    slorin
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      CJLewis87
      Frequent Visitor
      3 years ago

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