PowerQuery M Min/Max Contiguous ID Values by Group

[Gutedama]

Hi everyone,

I'm hoping for some assistance with flattening/grouping records across contiguous ranges. My data has 3 key columns that act as a key. My goal is to group columns within these 3 fields but only where the ranges are contiguous. There should never be any range overlap, only breaks between ranges, and there can be significant differences between the range starts and ends between different events and locations (illustrated below). Also there can be any number of events and locations (they'll repeat with some regularity within events but can change). I'm pretty new to M so a little out of my depth here.

Here's an example layout of my data:

Event	Location_1	Location_2	Range_Start	Range_End
Event_A	A1	R1	1	2
Event_A	A1	R2	4	6
Event_A	A2	R1	1	2
Event_A	A2	R1	5	7
Event_A	A2	R1	8	11
Event_A	A2	R1	12	12
Event_A	A2	R1	15	17
Event_A	A2	R1	21	23
Event_A	A3	R3	14	15
Event_B	A1	R1	2	5
Event_B	A1	R1	6	8
Event_B	A1	R1	12	16
Event_B	A1	R1	20	20
Event_B	A1	R2	3	4
Event_B	A1	R2	5	9
Event_B	A2	R3	15	18
Event_B	A2	R3	19	19

And here's the desired output, flattned for continguous ID ranges by the event, Location_1 and Location_2 fields:

Event	Location_1	Location_2	Range_Start	Range_End
Event_A	A1	R1	1	2
Event_A	A1	R2	4	6
Event_A	A2	R1	1	2
Event_A	A2	R1	5	12
Event_A	A2	R1	15	17
Event_A	A2	R1	21	23
Event_A	A3	R3	14	15
Event_B	A1	R1	2	8
Event_B	A1	R1	12	16
Event_B	A1	R1	20	20
Event_B	A1	R2	3	4
Event_B	A1	R3	5	9
Event_B	A1	R3	15	19

 The fundamental problem (I think) I'm running into is that the number of records +/- to evaluate is dynamic and I can't see an effective way to create a boundary between groups + ranges, and I haven't found a way to delineate between continguous seats within the same grouping to build a better defined group. I've attempted at a high level:

• Expanding the ranges from a list to individual items and attempting a group by on the other 3 fields with GroupType.Local. Doesn't seem to register the range item as a local break so my min and max are across the full location values, regardless of breaks in the middle.
• Attempting to add an index column but, again, without knowing how to define the boundary at the range level I'm not sure how to break the index meaningfully.

Would VERY MUCH appreciate anyone's help with this. Been banging my head against this for several hours. Thanks!

[Gutedama · Accepted solution]

Disregard , I finally found a solution at the link below. A huge thank you to @edhans whose solution there was more than sufficient for me to figure it out. I wasn't aware of how to lag a column's values to create the boundaries I was after in M, that was the missing key in my case.

Link to the original thread: https://community.powerbi.com/t5/Power-Query/Grouping-Date-sets-within-a-month/m-p/1428838#M44645

However, if anyone has any alternatives aside from lagging a column and then producing a conditional index that would be great. I'm a bit worried about the efficiency of this method as the data grows (could be unfounded though)  and always looking to learn.