Group rows based on values in adjacent rows

Hey,

here you will find a PBIX file that creates this table base on your raw data:

There are some twists for this reason the following explains all the steps.

Step Grouped Rows
Basically I started with the transform "Group by" using the advanced settings to be able to group by more than one column.
I choose as "All Rows" as Grouping Operation.

The M formula that will be generated look like this:

= Table.Group(#"Changed Type", {"HoleNo", "Rock"}, {{"Columns", each _, type table}})

But this would not consider that there is a 2nd group "TF", this reason I tweaked the generated M Formula by adding GroupKind.Local (https://msdn.microsoft.com/en-us/query-bi/m/table-group)

= Table.Group(#"Changed Type", {"HoleNo", "Rock"}, {{"Columns", each _, type table}}, GroupKind.Local)

Now I get 5 groups instead of 4 :-)

Please be aware that you will not be able to use the Grouping Dialog any longer ;-)

Added Index: Adding an Index Column (starting with 1)

Expanded Count: Table Expansion
I expanded the table without using a suffix, selecting just the missing columns.

Grouped Rows1: Group by (Index - Operation "All Rows"

Another "Group by" this time by freshly generated Index column. This will create the following M Formula:

= Table.Group(#"Expanded Count", {"Index"}, {{"Columns", each _, type table}})

Now I'm repacing the bold part of formula by this snippet:

{
{"AllRows", each _, Value.Type(#"Expanded Count")}, 
{"Minimum From", each List.Min([From]), type number},
{"Maximum To", each List.Max([To]), type number}
}

That finally leads to this formula:

= Table.Group(#"Expanded Count", 
{"Index"}, 
{
{"AllRows", each _, Value.Type(#"Expanded Count")}, 
{"Minimum From", each List.Min([From]), type number},
{"Maximum To", each List.Max([To]), type number}
}
)

The result will look like this:

Expanded AllRows (Table Expansion)

Once again I expand the table to get back the missing columns.

Removed Columns (Removing unwanted columns)

Removed Duplicates

Voila

Hopefully this is what you are looking for

Regards

Tom