cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Deepshikha21
Frequent Visitor

Merge Two collections

Hello,

I have parent child gallery in my app due to all the filters and logic I end up having two different collection . One collection is having Parent gallery data and Other collection is having child gallery data. I need to merge this two collection and need one collection as final. It will be one to many relationship. 

I tried merging using FOR All but I am getting table values from child collection.

Below are the two tables I need to merge one basis of common column i.e. KPI name in my case-

 

Deepshikha21_0-1623917167584.png

Deepshikha21_1-1623917184516.png

The formula I am using to merge this is -

Clear(collFinalData);
 
ForAll(collFy,
Collect(collFinalData,
Add Columns(
collKPIDetails,
"ActualValue",
Filter(collFy,KPIName = collKPIDetails[@KPIName],ActualValue).ActualValue,
"ValueYear",
Filter(collFy,KPIName=collKPIDetails[@KPIName],ValueYear).ValueYear,
"ValueQuarter",
Filter(collFy,KPIName=collKPIDetails[@KPIName],ValueYear).ValueQuarter,
"ReportedDate",
Filter(collFy,KPIName=collKPIDetails[@KPIName],ValueYear).ReportedDate,
"FinancialYear",
Filter(collFy,KPIName=collKPIDetails[@KPIName],ValueYear).FinancialYear
)
)
)

 

1 ACCEPTED SOLUTION

Accepted Solutions
CNT
Super User
Super User

@Deepshikha21 I've made a sample with some simple column names. You can follow the same pattern and use it with your collections and column names.

 

ClearCollect(col1, {Name:"Tom",ID:1}, {Name:"Mark",ID:2});
ClearCollect(col2, {Name:"Tom", Score:10, Rank:1}, {Name:"Tom", Score:20, Rank:3},{Name:"Mark", Score:15, Rank:2},{Name:"Mark", Score:25, Rank:4});
ClearCollect(col3, AddColumns(col2, "ID", LookUp(col1 As tempCol1, Name=tempCol1.Name).ID))

 

 

Please remember to give a 👍 and accept the solution as it will help others in the future.

View solution in original post

5 REPLIES 5
CNT
Super User
Super User

@Deepshikha21 I've made a sample with some simple column names. You can follow the same pattern and use it with your collections and column names.

 

ClearCollect(col1, {Name:"Tom",ID:1}, {Name:"Mark",ID:2});
ClearCollect(col2, {Name:"Tom", Score:10, Rank:1}, {Name:"Tom", Score:20, Rank:3},{Name:"Mark", Score:15, Rank:2},{Name:"Mark", Score:25, Rank:4});
ClearCollect(col3, AddColumns(col2, "ID", LookUp(col1 As tempCol1, Name=tempCol1.Name).ID))

 

 

Please remember to give a 👍 and accept the solution as it will help others in the future.

Hi @CNT , thanks for your response but as per your example col3 will be having values as below -

Deepshikha21_0-1623952041750.png

and as per my example I need output collection as below -

Deepshikha21_1-1623952603119.png

I need all the values where KPI Name is same in both the collection and that's why I am using filter instead of lookup.

@Deepshikha21 

Please check again. This is what I'm getting,

CNT_0-1623967554393.png

 

The code again,

ClearCollect(colComb1, {Name:"Tom",ID:1}, {Name:"Mark",ID:2});
ClearCollect(colComb2, {Name:"Tom", Score:10, Rank:1}, {Name:"Tom", Score:20, Rank:3},{Name:"Mark", Score:15, Rank:2},{Name:"Mark", Score:25, Rank:4});
ClearCollect(colComb3, AddColumns(colComb2, "ID", LookUp(colComb1 As tempColComb1, Name=tempColComb1.Name).ID))

It works, Thanks A lot @CNT  !!😊

@Deepshikha21 Glad to help! 

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors
Users online (2,668)