question

IanLyons-1239 avatar image
0 Votes"
IanLyons-1239 asked Ehren answered

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

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)

83706-screenshot-2021-04-01-162351.jpg

This link will take you to the dataset:



I'm hoping someone can help me :)



Thanks



Ian


power-query-not-supported
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Ehren avatar image
0 Votes"
Ehren answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.