Do you need the last row from your Excel table in your Power Automate workflow?

When you use the List rows present in a table action for the Excel connector, you will notice it returns all the data in your table and there is no option to get only the last row.

There are a few techniques that can be used to get the last row from your table depending on your situation. This post will show you all the ways you can get the last row in Excel.

Get the Last Row from an Excel Table with the Last Function

One way to get the last row of an Excel table is to get all the rows and then use the last function to get the last record in the array returned from the table.

Follow these steps to get the last row in Excel with the last function.

1. Add the List rows present in a table action to your flow.
2. Select the Location of the file.
3. Select the Document Library the file is in.
4. Select the File.
5. Select the Table within the file.

This will get all the rows and you can now use the last function to access the last row.

@{last(outputs('List_rows_present_in_a_table')?['body/value'])}

6. Add the above formula anywhere you want to return the last row.

⚠️ Warning: The List rows present in a table action will only return the first 256 rows of the table by default. This means if your table has more than 256 rows, you won’t get the last row. You will only get the last row of the first 256 rows.

You can adjust the settings for the List rows present in a table action so that it will return more than only 256 rows.

7. Click on the Ellipses icon at the right of the List rows present in a table action.
8. Select the Settings option from the menu.

9. Toggle the Pagination option to On.
10. Add a large number such as 100000 to the Threshold input.
11. Press the Done button.

Now the List rows present in a table action will return more than 256 rows and then the last function will be able to get the last row in Excel from this.

⚠️ Warning: This is very inefficient and could make for a really slow flow. This also may not work when you have a large Excel table with too many rows.

Get the Last Row from an Excel Table with an Order By and Top Count Query

There is another more efficient strategy you can use if your data table in Excel contains a serial number order.

=ROW()-ROW(Orders[[#Headers],[ID]])

For example, the above Excel formula can be used in your table to automatically create a row numbering for your table where Orders is the table name and ID is the column name of your row numbers.

You can use an Order By query to sort this in descending order and then use the Top Count to return only the 1st row.

When sorted in descending order the first row is actually the last row of the table!

Follow these steps to get the last row with the Order By and Top Count.

1. Add the List rows present in a table action then select the file location and table as seen previously.
2. Click on the Show advanced options link at the bottom of the List rows present in a table action. This will reveal more settings for the action.

3. Add the query ID desc in the Order By field.
4. Enter the Top Count as 1.

The Order By query will reverse the order of the data based on the ID column.

This means the last column will become the first. When you take the Top Count of 1, this only returns the last row.

Get the Last Row from an Excel Table with a Filter Query

The final option for getting the last row from your Excel table is to use a Filter Query.

But this will require adding a new calculated column to your Excel table.

=IF(ROW()=MAX(ROW(Orders)),"Y","N")

The above formula will identify the last row in your table and return Y if it’s the last row and N otherwise. Where Orders is the name of the table.

The formula is dynamic, so will update whenever new data is added to the table.

Follow these steps to get the last row with the Filter Query.

1. Add the List rows present in a table action then select the file location and table as seen previously.
2. Click on the Show advanced options link.
3. Add the query Last eq 'Y' in the Filter Query field where Last is the name of the column in your Excel table that contains the formula identifying the last row.

That’s it! When you run the flow, it will only return the last row in your Excel table.

This is the most efficient way to get the last row.

Conclusions

The most recent data in your Excel table is usually in the last row, so getting the last row from Excel will be a common requirement.

Unfortunately, there isn’t a bottom count feature in the Excel connector to get only the last few rows.

There are still ways to get the job done. The last function is useful but not very efficient for larger tables.

If you have a row number field in your table then you can use the Order By query and Top Count to effectively get the last row.

Otherwise, you can add a calculated count to your table to identify the last row and then use a Filter Query to return only this row.

Did you ever need to get the last row from Excel? How did you do it? Let me know in the comments!