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.

Deepshikha21
Frequent Visitor

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))
Deepshikha21
Frequent Visitor

It works, Thanks A lot @CNT  !!😊

@Deepshikha21 Glad to help! 

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Top Kudoed Authors
Users online (3,232)