As Power Automate cloud flows grow in complexity, makers often need to nest loops within loops. Nesting loops isn’t bad, but nesting the same loop more than once is inefficient. An alternative approach, loop the content once without nesting it and build an array of objects, which creates a lookup table that can be referenced throughout the cloud flow.

Now, why are lookup tables useful?

• [ 1 ] The source data is queried just once. For every query, there is a data request and a data response with a data payload. Larger the dataset, larger the data payload, which requires additional resources for each subsequent data query, so with a lookup table array, there isn’t a need for multiple data queries.

• [ 2 ] The lookup table array can be personalized. Data sources usually have more fields than are needed for the flow, but lookups can be formatted and populated to present differently than the data source. Imagine the source having fields for “First Name” and “Last Name”. The flow maker can choose to keep these fields unique or concatenate them into a single property called “Name”, without touching the source data.

Okay, lookup arrays are more efficient. Are they difficult to work with? Not really. Working with an array of objects isn’t drastically different than working with a single object variable:

variables('object')['ID']

item()?['ID']

The notable difference is building the lookup array. The cloud flow needs the Compose control to build a data object, then the output of that action is appended to an array variable. Once the array is populated, use the Filter array control to return the necessary object:

Compose controls are clever enough to recognize data types. Booleans, strings and numbers are valid when building data objects, which can be appended to the array manually or within a loop:

With the array populated with data objects, use the Filter array control and specify a property to filter against, like “Name”:

item()?['Name']

One thing to keep in mind, the Filter array control also outputs an array. To reference object properties of this output array, the flow will need to first grab the array item using an index before working with the object’s property:

Assuming a single array item was returned, use the first() expression to grab the record:

first(body('Filter_array'))

And to return the property value of the first() record, use the property name, like “ZipCode”:

first(body('Filter_array'))['ZipCode']

Conclusion:
Arrays are great as lookup tables. The objects are customized, and the tables can be used throughout the cloud flow as many times as necessary with the Filter array control.

“Everything can be explained to the people, on the single condition that you want them to understand.”

Frantz Fanon

#BlackLivesMatter