Index (Or Equivalent) that skips existing values (different row).

[Syrathos]

I have spent hours googling and trying various things to no avail so I thought I'd check in with the helpful individuals here.

I have a table in Excel which I am manipulating using power query:

Type	Related ID	Date	Code	QTY	Cost	Index	Desired
Purchase		10/01/2023	ABC	1	100	1	1
Purchase		15/02/2023	ABC	1	100	2	3
Purchase		17/02/2023	ABC	1	100	3	4
Purchase	2	10/03/2023	ABC	1	100	4	2
Purchase		05/01/2023	DEF	1	75	1	1
Purchase		02/02/2023	DEF	1	75	2	2

I am after a way of having an 'index' (or equivalent) that skips the value that exists in column Related ID (but where that index would normally be). Looking at the "Desired" column, row 2 would normally have the index of 2, but since 2 exists in "Related ID" (albeit on row 4) it skips to 3. 2 then shows on the row that has the "Related ID" (row 4). The "Related ID" could be any number and some products may have multiple rows with "Related ID".

[AlienSx]

@Syrathos , first, group your rows by "Code". For each group add new column for sorting like this: if [Related ID] = null then [Index] else [Related ID] - 0.1. Then sort your groups by this new column and add new index column. 

[Syrathos]

Apologies for the delay, I don't know where time has gone. I'm still relatively new to playing around with power query, but it I group the rows by "Code" I seem to lose access to the other column details when trying to add a new column with the if then else statement.

[AlienSx · Accepted solution]

@Syrathos 

let
    Source = your_table,
    group = Table.Group(Source, {"Code"}, {{"all", each fx_tbl(_)}}),
    fx_tbl = (tbl as table) as table =>
        [a = Table.AddColumn(tbl, "sorting", each if List.Contains({null, "", " "}, [Related ID]) then [Index] else [Related ID] - .1),
        b = Table.Sort(a, "sorting"),
        idx = Table.AddIndexColumn(b, "Desired", 1, 1)][idx],
    z = Table.RemoveColumns(Table.Combine(group[all]), "sorting")
in
    z

[Syrathos]

Thanks so much! That appears to have done the trick!