How to get `Nth` to return `Blank` if index is not found

Right now, Nth returns an error if the provided index is not found.

E.g. if my table has three rows, and I write the formula:

[My Table].Nth(5)

I will get this result:
image

However, if I’m trying to write a running total with Nth I could approach it like:

thisTable.Nth(thisTable.Find(thisRow) - 1).ifBlank(startingValue) + thisRow.Amount

You would think this works, but since Table.Nth(i) returns an error when i > Table.Count()—rather than returning Blank—it breaks. If it returned Blank not only could this formula work, but it would also likely allow more efficient lookups, e.g.

Is there an elegant way to get around this?

I’m not sure about this on tables, but this formula could work:

 List("","cats","dogs").Nth("1")
 List("","cats","dogs").Nth(4) 

Won’t though, right?

Yep. Are you using a control to determine the value of the nth?

Here is a formula that might work:

If([slider 1]>[Table 1].Count(), "",[Table 1].Nth([slider 1]))

Hope this helps! :grinning_face_with_smiling_eyes:

Maybe I’m not making sense.

The goal is to make a running total of a column.

I want to use thisTable.Nth() to look up the previous row. However, the first row will not have a previous row. I’d like to know how to access that previous row

You might want to look at this topic:

Although it solves your problem, the rowid is not reliable in the situatuion, so you might have to tinker around with the formula.

If you have any other questions, you can ask them to me.

Yeah, I know how to do a running total with thisRow.RowId() but I want to do it with Nth, that’s why I’m asking if there is a way to get Nth to return blank (not error) if it fails a lookup.

I’ve been making a doc with that, here:

(you’re probably going to want to hide the rowid column., also, you should use a sort.)

I hope this helps!

Dear @BODHI_SCHNEIDER,

Thanks sharing a sample, would you mind to update the sharing setting to " everybody with the link", to become visible to the community?

Always eager to learn :wink:

Sure @Jean_Pierre_Traets! :grinning_face_with_smiling_eyes:

Just PLEASE don’t delete anything, I couldn’t find play so I put it on edit.

The play mode is in the Embed option :wink:

So far, my Test docs are still in “can view” :blush: .

1 Like

Hi Connor, why would the index ever be out of range if you are finding thisRow in thisTable? Except for the first row, I suppose, which has no previous.

But maybe this is what you are looking for? But you are right, it is not as elegant as Nth() returning a blank value rather than an error.

WithName(Find(thisRow,thisTable), index,
   If(index=1, thisRow.Amount,
      thisRow.Amount+thisTable.Nth(index-1).[Running Total])
 )

If for whatever reason I thought that index might be higher or lower than the range, I would have to include that in my if statement. Eg, If(index<=1 || index>Count(thisTable), ...

Note that this index is the manual order of the rows and does not reflect either RowID or any filter/sort AFAIK.

As per OP’s original intent, can anyone confirm whether this is actually more efficient than the filter/slice version of the running total formula? Does it make any difference?

Yeah, this specific lookup is likely not more efficient. When I’m back at my computer I’ll have to look back at what I thought would be more efficient.

Yes, an if statement solves the problem at the price of elegance.

So apparently it is working now. In my memory, this formula brought up an error before, and now it still has an error but continues the calculation.

thisRow.Amount+thisTable.Nth(Find(thisRow,thisTable)-1).[Running Total]

The inelegant if statement is gone :slight_smile:

(Updated my doc above.)

So @cnr you are telling me that my three formulae in this doc are equally efficient?

For my understanding, The Running Total is the delta between N and N+1 ?

if that is the case an alternative might be:

thisTable.[Running Total].Slice(1,Find(thisRow,thisTable)).Sum()`

best, Christiaan

Still haven’t had a chance to play with this. I’ll get back to you

But yes, it is just the first row that returns the error, as you note

Yes. Assuming “Running Total” is the name of the column with the raw amounts (ie, not a self-reference) - in my table the column called “Amount”.

But yes, that’s a more elegant solution :slight_smile:

1 Like