Group rows based on values in adjacent rows

[LouiseSemaj]

I am trying to group adjacent rows (based on 'Rock'), then show the min/max of another field (From and To).  This would be OK, but one of the Rock codes (Tf) is repeated further down the table and needs to be a seperate ros in the results.  See below the raw data and what I am chasing. I am using Power query please.  Any assistance would be much appreciated.  Many thanks

From This					To this
HoleNo	From	To	Rock			HoleNo	From	To	Rock
Hole01	0	1	Qac			Hole01	0	4	Qac
Hole01	1	2	Qac			Hole01	4	9	Tf
Hole01	2	3	Qac			Hole01	9	10	Tk
Hole01	3	4	Qac			Hole01	10	15	Tm
Hole01	4	5	Tf			Hole01	15	19	Tf
Hole01	5	6	Tf
Hole01	6	7	Tf
Hole01	7	8	Tf
Hole01	8	9	Tf
Hole01	9	10	Tk
Hole01	10	11	Tm
Hole01	11	12	Tm
Hole01	12	13	Tm
Hole01	13	14	Tm
Hole01	14	15	Tm
Hole01	15	16	Tf
Hole01	16	17	Tf
Hole01	17	18	Tf
Hole01	18	19	Tf

[TomMartens · Accepted solution]

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

[pdsoutherland]

Adapted this to create a consecutive hour group for an hour category from a dateTime dimension table. Absolutely awesome.

[LouiseSemaj]

Awesome,  I think this is just what I am chasing. How would I access and read the PBIX file please?  Many thanks Tom

[TomMartens]

Hey,

if you don't have Power BI Desktop installed on your machine, you can get from here www.powerbi.com (it's free). Maybe you get asked to sign in 😉 Just press "I already have an account", close the next dialog using the the cross in the top right corner. Now you are able to use my already downloaded pbix file, Hit Edit queries and there you will find my query 😀

Regards Tom

[LouiseSemaj]

Thats it.  Done.  Many thanks Tom Working a treat.  Kind regards

[TomMartens]

Hey,

is it valid to assume that the table can be ordered by HoleNo ASCENDING and From ASCENDING

And if I got the table in that order it will be valid that each time when ROC(from current row) <> ROC(from previous row) a new "grouping" starts?

Regards

Tom 

[LouiseSemaj]

Many thanks Tom,  Yes will always be soted such