Unanswered

Combining 2 collections

(0) Share

Report

Posted on by TerryHughes

It seems this wouldn't be had but power apps has succeeded in driving me nuts:

I have 2 collections, colSource, and colDest. They have an index column, index, and 12 data point columns.

A definition of one is

ClearCollect(
 colSource,
 ForAll(
 Gallery1.AllItems,
 {
 Index: "",
 SALT_CODE: ALT_CODE,
 SDG_ID: DG_ID,
 SGPH: GPH,
 SL1: L1,
 SL2: L2,
 SL3: L3,
 SL4: L4,
 SLB_ID: LB_ID,
 SPID: PID,
 SPROD_DESC: PROD_DESC,
 SVENDOR_ID: VENDOR_ID,
 SVENDOR_NAME: VENDOR_NAME
 }
 )
);
ForAll(
 Sequence(CountRows(colSource)),
 Patch(
 colSource,
 Last(
 FirstN(
 colSource,
 Value
 )
 ),
 {Index: Value}
 )
);

colDest is the same thing except with D as a field prefex.

All I am trying to accomplish is to have a button that combines them on index so the colCombo has 25 columns: index, 12 from colSource,12 from colDest.

I have tried writing this a dozen ways and nothing seems to work from simple

ClearCollect(colCombo, colSource, colDest)

to more complex

ClearCollect(colCombo,
 ForAll(colSource,
 {
 Index: "",
 SALT_CODE: SALT_CODE,
 SDG_ID: SDG_ID,
 SGPH: SGPH,
 SL1: SL1,
 SL2: SL2,
 SL3: SL3,
 SL4: SL4,
 SLB_ID: SLB_ID,
 SPID: SPID,
 SPROD_DESC: SPROD_DESC,
 SVENDOR_ID: SVENDOR_ID,
 SVENDOR_NAME: SVENDOR_NAME,
 DALT_CODE: LookUp(colDest, colDest[@Index] = colSource[@Index],colDest[@DALT_CODE]),
 DDG_ID: LookUp(colDest, colDest[@Index] = colSource[@Index],colDest[@DDG_ID]),
 DGPH: LookUp(colDest, colDest[@Index] = colSource[@Index],colDest[@DGPH]),
 DL1: LookUp(colDest, colDest[@Index] = colSource[@Index],colDest[@DL1]),
 DL2: LookUp(colDest, colDest[@Index] = colSource[@Index],colDest[@DL2]),
 DL3: LookUp(colDest, colDest[@Index] = colSource[@Index],colDest[@DL3]),
 DL4: LookUp(colDest, colDest[@Index] = colSource[@Index],colDest[@DL4]),
 DLB_ID: LookUp(colDest, colDest[@Index] = colSource[@Index],colDest[@DLB_ID]),
 DPID: LookUp(colDest, colDest[@Index] = colSource[@Index],colDest[@DPID]),
 DPROD_DESC: LookUp(colDest, colDest[@Index] = colSource[@Index],colDest[@DPROD_DESC]),
 DVENDOR_ID: LookUp(colDest, colDest[@Index] = colSource[@Index],colDest[@DVENDOR_ID]),
 DVENDOR_NAME: LookUp(colDest, colDest[@Index] = colSource[@Index],colDest[@DVENDOR_NAME])
 }
 )
)

and all I get is error the function 'ClearCollect' has some invalid arguments

this out of all the power languages is by far the most frustrating to work in.

Any solutions out there?

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (2)

WarrenBelz 153,026 Most Valuable Professional on at

Like (0)

Report
Hi @TerryHughes ,

Try this structure - I am sure you can put the rest in

ClearCollect( colCombo, AddColumns( colSource, "DDG_ID", LookUp( colDest As _Dest, _Dest.Index = Index ).DDG_ID, "DGPH", LookUp( colDest As _Dest, _Dest.Index = Index ).DGPH, . . . . . . ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
mmbr1606 14,605 Super User 2025 Season 2 on at

Like (0)

Report
hey @TerryHughes

you can also try something like this:
ClearCollect( colCombo, ForAll( colSource as _source, Let( _destRecord := LookUp(colDest, Index = _source.Index), { Index: _source.Index, SALT_CODE: _source.SALT_CODE, SDG_ID: _source.SDG_ID, SGPH: _source.SGPH, SL1: _source.SL1, SL2: _source.SL2, SL3: _source.SL3, SL4: _source.SL4, SLB_ID: _source.SLB_ID, SPID: _source.SPID, SPROD_DESC: _source.SPROD_DESC, SVENDOR_ID: _source.SVENDOR_ID, SVENDOR_NAME: _source.SVENDOR_NAME, DALT_CODE: _destRecord.DALT_CODE, DDG_ID: _destRecord.DDG_ID, DGPH: _destRecord.DGPH, DL1: _destRecord.DL1, DL2: _destRecord.DL2, DL3: _destRecord.DL3, DL4: _destRecord.DL4, DLB_ID: _destRecord.DLB_ID, DPID: _destRecord.DPID, DPROD_DESC: _destRecord.DPROD_DESC, DVENDOR_ID: _destRecord.DVENDOR_ID, DVENDOR_NAME: _destRecord.DVENDOR_NAME } ) ) );

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.

Greetings

Was this reply helpful? Yes No
Verified answer

sgtsnacks64 322 Super User 2024 Season 1 on at

Like (2)

Report
Another approach might be to use ForAll and Sequence to index records from both collections, then patch to combine the records together on each iterations:

ClearCollect( colCombined, ForAll( Sequence(CountRows(colSource)), Patch( Index(colSource, ThisRecord.Value), Index(colDest, ThisRecord.Value))))

Patch will combine two records together when a data source is not specified. Therefore the ForAll statement will return a table of combined records.

Was this reply helpful? Yes No
TerryHughes 36 on at

Like (0)

Report

Thank you, I knew there had to be an easy way to do this, just wish was easier to figure it out (there needs to be a powerfx version of dax.guide)

Was this reply helpful? Yes No
TerryHughes 36 on at

Like (0)

Report
Thank you again for the help,

I now need to add to this a key column that combines the SPID value from colSource, an * and the DPID value from colDest,

ClearCollect( colCombined, ForAll( Sequence(CountRows(colSource)), Patch( Index( colSource, ThisRecord.Value ), Index( colDest, ThisRecord.Value ), { 'Exact Cross': Blank(), SubBy: Gallery1.Selected.UserName, SubKey: Concatenate(colSource[@SPID],"*",colDest[@DPID]) } ) ) );
Doesn't work as the Subkey is a table and not a field

Was this reply helpful? Yes No
Verified answer

sgtsnacks64 322 Super User 2024 Season 1 on at

Like (1)

Report
Hi Terry,

We'd need to use the lookups of both colSource and colDest to get the records from the table, as we're referencing them multiple times we can use With() to declare them once:

ClearCollect( colCombined, ForAll( Sequence(CountRows(colSource)), With( { tvSourceRecord: Index( colSource, ThisRecord.Value), tvDestRecord: Index( colDest, ThisRecord.Value)}, Patch( tvSourceRecord, tvDestRecord, { 'Exact Cross': Blank(), SubBy: Gallery1.Selected.UserName, SubKey: Concatenate(tvSourceRecord.SPID,"*",tvDestRecord.DPID)}))));

Was this reply helpful? Yes No