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.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Check out the New Ideas Site

We are excited to announce a new way to share your ideas for Power Apps!

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