Answered

Get 2nd to last row in an excel table

(0) Share

Report

Posted on by Noah100

Hello community!

I am having trouble figuring out how to reference the second to last value in a column using power automate.

The goal is that when a new Sharepoint form is submitted, which the results are submitted into a table in excel, the flow will select the second to last row for column Counter Email. The conditions that follow will tell it to select an email for the newest row in column Counter Email based on the previous value that was selected for the 2nd to last row.

@{last(outputs('List_rows_present_in_a_table')?['body/value'])} is what I have so far, but I do not know how to make it reference the 2nd to last row instead of just the last row. Can someone please edit that to make it reference the 2nd to last row in column "Counter Email"

Thank you,
Noah Strockbine

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (1)

Verified answer

ManishSolanki 15,091 Super User 2025 Season 2 on at

Like (1)

Report
Hi @Noah100

Pls use the below expression to get the second last record from the excel:
@{if(greaterOrEquals(length(outputs('List_rows_present_in_a_table')?['body/value']),2),take(skip(reverse(outputs('List_rows_present_in_a_table')?['body/value']),1),1),null)}

It will first check if there are 2 or more rows. If no, then it will return null. If there are 2 or more rows, then we first reverse the array & get the second element from it using 'skip' & 'take' functions. This will return the second last element from the original array.

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

Was this reply helpful? Yes No
Noah100 on at

Like (0)

Report

@ManishSolanki
That is great! Is there a way to write that so that it references the 2nd to last row for a single column? I have a column named Counter Email. I need the value for the 2nd to last row in the Counter Email column.

Was this reply helpful? Yes No
ManishSolanki 15,091 Super User 2025 Season 2 on at

Like (0)

Report
Hi @Noah100

Here you go:
@{if(greaterOrEquals(length(outputs('List_rows_present_in_a_table')?['body/value']),2),take(skip(reverse(outputs('List_rows_present_in_a_table')?['body/value']),1),1)?['CounterEmail'],null)}

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

Was this reply helpful? Yes No
Noah100 on at

Like (0)

Report

@ManishSolanki
The compose action using the expression you wrote for me gave this error
Can you help?

Was this reply helpful? Yes No
ManishSolanki 15,091 Super User 2025 Season 2 on at

Like (0)

Report
Hi @Noah100

Looks like I have made typo in column name:
@{if(greaterOrEquals(length(outputs('List_rows_present_in_a_table')?['body/value']),2),take(skip(reverse(outputs('List_rows_present_in_a_table')?['body/value']),1),1)?['Counter Email'],null)}
Pls replace the actual col name in excel with 'Counter Email' in the above expression.

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

Was this reply helpful? Yes No
Noah100 on at

Like (0)

Report

@ManishSolanki That returned with the same error unfortunately.

Was this reply helpful? Yes No
ManishSolanki 15,091 Super User 2025 Season 2 on at

Like (0)

Report
Hi @Noah100

Pls try this:
@{if(greaterOrEquals(length(outputs('List_rows_present_in_a_table')?['body/value']),2),take(skip(reverse(outputs('List_rows_present_in_a_table')?['body/value']),1),1)[0]?['Counter Email'],null)}
Note: I assume that the column name is 'Counter Email' but if not then pls change the column name from the excel file in the above expression.

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

Was this reply helpful? Yes No