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

(0) Share

Report

Posted on by 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.

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (2)

Sort by

Verified answer

a33ik 3,306 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
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 ) ) ) );

Was this reply helpful? Yes No
MIA27 2 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
a33ik 3,306 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
MIA27 2 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
a33ik 3,306 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
MIA27 2 on at

Like (0)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
Verified answer

a33ik 3,306 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No