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

How to merge two collections with different columns and records

Hi there,

 

I'm trying to merge two completely different collections (collectionA and collectionB) with different records and columns into a new collection (newCollection). I've tried creating a new collection (newCollection) using AddColumns to collectionA but the records from collectionB were merged in different rows. As a result I am expecting a newCollection with the same columns from collectionA and collectionB with the exact same structure for the records from collectionA and collectionB.

 

Here is an image that shows clearly what I'm looking for:

 

merging different collections.png

 

Here is an example.

 

collectionA:

IDNameLastNameStatus
1JuliusBennetapproved
2NickSalesremoved
3MariaTorrespending
4SamMenningapproved

 

collectionB:

Order NamePriceQuantity
tshirt floral P203
coat black M452
tshirt white P201
cap green U154

 

newCollection - expected result:

IDNameLastNameStatusOrder NamePriceQuantity
1JuliusBennetapproved   
2NickSalesremoved   
3MariaTorrespending   
4SamMenningapproved   
    tshirt floral P203
    coat black M452
    tshirt white P201
    cap green U154

 

 

Thanks for the help! 🙂

 

14 REPLIES 14
RandyHayes
Super User
Super User

@raphansa 

If you want what you showed in your expected results example, then you can simply do:

   ClearCollect(NewCollection, CollectionA, CollectionB)

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
CNT
Super User
Super User

@raphansa 

Please try the following,

ClearCollect(newCollection, 
	ForAll(Sequence(CountRows(collectionA)),
		({
			ID:First(LastN(collectionA,ThisRecord.Value)).ID, 
			Name:First(LastN(collectionA,ThisRecord.Value)).Name, 
			LastName:First(LastN(collectionA,ThisRecord.Value)).LastName,
			Status:First(LastN(collectionA,ThisRecord.Value)).Status, 
			OrderName:First(LastN(collectionB,ThisRecord.Value)).OrderName, 
			Price:First(LastN(collectionB,ThisRecord.Value)).Price, 
			Quantity:First(LastN(collectionb,ThisRecord.Value)).Quantity, 
		})
	)
)

Hi!

 

Thanks for the answer! I've tried using this formula, but it shows an error of "Invalid arguments", I assume that ClearCollect items cannot be collections.

raphansa
Frequent Visitor

Hey @CNT !

 

Thanks for the answer! But what if collectionA and collectionB had different number of rows, would this formula still work?

RandyHayes
Super User
Super User

@raphansa 

With the formula ClearCollect(NewCollection, CollectionA, CollectionB) you will end up with what you were looking for regardless of the number of rows.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

@RandyHayes 

 

I've just tried using this formula but I'm receiving an error of "Invalid arguments" here is a photo.

 

Translating the error message:

"The types provided are incompatible.

The "ClearCollect" function has some invalid arguments."

 

raphansa_0-1638214233451.png

 

Thanks! 🙂

 

 

RandyHayes
Super User
Super User

@raphansa 

Place your pointer directly over the dark red underlined promotions and see what the error shows.

The formula is perfectly fine, so there has to be something about the promotions part.

 

Using your provided sample data...this formula:

RandyHayes_0-1638214634922.png

Produces these results in colC (sorry a bit cut off, but shows the results):

RandyHayes_1-1638214692891.png

RandyHayes_2-1638214706524.png

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

@RandyHayes 

 

I don't know what might be causing this, but for me it shows the same error message as before ("Invalid arguments"). If I change the order the error remains the same 😞

 

raphansa_2-1638215150812.png

-- Changing the order:

raphansa_1-1638215116233.png

 

 

RandyHayes
Super User
Super User

@raphansa 

Something is conflicting in some other part of the formula or some other formula in your app.

The best thing to do at this point is to comment out the clearcollect statement in this formula and then look at the other two collections to see what they contain and what they are used for.

I am not sure what you are using these for, or why even a collection is needed, but that would be the first place to look is at every place you are using the collection and how you are populating the values to it.  If you have more examples, I can look at those too.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,284)