Skip to main content

Notifications

Power Apps - Building Power Apps
Answered

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

(0) ShareShare
ReportReport
Posted on by

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.

 

MIA27_0-1713458394805.png

 

 

 

 

  • Verified answer
    a33ik Profile Picture
    a33ik 3,304 on at
    Re: How to compare in between two collection and bring the result in the third collection

    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.

  • MIA27 Profile Picture
    MIA27 on at
    Re: How to compare in between two collection and bring the result in the third collection

    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 Profile Picture
    a33ik 3,304 on at
    Re: How to compare in between two collection and bring the result in the third collection

    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 Profile Picture
    MIA27 on at
    Re: How to compare in between two collection and bring the result in the third collection

    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 Profile Picture
    a33ik 3,304 on at
    Re: How to compare in between two collection and bring the result in the third collection

    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 Profile Picture
    MIA27 on at
    Re: How to compare in between two collection and bring the result in the third collection

    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

     

     

     

     

     

     

     

     

  • Verified answer
    a33ik Profile Picture
    a33ik 3,304 on at
    Re: How to compare in between two collection and bring the result in the third collection

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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,495

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,822

Leaderboard