Easiest way to delete item from a list iteratively

[Chase_Schwalbach]

Hey all,

I’m using a FormulaMap to iterate over every row but the one I’m on, so something like

Table.Filter(CurrentValue!=thisRow).FormulaMap()

Then I want to go and delete my name from a list. I wanted to use .Filter() but that doesn’t work if my name isn’t on the list. In that case I have to use an IF() statement which gets super clunky. I was hoping something failed gracefully. I tried thisRow-User() but it deletes everything in the column.

Beyond the IF() statement being clunky and looking for something cleaner, I also get an “Unable to execute invalid action” when trying to do a Null result on a FormulaMap, so I had to do another ModifyRows() for a fake column just to make it work. How can I fix that??

Test.Filter(

CurrentValue!=thisRow).FormulaMap(

IF(CurrentValue.Voters.Contains(User()),

ModifyRows(
CurrentValue,
Voters,
Voters.Filter(CurrentValue!=User())),
ModifyRows(CurrentValue, [Column 3], “good”)))

[Jean_Pierre_Traets]

Dear @Chase_Schwalbach,

@Paul_Danyliuk has made a detailed tutorial on iterator tables in the below link:

Tutorial: Iterator tables (CurrentValue workaround)

After answering the CurrentValue issue questions so. many. times, I finally recorded the tutorial on a Coda data processing pattern I use as a workaround for this very problem. It involves creating single-row helper tables that store outer CurrentValue values into cells, so that they’re accessible in nested Filters and FormulaMap loops. I call this pattern Iterator tables, and beside being a workaround for CurrentValue, these are very powerful for doing more complicated calculations The video i…

I am rather sure that this a good start point

[Paul_Danyliuk]

@Chase_Schwalbach no need for iterator table — this works, not sure why you had problems:

thisTable.Filter(
  CurrentValue != thisRow
).FormulaMap(
  CurrentValue.ModifyRows(
    Voters,
    Voters.Filter(CurrentValue != User())
  )
)

If you want to avoid modifying rows that have nothing to modify, you can further filter rows down to only those that have you as a voter; no If() required:

thisTable.Filter(
  CurrentValue != thisRow
  AND Voters.Contains(User())
).FormulaMap(
  CurrentValue.ModifyRows(
    Voters,
    Voters.Filter(CurrentValue != User())
  )
)

You’d need an iterator table if you were doing some comparison within the inner Filter with the CurrentValue of FormulaMap. But in this case there are no CurrentValue context issues here.