Referencing Collection Columns in PowerApps

Alfwyn Jordan, 08 March 2019

When using PowerApps it is quite easy to work with and refer to individual columns in a collection. For example, if we have a collection of fruit, created using the following code:
     Collect(Fruit, {ID:“1”, Type:“Apple”, Colour:“Green”}, {ID:”2”, Type:”Lemon”, Colour:”Yellow”}, {ID:”3”, Type:”Banana”, Colour:”Yellow”}, { ID:”4”, Type:”Plum”, Colour:”Red”}, { ID:”5”, Type:”Orange”, Colour:”Orange”}, { ID:”6”, Type:”Grapes”, Colour:”Green”})

We can pass the individual columns to a function and then evaluate the function, say a Filter for yellow fruit:
     Filter(Fruit, “yellow” in Lower(Colour) )

   
So far so good, but what if we wanted a collection of fruit filtered on the values in a second collection, say for this example, of just colours:
     ClearCollect(Colours, {Colour: “Orange"},{Colour: "Yellow"})   

A good starting point would be to loop through all the values in our Colour collection and add the filtered results to a new collection:
     ForAll(Colours, Collect(SpecificFruit,(Filter(Fruit, ‘Colour’ in ‘Colour’)))

Our new collection of SpecificFruit looks identical to the initial collection. PowerApps has limited the scope of ‘Colour’ to the collection Fruit as that is the data source for the filtering function.

The solution is to use the @ disambiguation operator. This allows us to remove any scoping issues and make it explicit which column we are referencing.
     ForAll(Colours, Collect(SpecificFruit,(Filter(Fruit, Colours[@’Colour’] in ’Colour’)))

This results in the following collection:

We have successfully filtered one collection using the column of another collection in PowerApps.