Forum Discussion
Index (Or Equivalent) that skips existing values (different row).
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".
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
4 Replies
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
Super User
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
