Skip to main content

Notifications

Community site session details

Community site session details

Session Id :
Power Automate - Building Flows
Unanswered

Excel get rows - I only want the last x rows

Like (1) ShareShare
ReportReport
Posted on 24 Aug 2024 10:49:41 by 22
Hi,
I have an excel file with data in it.  I read the file using a GET ROWS and this returns around 250 records.  I am only interested in the last 30 each time i run the flow.  Ive tried several variations of a filter array and each one throughs an error.
 
@take(skip(body('Get_All_DATA')?['body/value'], variables('iStartIndex')), 30)

@take(skip(body('Get_All_DATA'), variables('iStartIndex')), 30)

@take(skip(outputs('Get_All_DATA')?['value'], variables('iStartIndex')), 30)
 
 
iStartIndex is the total number of rows minus 30.  this gives me the startpoint.  However i just get errors like 'The template language function 'skip' expects its first parameter 'collection' to be an array or a string. The provided value is of type 'Null'.   On others i just get filter array error and it lists the data returned but does not say what the error is.
 
What am i doing wrong or is there a better way?  I can't use get rows and enter a query due to the random data held
 
  • Suggested answer
    David_MA Profile Picture
    10,874 Super User 2025 Season 1 on 26 Aug 2024 at 19:21:38
    Excel get rows - I only want the last x rows
    Try this:
    1. Add a column to your spreadsheet named "Row" and populate it with this function: =ROW()
      1. This will populate it with the current row number
    2. ​​​​​​​Then configure the List rows present in a table with Order by set to Row desc
    3. And Top Count to 30
  • lbendlin Profile Picture
    7,834 Super User 2025 Season 1 on 26 Aug 2024 at 00:16:04
    Excel get rows - I only want the last x rows
    "I am only interested in the last 30"
     
     
    That's a very costly ask.  Consider running a SQL query against the Excel table so you can let Excel do the work for you.
     
  • creativeopinion Profile Picture
    10,411 Super User 2025 Season 1 on 25 Aug 2024 at 23:16:41
    Excel get rows - I only want the last x rows
    I'm assuming when you are saying GET ROWS that you are using the List Rows Present in a Table action. If this is the case, this action returns up to 256 rows by default. In order to get all rows, you'll need to toggle on pagination. This means that if your table has more than 256 rows and you don't toggle on pagination it will only return the first 256 rows of your table. 
     
    If possible, it's always best practice to filter out your rows of data by defining a Filter Query. Note: This action will only take a single condition. If you need to filter by more than one condition—use a Filter Array action. Not sure how to use a Filter Array action? I've linked a tutorial at the bottom of this post that you might be interested in. 
     

    Troubleshoot Your Expression

    The error you are getting is saying that the variable you are using is null.

    Return Count of Rows

    The skip() function requires the second parameter to be an integer (aka a number).
    In your case, you don't need a variable. You can simply use a Compose action. Tip: Rename your actions to keep your flow organized. This is especially helpful when using multiple instances of the same action.
     
    Note: If you are using a Filter Array action (you'll need to use the length() function on the output of the Filter Array action and NOT the Value dynamic content of the List Rows Present in a table action. 
     
    In the length() function, insert the value dynamic content from the List Rows Present in a table action. 
    Run a test. Review the outputs. It's easier to run tests and troubleshoot your flow as you build it rather than building your entire flow and trying to troubleshoot it after you've completed it.
     
    Ensure the Compose action is outputting the number or rows you are expecting. 
     

    Starting Index

    To return the starting index you'll need to use the sub() function. You can combine this with the expression in the Compose action above. However, to help you better understand the logic of the flow, insert another Compose action. 
     
    Use the sub() function. The sub() function takes two numbers and subtracts one from the other. 
     
    For the first parameter, insert the output from the Compose action above (which is the count of rows returned from the List Rows Present in a table action). 
    Add a comma and insert the second parameter. In your case it's 30.
    Run a test. Review the outputs. Ensure that the output is what you are expecting as the starting index for the last 30 rows. For example, if your excel table has 300 rows, less 30, the output should be 270.
     

    Get Last 30 Rows

    Add another Compose action. Use this Compose action to output the last 30 rows. Use the skip() function. The skip() function takes two parameters:
    skip([array],[integer])
    For the first parameter, insert the value dynamic content from the List Rows Present in a table action. If you are using a Filter Array—insert the body output from that action. 
     
     
    For the second parameter, insert the output from the Compose action above (aka the starting index). This expression will skip the number of rows you've specified in the second parameter. For example, if your Excel table has 300 rows, the skip() function will skip the first 270 rows.
    Run a test. Review the outputs. This expression should return the last 30 rows.

    For more tips on using expressions, you might be interested in this YT Tutorial: 7 Functions You Need to Know | ️Expression Essentials: Part 1


    In this section, I'll cover how to get dynamic content with an expression when the dynamic content you need isn't listed in the dynamic content menu.

    In the full tutorial I cover 7 functions you need to know when getting started with expressions.

    1️⃣ empty()

    2️⃣ coalesce()

    3️⃣ equals()

    4️⃣ if()

    5️⃣ concat()

    6️⃣ length()

    7️⃣ split()


    I cover how to use these functions in expressions and I’ll also cover common mistakes when it comes to writing expressions and show you a few tips and tricks along the way.

    As a beginner or even an intermediate flow builder—expressions can seem a bit complex at first, I’m going to try to simplify it for you. If you want to level up your flows by writing expressions—keep watching!


    IN THIS VIDEO:

     What is an Expression?

     What is a Function?

     What Does Wrapping a Function Mean?

     How Do I Insert an Expression?

     How to Use a Compose action

     How to Navigate the Expression Builder with Arrow Keys

     How to use the Expression Tooltip

     Common Mistakes When Writing Expressions

     How to differentiate a null from an empty string

     How to Get Dynamic Content When it’s Not Listed

     How to Use a Get Item Action to Verify Dynamic Content Output

     How to Convert Strings to Lower Case

     How to Troubleshoot the if() Function

     
    Hope this helps!

    Consider giving me a ❤️ if you liked my response!

    👉 Level up your Power Automate skills by checking out my tutorials on YouTube
    👉 Tips and Tricks on TikTok and Instagram

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

🌸 Community Spring Festival 2025 Challenge Winners! 🌸

Congratulations to all our community participants!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Power Automate - Building Flows

#1
stampcoin Profile Picture

stampcoin 105

#2
Michael E. Gernaey Profile Picture

Michael E. Gernaey 86 Super User 2025 Season 1

#3
David_MA Profile Picture

David_MA 62 Super User 2025 Season 1

Overall leaderboard