Stuck on how to approach assigning a Package_id to multiple lines within a group

Question

Hi, i'm relatively new to Power Query and have hit an obstacle which i can't seem to get my head around.

I havea  list of records for a client which denotes how many services they've had over a period. If additional lines are added within 28 days of the previous it would be considered an 'existing' line to a package of care. I have manged to identify these...but what I need to do now is further group the first instance of a 'new' package with subsequent 'existing' lines for each client...creating a [package_id] in the process. Once I have each line assigned to a [package_id] i then need to determine the [start date] and [end date] for that [package_id] (see example in link below)

This link will take you to the dataset:

I'm hoping someone can help me :)

Thanks

Ian

Answer 1

For this kind of grouping, usually the first step is to add a zero-based index column. This can be used to reference the previous/subsequent rows from the current row (by calculating [Index] plus or minus one). Then add a custom column that does something like: if there's a next row and the current row is within 28 days of next row, then return the next row's index; otherwise return the current row's index. Then group by this custom column. You can now perform calculations within these groups to get the start/end date etc.