How to compare in between two collection and bring the result in the third collection

[MIA27]

I am developing an app in teams power app.

I have a 2 collection, each is based on a table.

I want to compare in between the 2 collection and bring the result from both without duplication in the 3rd collection.

To explain better I made a sample and pasted below as an image.

Please guide how to to be done.

[a33ik · Accepted solution]

Hello,

Here is the formula that worked for me:

ClearCollect(
    CollectionA,
    {
        RecoNo: 1,
        Model: "WA470-5",
        Serial: "A1234"
    },
    {
        RecoNo: 2,
        Model: "PC400-8R",
        Serial: "N23456"
    },
    {
        RecoNo: 3,
        Model: "PC400-8R",
        Serial: "N23456"
    },
    {
        RecoNo: 4,
        Model: "D155A-6R",
        Serial: "B1725"
    },
    {
        RecoNo: 5,
        Model: "GD755-5R",
        Serial: "G1234"
    }
);
ClearCollect(
    CollectionB,
    {
        RecoNo: 1,
        Model: "WA470-5",
        Serial: "A1234"
    },
    {
        RecoNo: 2,
        Model: "PC400-8R",
        Serial: "N0082"
    },
    {
        RecoNo: 3,
        Model: "PC400-8R",
        Serial: "N23456"
    },
    {
        RecoNo: 4,
        Model: "D155A-6R",
        Serial: "B1725"
    },
    {
        RecoNo: 5,
        Model: "GD755-5R",
        Serial: "G6752"
    }
);
ClearCollect(CollectionC, CollectionA);
Collect(CollectionC,
    Filter(
        CollectionB As colb,
        IsBlank(
            LookUp(
                CollectionA As cola,
                cola.Model = colb.Model && cola.Serial = colb.Serial
            )
        )
    )
);

[MIA27]

Dear,

Thank you for your reply.

But in my case, collectionA is based on a Dataverse teams table and has around 6200 records and collectionB has around 6200 records.

In both the collection Model and Serial data type matches.

The example i made in excel just to make advisors understand my requirement.

Your code shows hard coding for reach record which is practically not possible and its keep adding few records on daily basis. Therefore, hard code for each row will not work.

It is not possible , that while making the collectionA , i will collect only the  required 3 columns and the same will do for CollectionB. So both collection column and data type will match and then can compare to build collectionC.

Please guide

[a33ik]

Dear,

Instead of hardcoding (that was just an example) you can grab the information from Dataverse in CollectionA and CollectionB and use the second part of the formula. Does it make sense?

[MIA27]

Sorry, I am not in coding and also new in this development of power app.

I was confused that how to pull only 3 field in a collection from a table.

As my Table1 (on which collectionA is based) has many different column in comparison to Table2 (on which CollectionB is based).

But the 2 column name (Model and Serial) and data type matches. That 2 column is my requirement to be pulled in CollectionC, which I want to make the source of my gallery.

You mean, I can Write as below to get the result:

ClearCollect(CollectionA, Table1);

ClearCollect(CollectionB, Table2);

ClearCollect(CollectionC, CollectionA);
Collect(CollectionC,
Filter(
CollectionB As colb,
IsBlank(
LookUp(
CollectionA As cola,
cola.Model = colb.Model && cola.Serial = colb.Serial
)
)
)
);

Please guide

[a33ik]

Hello,

In order to grab and format the collection you can use different approaches. Here is one of those - I use "Account" table from Dataverse for this purpose:

ClearCollect(
    CollectionD,
    ForAll(
        Accounts As a,
        {
            RecoNo: a.'Import Sequence Number',
            Model: a.'Account Name',
            Serial: a.'Account Name'
        }
    )
);

You can use a similar approach with your 2 sources, convert them to a collection, and then compare 2 collections.

[MIA27]

I will give a try as you advised,

In the mean time, while search came across to this code to pull only the required column

ClearCollect(ColWhatever, ShowColumns(TheListName, "ColumnName1", "ColumnName2"))

But I think in my case it may not work as both collection will get into the same column name  .

Please guide on this method,.

Also I just wanted to clarify from the second parts of the code you advised.

Collect(CollectionC,
    Filter(
        CollectionB As colb,
        IsBlank(
            LookUp(
                CollectionA As cola,
                cola.Model = colb.Model && cola.Serial = colb.Serial
            )
        )
    )
);

I will check by practical result, as I have not understood the code, 

My requirement was to have all records of CollectionA (as it has 6200) and unmatched records of CollectionB

[a33ik · Accepted solution]

ShowColumns might be used combined with RenameColumns to perform the transformation to the format you need:

ClearCollect(ColWhatever, RenameColumns(ShowColumns(TheListName, "ColumnName1", "ColumnName2", "ColumnName3"), ColumnName1, RecoNo, ColumnName2, Model, ColumnName3, Serial))

You correctly understood what I meant with the second part of the code.