Sequential Order column based on multiple criteria

[unknown917]

I am in need of creating a column "sequential order"  that will assign numerical order to each unique subset under the hub.  Here's the catch...the hub parent should always be first & not all hubs have subsets.  Each subset in the hub gets numbered sequentially.  Below is the desired result column "Seq Order".  Any help will be greatly appreciated.

Hub	Hub Name	Subset ID	Subset Name	Seq Order
1	FW		FW	0
2	INA	702	INA	0
2	INA	624	GUAVA	1
3	SB		SB	0
4	LLLE	700	LLLE	0
4	LLLE	203	APPLE	1
4	LLLE	521	BANANA	2
4	LLLE	700	GRAPE	3
4	LLLE	136	ORANGE	4
4	LLLE	953	PEAR	5
4	LLLE	630	MANGO	6
4	LLLE	631	PINEAPPLE	7
4	LLLE	632	COCONUT	8
5	COL	028	COL	0
6	CIN	609	CIN	0
7	DTN		DTN	0
8	MAR		MAR	0
9	TWI		TWI	0
10	MID		MID	0
11	SP		SP	0
12	PTB		PTB	0
13	BEC		BEC	0
14	STO		STO	0

[MFelix · Accepted solution]

Hi @unknown917 ,

Do a group by the hub then add a new column with the following code:

Table.AddIndexColumn ([HubRows], "Seq Order",0)

Now expand the columns you need to see.

Full code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZHBDoMgEER/pfHcA2LVekRqDYkFotgejP//G93B5VDpZTO8YZcBtq0oi2vx/FC5HGK/boUkaayi2oqkf3kjb1THVb0Pp6LV0vMUEmDYMU3TEMeItDg5UqBVeZ9btUS2XlnFx2cDx1n5rK2sGqpuVnbMvK7GaX5Q89lpKgx8UZPLLeTwxg5/YzYVnkU77ewaolnH9URVyDtrcOTSxqJHdKzBW9KPYPn1oEDvMc/M9MWR0Rc+hikUaBnDm0faTCpiBF98+hZ/QMT1IX0WVMR4mX7QjKEixk2X4NIMUvv+BQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Hub = _t, #"Hub Name" = _t, #"Subset ID" = _t, #"Subset Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Hub", Int64.Type}, {"Hub Name", type text}, {"Subset ID", Int64.Type}, {"Subset Name", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Hub"}, {{"HubRows", each _, type table [Hub=nullable number, Hub Name=nullable text, Subset ID=nullable number, Subset Name=nullable text, Seq Order=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn ([HubRows], "Seq Order",0)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Hub Name", "Subset ID", "Subset Name", "Seq Order"}, {"Hub Name", "Subset ID", "Subset Name", "Seq Order"})
in
    #"Expanded Custom"

[unknown917]

So much to learn!  Thank you @MFelix