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! 🙂

 

12 REPLIES 12
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.
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.
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.
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.
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
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

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