[Power Apps] Creating a Full Outer Join Between Collections in Canvas App

Level of Difficulty: Intermediate – Senior.

To join or not to join! Let’s delve deep into the realm of data manipulation by introducing the concept of a “full join” and how to implement it between two collections in Power Apps. If you come from a database background, the term ‘full join’ might be familiar. For those who aren’t in the know, let’s break it down.

When you have two datasets (collections in Power Apps) that contain data, you may have a scenario where you need to extract data from both sets, depending on the inclusion and exclusion criteria of your scenario. In the image above, the blue represents the data you would like to extract based on the criteria you are using and the nature of the join you are executing.

In this context, to join two tables (or collections) in Power Apps to collect all data from both collections, you’d be executing a “full outer join” equivalent query to collect the joining of two collections (col_Source and col_Destination – on the Id column) into one big collection (col_FullOuterJoin) where you can manipulate and update the columns needed in the joined collection (col_FullOuterJoin).

Here’s the code:

Collect(col_FullOuterJoin,
    // Iterate through all source records and join to destination collection
    ForAll(

        // Iterate through the source collection
        col_Source,
        {
            // Create a item for each mapped source and destination record
            // Find the corresponding destination record
            col_Destination: Filter(col_Destination, Id = ThisRecord.Id),
            col_Source: ThisRecord,

            // Columns to appear in dataset
            Code: ThisRecord.Code,
            Name: ThisRecord.Name,

            // Conditioned Columns - Use logic from one collection to set the value
            Status: If(
                    (ThisRecord.RiskScore)<=100,"Low",
                    (ThisRecord.RiskScore)>100 And (ThisRecord.RiskScore)<150,"Medium",
                    (ThisRecord.RiskScore)>=150,"High")
        }
    )
);

The join above should result in the following: