Forum Discussion

Syrathos's avatar
Syrathos
New Member
3 years ago
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: ...
Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    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's avatar
Syrathos
New Member
3 years ago

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's avatar
AlienSx
Icon for Super User rankSuper User
3 years ago

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's avatar
    Syrathos
    New Member
    3 years ago

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