Forum Discussion
Solved
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: ...
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
AlienSx
Super User
Super UserSyrathos , 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.
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
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 zThanks so much! That appears to have done the trick!
