Tuesday 29 October 2019

Getting around the 256 row limit in the MS Flow Excel Action

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.

Migrating Multiple Teams between 2 tenants from a list of URL's using ShareGate PowerShell.

If you are lucky enough to have a license to ShareGate, you may find yourself using the Copy Team functionality. The Desktop App is pretty g...