Home / Power Automate Tutorial / Day 7
Day 7 — Filter array, Select and working without loops
The single biggest performance win in Power Automate: stop looping.
Filter array
Give it an array and a test. It gives back the items that pass, in one action.
{
"trigger": { "type": "manual", "body": {} },
"actions": [
{ "name": "Orders", "type": "Compose", "input": [
{ "Product": "Laptop", "Region": "North", "Units": 3, "Price": 52000 },
{ "Product": "Monitor", "Region": "South", "Units": 7, "Price": 9500 },
{ "Product": "Keyboard", "Region": "North", "Units": 12, "Price": 1200 },
{ "Product": "Mouse", "Region": "East", "Units": 20, "Price": 650 },
{ "Product": "Printer", "Region": "North", "Units": 4, "Price": 14500 }
] },
{ "name": "North only", "type": "FilterArray",
"from": "@outputs('Orders')",
"where": "@equals(item()?['Region'], 'North')" },
{ "name": "How many", "type": "Compose",
"input": "@length(outputs('North only'))" }
]
}
Three of the five are North. No loop, one action.
Two tests at once
{
"trigger": { "type": "manual", "body": {} },
"actions": [
{ "name": "Orders", "type": "Compose", "input": [
{ "Product": "Laptop", "Region": "North", "Units": 3, "Price": 52000 },
{ "Product": "Monitor", "Region": "South", "Units": 7, "Price": 9500 },
{ "Product": "Keyboard", "Region": "North", "Units": 12, "Price": 1200 },
{ "Product": "Mouse", "Region": "East", "Units": 20, "Price": 650 },
{ "Product": "Printer", "Region": "North", "Units": 4, "Price": 14500 }
] },
{ "name": "Big North orders", "type": "FilterArray",
"from": "@outputs('Orders')",
"where": "@and(equals(item()?['Region'],'North'), greater(mul(item()?['Units'], item()?['Price']), 20000))" },
{ "name": "Result", "type": "Compose", "input": "@outputs('Big North orders')" }
]
}
North and over 20,000: Laptop is 156,000 and Printer is 58,000, but Keyboard is only 14,400. So two survive.
Select - reshape every item
Select maps each item to something new. Use it to pull one field out, or to rebuild the objects into a different shape.
{
"trigger": { "type": "manual", "body": {} },
"actions": [
{ "name": "Orders", "type": "Compose", "input": [
{ "Product": "Laptop", "Region": "North", "Units": 3, "Price": 52000 },
{ "Product": "Monitor", "Region": "South", "Units": 7, "Price": 9500 },
{ "Product": "Keyboard", "Region": "North", "Units": 12, "Price": 1200 }
] },
{ "name": "Just names", "type": "Select",
"from": "@outputs('Orders')",
"map": "@item()?['Product']" },
{ "name": "As a sentence", "type": "Compose",
"input": "@join(outputs('Just names'), ', ')" },
{ "name": "Reshaped", "type": "Select",
"from": "@outputs('Orders')",
"map": {
"Item": "@item()?['Product']",
"Value": "@mul(item()?['Units'], item()?['Price'])",
"Zone": "@toUpper(item()?['Region'])"
} },
{ "name": "Values only", "type": "Select",
"from": "@outputs('Orders')",
"map": "@mul(item()?['Units'], item()?['Price'])" }
]
}
Select is how you build a clean table
Data out of SharePoint or Dataverse arrives with thirty fields you do not want, all with ugly internal names. One Select turns it into exactly the four columns your report needs, with the names you want on them. It is the tidiest thing in Power Automate.
Totalling without a loop
Select the numbers out, then add them up. There is no sum() function in Power Automate, which surprises people - but you can do it in one Compose with a small trick, or keep it readable with a short loop over the far smaller array.
{
"trigger": { "type": "manual", "body": {} },
"actions": [
{ "name": "Orders", "type": "Compose", "input": [
{ "Product": "Laptop", "Units": 3, "Price": 52000 },
{ "Product": "Monitor", "Units": 7, "Price": 9500 },
{ "Product": "Keyboard", "Units": 12, "Price": 1200 }
] },
{ "name": "Line values", "type": "Select",
"from": "@outputs('Orders')",
"map": "@mul(item()?['Units'], item()?['Price'])" },
{ "name": "Init total", "type": "InitializeVariable", "variable": "total", "value": 0 },
{ "name": "Add them", "type": "ApplyToEach",
"from": "@outputs('Line values')",
"actions": [
{ "name": "Add", "type": "IncrementVariable", "variable": "total", "value": "@item()" }
] },
{ "name": "Total", "type": "Compose", "input": "@variables('total')" },
{ "name": "Biggest", "type": "Compose", "input": "@max(outputs('Line values'))" },
{ "name": "Smallest", "type": "Compose", "input": "@min(outputs('Line values'))" }
]
}
156,000 + 66,500 + 14,400 = 236,900. max() and min() work straight off an array of numbers, which saves a loop when that is all you need.
The array functions
@length(variables('list'))
@first(variables('list'))
@last(variables('list'))
@join(variables('list'), ' | ')
@contains(variables('list'), 'SQL')
@union(variables('list'), variables('list'))
@take(variables('list'), 2)
@skip(variables('list'), 2)
@max(variables('nums'))
@min(variables('nums'))
@range(1, 5)
union() is how you remove duplicates
union(list, list) - the same array twice. Union merges two arrays and drops duplicates on the way, so unioning a list with itself just removes its own duplicates. "Excel" appears twice in the list above and once in the result. It is a hack, and it is the accepted way to do it, because there is no distinct action.Grouping - counting per region
There is no group-by action. This is how it is actually done: get the distinct values, then filter once per value.
{
"trigger": { "type": "manual", "body": {} },
"actions": [
{ "name": "Orders", "type": "Compose", "input": [
{ "Product": "Laptop", "Region": "North", "Value": 156000 },
{ "Product": "Monitor", "Region": "South", "Value": 66500 },
{ "Product": "Keyboard", "Region": "North", "Value": 14400 },
{ "Product": "Mouse", "Region": "East", "Value": 13000 },
{ "Product": "Printer", "Region": "North", "Value": 58000 }
] },
{ "name": "All regions", "type": "Select",
"from": "@outputs('Orders')", "map": "@item()?['Region']" },
{ "name": "Distinct regions", "type": "Compose",
"input": "@union(outputs('All regions'), outputs('All regions'))" },
{ "name": "Init summary", "type": "InitializeVariable", "variable": "summary", "value": [] },
{
"name": "Each region",
"type": "ApplyToEach",
"from": "@outputs('Distinct regions')",
"actions": [
{ "name": "Rows for this region", "type": "FilterArray",
"from": "@outputs('Orders')",
"where": "@equals(item()?['Region'], items('Each region'))" },
{ "name": "Record it", "type": "AppendToArrayVariable", "variable": "summary",
"value": {
"Region": "@items('Each region')",
"Orders": "@length(outputs('Rows for this region'))"
} }
]
},
{ "name": "Summary", "type": "Compose", "input": "@variables('summary')" }
]
}
North 3, South 1, East 1. Note the loop runs three times, not five - once per distinct region rather than once per row. That is the pattern: loop over the small thing, filter the big one.
Inside the loop, item() is ambiguous
The Filter array inside "Each region" has its own
item() - the order being tested. To reach the region we are looping over, you must say items('Each region'). Get this wrong and the filter compares each order to itself, which quietly returns everything.Try these yourself
- Filter the orders down to a single region and count them.
- Use Select to produce an array of just the product names, then join it into a sentence.
- Reshape the orders into objects with Item, Value and Zone.
- Get the distinct list of products using union.
- Explain why Filter array is faster than an Apply to each with a Condition inside it.