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
Community Champion
Community Champion

@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
Community Champion
Community Champion

@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

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.

CNT
Community Champion
Community Champion

@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  !!😊

CNT
Community Champion
Community Champion

@Deepshikha21 Glad to help! 

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Carousel April Dunnam Updated 768x460.jpg

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors
Users online (1,955)