Perform Join In PowerApps Collection

Overview

 

In this article, we will talk about the Join Operation within the PowerApps. Sometimes, there is a situation in which we need to combine the data of two lists and need to represent them into a single gallery.

 

In this article, we will perform the same with the help of Collection and real-life use case and scenario.

 

So, now let’s get started!

 

Real-Life Scenario

 

We have the following two lists.

• Employee
• EmployeeAddress

The “Employee” table covers the information about – Employee Name, designation, and ID.

 

 

The “Employee Address” covers the information about – City, Address, and Employee ID.

 

 

So, here, Employee and Employee Address lists are in 1 to Many(*) relationship.

 

 

We want our end result something like the following screenshot.

 

 

Here, we want all the addresses of the Employee.

 

In order to achieve that, we will use the following two functions.

• AddColumns
• LookUp

AddColumns

 

This function is used to add the additional column in the collection

 

LookUp

 

It is used for a condition to evaluate for the rows in the specified input. In short, when we have a relationship between the tables and we wish to retrieve any value from another table, at that time this function is used.

 

Let’s add the following formula

ClearCollect(
    EmployeeCollection,
    AddColumns(
        EmployeeAddress,
        "EmployeeInfo",
        LookUp(
            Employee,
            ID = EmployeeAddress[@EmployeeID]
        )
    )
)

 

Here, Name of Collection = Employee Collection,

 

We need all the address of Employee Address and need Employee Information from the Employees list.

 

So, in AddColumns function,

• Source Table = Employee Address = We want all the address from this table.
• Column Name = Name of the New Merged Column = Any name you can give. Here, we took a name “EmployeeInfo”.
• We want EmployeeInfo from Employee table.

So, we will LookUp the function.

LookUp(
            Employee,
           ID = EmployeeAddress[@EmployeeID]
       )

In the above function, we look up into Employee list, to match ID column of Employee list to Employee ID column of EmployeeAddress.

Now, let's run the code and check the collection.

 

 

You can see here, a new column with “EmployeeInfo” has been added.

 

 

 

Now, create a Gallery and apply the value from the collection.

 

So, this is how we can combine the two collections and show them to a single gallery.

 

Conclusion

 

Hope you love this article! Stay connected with me for the amazing articles!

 

Happy PowerApping!!