Hello everyone and welcome to my blog. In today’s blog I will show how you can fetch more than 256 rows in from excel file in Power Automate.
Through my blog I share interesting tips and discuss on latest releases of Microsoft.NET technologies, Dynamics 365 and Power Platform, SharePoint and Client scripting libraries. Please subscribe to my blog to stay updated.
Let’s discuss the problem here. I have an excel file in my One-drive for Business with more than 100K users. I use the ‘List rows present in a table‘ action of the Excel Online (Business) connector to fetch the data from excel. Illustration below for reference.
However, when I execute the flow, I get only the first 256 rows.
One argument can be that we can iterate through all the records in the excel using paging. We do get the @odata.nextLink in the response which can be used to fetch the next set of records.
That however make the flow complex and what if you just have 1500 records to fetch and for that taking all these hassles is certainly not warranted.
Well not to worry. While not intuitive, the action ‘List rows present in a table‘ indeed has a setting to fetch more than 256 records at one go. To enable this, navigate to the Settings of the connector.
Enable Paging and then set the number of records you want to fetch in a single call in the Threshold value.
Make sure to click on Done before you exit the Settings dialog.
You are all set. Now when you run the flow, you should be able to fetch more than 256 records from the excel in a single call in Power Automate.
You might be wondering that I have set the Threshold value to 2000 but the result is showing 2048 records fetched. This is because results are retrieved in sets of 256 only. So be mindful of the fact that the results returned may be higher in number than the Threshold value you have specified.
Hope this helped. For similar interesting topics on Microsoft.NET and Power platform, you can subscribe to my blog.
Debajit Dutta
Business Solutions MVP