To compare all Records in one Collection to another, you have to Concatenate() the data into one column.
Then you just compare the whole "concatenated" data set using the in operator.
Copy and paste this YAML into a Power App. Or scroll below to copy the individual functions.
The in operator:
Docs: PowerFx Docs
The in operator is used to match data contained in a collection, table or string.
You cannot compare multiple "records" in a collection to another collection.
With the in operator, concerning collections, you can do the following:
single record | | A single record is compared to a collection of records. |
multiple values in a column | | Multiple values in the column |
Because of this limitation we have to use a workaround to compare multiple records.
The AddColumns() function:
Docs: AddColumns() PowerFx
Used to add a column on the fly.
The syntax is:
AddColumns(collection_name, new_column_name, <logic or static data>, next_new_column_name, <logic or static data>...)
The Concatenate() function:
Docs: Concatenate() PowerFx
Used to combine strings. You can combine multiple records values using the & (the concatenate operator).
The syntax is:
Concatenate("string 1", "string 2", "string 3")
(returns: string 1string 2string3)
Concatenate("string 1" & " " & "string 2" & " " & "string 3")
(returns: string 1 string 2 string 3
Let's combine these functions to compare multiple records:
Concatenate is best used if you need to compare specific collections in a column. There is a faster and dynamic way to handle this filter feature using JSON()
Let's make it easier using JSON!
Your collections have the same column names for this method.
The JSON() function:
Docs: JSON() PowerFx
Super useful for working with collections. This function turns any collection into JSON.
The ThisRecord operator:
Docs: ThisRecord PowerFx
This is a way to reference a Record within the current Scope.
Example:
//FYI this is psuedo code.
// Collection of records
collection_1:
[
{ID:2,One: "a", Two: "b"},
{ID:3,One: "a", Two: "b"},
{ID:4,One: "a", Two: "b"},
{ID:5,One: "a", Two: "b"},
{ID:6,One: "a", Two: "b"}
]
// Iterate through each record and print as JSON
ForAll(collection_, print(JSON(ThisRecord)))
// Expected Output:
{"ID":2,"One":"a","Two":"b"} -- first line of output
{"ID":3,"One":"a","Two":"b"} -- second line of output
{"ID":4,"One":"a","Two":"b"} -- third line of output
{"ID":5,"One":"a","Two":"b"} -- fourth line of output
{"ID":6,"One":"a","Two":"b"} -- fifth line of output
So the Scope in this psuedo code is all Records stored in collection_1.