AddCollumn Join Collections or Merge -to Create new collection

(0) Share

Report

Posted on by chico

638

Esteemed Colleagues,

Not sure if I'm over thinking, or simply lost.

Working with (2) Collections

colQueryTest
colQueryTest2

ClearCollect(colQueryTest,
 	Table(
 	{ Flavor: "Strawberry", UnitPrice: 1, QuantitySold: 20 }, 
 	{ Flavor: "Chocolate", UnitPrice: 3, QuantitySold: 45 },
 	{ Flavor: "Vanilla", UnitPrice: 5, QuantitySold: 35 }
 	)
 );
 ClearCollect( colQueryTest2, 
 	Table(
 	{ Flavor: "Strawberry", size: 3, Style: "original" }, 
 	{ Flavor: "Chocolate", size: 6, Style: "original" },
 	{ Flavor: "Vanilla", size: 9, Style: "mixed" }
 	)
 )

End goal is to create a New Collection "colQueryTest3
merging all data based on the 'Flavor' field.

So for example result in colQueryTest3;

{ Flavor: "Chocolate", UnitPrice: 3, QuantitySold: 45, size: 6, Style: "original" }

Been bumping my head on the wall using AddColumns, Groupby, Collect and just not gaining any traction so far.

Also for what its worth,
I'm using simple collections to test out formulas but the end goal would be to use SharePoint Lists as the sources.

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Verified answer

CNT 10,921 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
@chico

Please try the following,

ClearCollect(colQueryTest3, AddColumns(colQueryTest2, "UnitPrice", LookUp(colQueryTest As tempCol, Flavor=tempCol.Flavor).UnitPrice, "QuantitySold", LookUp(colQueryTest As tempCol, Flavor=tempCol.Flavor).QuantitySold ) )

Please remember to give a 👍 and accept my solution as it will help others in the future.

Was this reply helpful? Yes No
chico 638 on at

Like (0)

Report
Copy link

Link copied!
@CNT ,

that formula def works and I appreciate the assist.
Can you give me some insight in to what is happening here;
LookUp(colQueryTest As tempCol
my first time seeing the "As" and is it safe to assume its simply storing in a very temp collection that is instantly recollected in the next line?

Also, I've seen challenges in the past when doing a Lookup on SharePoint lists with more than 1k records,
could this same process be accomplished with using Filter, instead of Lookup?

Was this reply helpful? Yes No
CNT 10,921 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@chico

LookUp is used to find the matching record with the same key (Flavour in this case). LookUp returns the first matching record whereas Filter returns all the matching records.

We are using the As operator to avoid ambiguity of the Column Names.

Please remember to give a 👍 and accept my solution as it will help others in the future.

Was this reply helpful? Yes No
chico 638 on at

Like (0)

Report
Copy link

Link copied!

oki, thank you.
appreciate the insight and support

Was this reply helpful? Yes No
CNT 10,921 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@chico

Glad to help!

Was this reply helpful? Yes No