Combining 2 collections

[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?

[WarrenBelz]

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

[mmbr1606]

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

[sgtsnacks64 · Accepted solution]

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.

[TerryHughes]

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)

[TerryHughes]

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

[sgtsnacks64 · Accepted solution]

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)}))));