Power Automate: intersection Function

Power Automate has a handy collection function called "intersection" that returns only the items that are common across all the arrays you provide. If you've ever needed to find what two or more lists share in common, this is the function for the job. Think of it like a Venn diagram, the "intersection" function gives you only what's in the overlapping part.

Usage

It follows a simple pattern.

1. First collection (array)
2. Second collection (array)
3. (Optional) Additional collections

You can pass two or more arrays, and the function returns a new array containing only the items that appear in every single one of them. You'll often see it used with the "createArray" function to build the arrays inline.

Here's a basic example with two arrays of numbers:

intersection(createArray(1, 2, 3), createArray(2, 3, 4))

will return

[2, 3]

Only 2 and 3 appear in both arrays, so those are the items returned.

Multiple arrays

You're not limited to two arrays. You can pass as many as you need:

intersection(createArray(1, 2, 3, 4), createArray(2, 3, 4, 5), createArray(3, 4, 5, 6))

will return

[3, 4]

Only 3 and 4 appear in all three arrays.

With strings

It works the same way with string values:

intersection(createArray('Tom', 'Hardy', 'Harry', 'John'), createArray('Tom', 'Maria', 'Harry', 'Campbell'))

will return

['Tom', 'Harry']

With objects

When you pass arrays of objects, the "intersection" function compares all properties of each object. Two objects are only considered a match if every single property is identical. Here's an example:

intersection(
  createArray(
    json('{"name":"Tom","age":30}'),
    json('{"name":"Harry","age":25}')
  ),
  createArray(
    json('{"name":"Tom","age":30}'),
    json('{"name":"Maria","age":28}')
  )
)

will return

[{"name":"Tom","age":30}]

The Tom object appears in both arrays with the exact same properties, so it's included.

Edge Cases

Objects must match completely

This is probably the most common frustration with the "intersection" function. When comparing arrays of objects, every property must match exactly. If two objects share the same name but differ in any other property, they won't be considered a match.

Duplicate handling

If duplicates exist in the input arrays, the "intersection" function returns each common item only once. You won't get repeated values in the result even if the same item appears multiple times in the source arrays.

Empty arrays

If any of the input arrays is empty, the result will always be an empty array. There can't be common items if one of the lists has nothing in it.

Case sensitivity

String comparisons in the "intersection" function are case-sensitive. "Tom" and "tom" are treated as different values. If you need case-insensitive matching, convert all values to the same case first using the "toLower" function or the "toUpper" function.

Limitations

No property-specific comparison

As mentioned above, the "intersection" function compares entire objects, not individual properties. There's no parameter to say "compare only by this field." If you need that behavior, you'll need to restructure your data first.

Arrays only

The function works with arrays only. You cannot mix arrays and objects as parameters, all parameters must be the same type.

No "except" function

Power Automate doesn't have a built-in "except" function to find items in one array that aren't in another. But you can combine "intersection" with a Filter Array action to achieve this. First, find the common items with "intersection," then filter them out from either array.

Recommendations

Here are some things to keep in mind.

Simplify your arrays before comparing

If you're working with arrays of complex objects and only care about matching on one property, use a Select action to extract that property into a simple array first. Then run the "intersection" function on the simplified arrays. This avoids the "all properties must match" limitation.

Combine with length to check for overlap

If you just need to know whether two arrays share any items at all, wrap the "intersection" function with the "length" function:

length(intersection(createArray(1, 2, 3), createArray(4, 5, 6)))

will return

0

A result of 0 means no common items. Anything greater than 0 means there's overlap.

Use it instead of loops for performance

The "intersection" function is significantly faster than using an Apply to Each loop to compare arrays manually. Tests with arrays of around 30,000 objects completed in about 2 seconds using built-in functions, compared to minutes (or timeouts) with loops.

Always add a comment

Adding a comment will help others understand your formula. Indicate what the function is doing and why, especially if the expression is complex.

Final Thoughts

The "intersection" function is a clean and efficient way to find common items between arrays in Power Automate. It shines with simple arrays but requires some extra preparation when working with complex objects. Simplify your data first, and you'll get exactly the overlap you need without any loops.

Sources

Microsoft's "intersection" Function Reference

Back to the Power Automate Function Reference.

Photo by Denys Nevozhai on Unsplash