Perform Join In PowerApps Collection

We have studied about Joins in SQL. What if we need to combine the data using Joins in PowerApps? Here is the solution for the same. In PowerApps we can use LookUp and Add Columns function to get the similar result like Joins in PowerApps collection. Read the article for the detailed information.

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.
 
Perform Join In PowerApps Collection
 
The “Employee Address” covers the information about – City, Address, and Employee ID.
 
Perform Join In PowerApps Collection
 
So, here, Employee and Employee Address lists are in 1 to Many(*) relationship.
 
Perform Join In PowerApps Collection
 
We want our end result something like the following screenshot.
 
Perform Join In PowerApps Collection
 
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
  1. ClearCollect(  
  2.     EmployeeCollection,  
  3.     AddColumns(  
  4.         EmployeeAddress,  
  5.         "EmployeeInfo",  
  6.         LookUp(  
  7.             Employee,  
  8.             ID = EmployeeAddress[@EmployeeID]  
  9.         )  
  10.     )  
  11. )  
Perform Join In PowerApps Collection
 
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.
  1. LookUp(  
  2.             Employee,  
  3.            ID = EmployeeAddress[@EmployeeID]  
  4.        )  
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.
 
Perform Join In PowerApps Collection
 
You can see here, a new column with “EmployeeInfo” has been added.
 
Perform Join In PowerApps Collection
 
Perform Join In PowerApps Collection
 
Now, create a Gallery and apply the value from the collection.
 
Perform Join In PowerApps 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!!