cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Merging collection without primary key

Hi all,

I am looking for something really simple (which, lo and behold, is not that simple at all in Powerapps 😫).
I have two collection (more actually, but if I solve this problem then I am good).

collection1    collection2

   Result         Result
   abc             jkl
   def             mno
   ghi             pqr

Two collections, each with one column named Result. What I need is to simply merge the two collections (that is, the two columns) into a new collection that contains the columns from the respective collections.

I've tried a lot of things but I either end up with a column that contains tables in each field or,  using the solution in this thread, I end up with a collection with 9 duplicated records.

 

I can't use ClearCollect with AddColumns and LookUp since I don't have a primary key by which to join the records (as described in this thread). 

 

Isn't there a simple solution to such a simple problem?



1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Hi @NSimpraga ,

Got it. Please try this.

If(CountRows(Col1)>=CountRows(Col2),
ClearCollect(Col3,AddColumns(RenameColumns(Col1,"Result","ResultCollection1"),"ResultCollection2",""));
ClearCollect(G,[0]);ForAll(Col2,Collect(G,{Value:Last(G).Value+1});Patch(Col3,Last(FirstN(Col3,Last(G).Value)),{ResultCollection2:Result})),
ClearCollect(Col4,AddColumns(RenameColumns(Col2,"Result","ResultCollection2"),"ResultCollection1",""));
ClearCollect(G,[0]);ForAll(Col1,Collect(G,{Value:Last(G).Value+1});Patch(Col4,Last(FirstN(Col4,Last(G).Value)),{ResultCollection1:Result}))
)

Hope this helps.

Sik

View solution in original post

7 REPLIES 7
Highlighted
Super User III
Super User III

Hi @NSimpraga ,

 

it is very very straight forward even in Power Apps, Try the below:

ClearCollect(Col3, Col1,Col2);
Now your 3rd collection is the merge of 2 colletions

 
I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
Krishna
If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Proud to be a Super User!

Regards,
KrishnaV
Business Applications MVP
Twitter | Linkedin | YouTube | GitHub
If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members / new members of the community.
Highlighted

Okay, I probably explained it incorrectly. What I need is to merge the two collections into a single collection which has 2 columns, each of which were in collection1 and collection2.

So if we take the data set above the new collection will look like this:

                      Collection3
ResultCollection1       ResultCollection2
         abc                             jkl
         def                             mno
         ghi                             pqr

The solution you typed above is for simply merging the data into a single column.

Highlighted
Helper I
Helper I

*bump* 
Feels like I am stuck in a nightmare, pls send help 

Highlighted

Hi @NSimpraga ,

 

Please try with this:

ClearCollect(Col3,Table({ResultCollection1:First(Col1).Result,ResultCollection2:First(Col2).Result}))

Hope this helps.

Sik

Highlighted

Thanks for the answer!
This creates a new collection with only the first records from each column being merged. Like this:
                            Col3
ResultCollection1     ResultCollection2
            abc                      jkl

 

It is a good start but sadly not enough.

Highlighted

Hi @NSimpraga ,

Got it. Please try this.

If(CountRows(Col1)>=CountRows(Col2),
ClearCollect(Col3,AddColumns(RenameColumns(Col1,"Result","ResultCollection1"),"ResultCollection2",""));
ClearCollect(G,[0]);ForAll(Col2,Collect(G,{Value:Last(G).Value+1});Patch(Col3,Last(FirstN(Col3,Last(G).Value)),{ResultCollection2:Result})),
ClearCollect(Col4,AddColumns(RenameColumns(Col2,"Result","ResultCollection2"),"ResultCollection1",""));
ClearCollect(G,[0]);ForAll(Col1,Collect(G,{Value:Last(G).Value+1});Patch(Col4,Last(FirstN(Col4,Last(G).Value)),{ResultCollection1:Result}))
)

Hope this helps.

Sik

View solution in original post

Highlighted

Thank you for the answer!

Feel stupid a bit now, but in the meantime I found a bit more elegant solution in this thread.
Again, thank you for your effort, if I find time to test it, I will try out your solution too!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (9,945)