Working with Datasets in Power Automate
As organizations look to improve efficiencies by leveraging robotic process automations, many are adopting Microsoft’s Power Automate to support their automated workflows and data pipelines. We have started building the ArcGIS Connectors for Power Automate to help extend the Flows to leverage your geospatial content.
The biggest consideration to be aware of when starting to build Flows in Power Automate using the ArcGIS Connectors is record count. Record count is important for a few reasons:
- HTTP request time limits in Flows
- Maximum of 5000 iterations the ‘Do until’ control can handle
- Getting consistent results
Power Automate has HTTP request limits and timeouts for connector operations that take longer than two minutes. Geospatial can have thousands of records and when working with complex geometry, the size of the data grows, and processing can take a while. To prevent the connectors from timing out, the ArcGIS and ArcGIS PaaS connectors have been built with 150 record count limits to help prevent timeouts from occurring.
As we know, many datasets we work with have more than 150 features, but the ‘Do until’ control allows us to iterate through the data in 150 record chunks.
There are a few steps to getting the ‘Do until’ control set up to step through your dataset:
- Initialize a variable to track the offset as the Flow iterates through the records
- Wrap the logic for each record in a ‘Do until’ control
- Update the offset variable with the current offset of the record you are working with
This pattern will allow the flow to iterate through the records with less concern of the control timing out.
For those new to programming concepts, below are a few resources to provide some additional understanding:
One thing to be aware of, the ‘Do until’ control does have a maximum number of iterations that it will run. By default, the control is set to run 60 times. The count is configurable and can be set to run a maximum of 5000 times. For other configuration options and design considerations, check Microsoft’s documentation.
Finally, record count is an important consideration for consistent of results. In our testing, we have seen discrepancies in the result counts when working with large datasets and have found working with data that is 5000 records or less has much more consistent results.
To summarize:
- Record count and data size is a VERY important consideration when building your Flows in Power Automate.
- Use the ‘Do until’ control to facilitate the iteration through the individual records.
- Configure the ‘Do until’ count limit to support the number of records you expect to work with each time the Flow is run.