If you find yourself using the "List rows present in a table" Excel function within Microsoft Flow, you may hit an issue where it can only return the first 256 rows of the table.
I suspect that this limit may be in place for performance reasons, so please proceed with the following steps with caution.
Here are the steps if you want to loop through more than 256 rows:
Initialize a variable called "TotalRows". This should be an "Integer" type and the default value should be 256.
Initialize a variable called "SkipCount". This should be an "Integer" type and the default value should be 0.
Create a "Do until" action. The condition should be to loop until the TotalRows is less than 256.
Add your "List rows present in a table" Excel action inside the Do Until from the previous step. Set the required settings as required. In the "Skip Count" field, add the "SkipCount" variable you created in step 2.
Under the "List rows present in a table" action, add a new "Set variable" action and set the TotalRows variable to the amount of rows returned from the previous action. To do this, use the following expression: length(body('List_rows_present_in_a_table')?['value'])
Next add a "Increment variable" action and increment the "SkipCount" variable by 256.
The idea here is to loop through the Excel 256 rows at a time.
Every time you loop, you skip an extra 256 rows (so first time it will skip 0, then 256, then 512 etc).
The Do Until just checks to see if there are less than 256 rows returned, because if that is true you know you are at the end of the Excel file.
All actions that you want to perform on your Excel data should be done inside this "Do until" loop.
Thanks for this David, saved me hours of trial and error.ReplyDelete
Very clever - thank you!ReplyDelete
Some really great info , Gladiola I found this. Excel spreadsheet consultingReplyDelete
Thank for the idea !!ReplyDelete
Thank you so much Gumbleton, you saved my job. :)ReplyDelete