Add rows only if not already present in a Collection

(0) Share

Report

Posted on by cbroy_22

Hi All,

Need help. I have two collections which I would like to combine. One is colSPList and another colExcel.

Sample data from SP List is

Sample Data from Excel

Code	Name	ID	StartDate
33	Elliot, Erin	0034	4/15/2021
33	Daniel, Wilfred	4576	9/15/2020
37	Willis, Joy	7348	2/12/2021

ClearCollect(
colSPList,
RenameColumns(
ShowColumns(
Filter(
'SP Review',
Code = varCode
),
"Code",
"Name",
"IdNumber"
),
"IdNumber",
"ID"
)
);

ClearCollect(
colExcel,
DropColumns(
Filter(
Listing,
Code = varCode
),
"Code",
"Name",
"ID"
),
"StartDate")
);

What I am trying to achieve is to combine and add details from colExcel only if it is not present in colSPList as below

Categories:

Building Power Apps

All responses (4)

Answers (2)

Sort by

Verified answer

WarrenBelz 156,736 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @cbroy_22 ,

I cannot test this (and free-typed, so watch commas/brackets etc), but I believe this is the structure you need

With( { wSP: RenameColumns( ShowColumns( Filter( 'SP Review', Code = varCode ), "Code", "Name", "IdNumber" ), "IdNumber", "ID" ), wXL: ShowColumns( Filter( Listing, Code = varCode ), "Code", "Name", "ID" ) }, ClearCollect( colCombined, wSP, Filter( wXL, !(Name in wSP.Name) ) ) )

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.

Visit my blog Practical Power Apps
cbroy_22 37 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you @WarrenBelz , this works absolutely fine when I am not bringing in StarteDate. In SharePoint Date is stored in date and time format but in excel its in text format, Whenever I am trying to write combined collect its shows error
Verified answer

WarrenBelz 156,736 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @cbroy_22 ,

You example post did not have the Start Date in and the SharePoint list does not show this field (so I assumed you did not need it), so if the name of the field in SharePoint is Date (a bad name for a field) -

With( { wSP: ShowColumns( RenameColumns( Filter( 'SP Review', Code = varCode ), "IdNumber", "ID", "Date", "DateStart" ), "Code", "Name", "ID", "DateStart" ), wXL: ShowColumns( AddColumns( Filter( Listing, Code = varCode ), "DateStart" DateValue(StartDate) ), "Code", "Name", "ID", "DateStart" ) }, ClearCollect( colCombined, wSP, Filter( wXL, !(Name in wSP.Name) ) ) )

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.

Visit my blog Practical Power Apps
cbroy_22 37 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you, I wasn't sure if DateValue would solve the problem. Agreed Date is a bad name 🙂 , it does prefix which I had to edit, sharepoint list snapshot was incomplete and apologize I couldn't communicate my requirement first time. Thank you so much for your prompt reply.