Remove Duplicate Rows From A Power Apps Collection

Remove the duplicate rows from any Power Apps collection with this 1 line of code.

//Duplicate removal code
ClearCollect(colSolution, ForAll(Distinct(yourCollectionName, ThisRecord), Result));

Here’s how it works:

The Distinct function returns a one-column table of unique values for a field. But when we use ThisRecord instead of a column name it results in a one-column table of unique records. Then we loop over the one column table using ForAll to extract the rows into a multliple column table.

For more help with collections check out my Power Apps Collections Cookbook. There are over 50 examples and code you can copy + paste into your own apps.

Full Example: Remove Duplicate Rows

Input Collection

Start with this Power Apps collection named colVehicles.

Year	Make	Model
2020	Ford	F150
2018	Chevy	Silverado
2020	Ford	F150
2020	Ford	F150
2017	Nissan	Frontier
2016	GM	Yukon
2016	GM	Yukon

Output Collection

Our goal is to remove the duplicate rows from the Power Apps collection colVehicles and achieve this result.

Year	Make	Model
2020	Ford	F150
2018	Chevy	Silverado
2017	Nissan	Frontier
2016	GM	Yukon

Solution Code

Use this code to remove the duplicate rows from colVehicles.

//Create a collection
ClearCollect(colVehicles,
    {Year: 2020, Make: "Ford", Model: "F150"},
    {Year: 2018, Make: "Chevy", Model: "Silverado" },
    {Year: 2020, Make: "Ford", Model: "F150" },
    {Year: 2020, Make: "Ford", Model: "F150"},
    {Year: 2017, Make: "Nissan", Model: "Frontier"},
    {Year: 2016, Make: "GM", Model: "Yukon"},
    {Year: 2016, Make: "GM", Model: "Yukon"}
);

//Duplicate removal code
ClearCollect(mySolution, ForAll(Distinct(colVehicles, ThisRecord), Result));

Questions?

If you have any questions about Remove Duplicate Rows From A Power Apps Collection please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.

Matthew Devaney

How To Validate A Power Apps Form Before Submission

Share A Model-Driven App With An Entra Security Group

Share Canvas App With A Microsoft 365 Group In Power Apps

32 Comments

Oldest

Newest

Inline Feedbacks

View all comments

George Ellis

2 years ago

Interesting Matt—thanks. On a related note, you make me think of the times I want to step through a collection and modify one value in each row. Because you cannot modify what you’re using for the index, I make a whole new copy of the entire collection. Looking at this, I wonder if I could just use something like

ForAll(Distinct(fullCltn, fullCltnGUID), and then step through the GUIDs. If something like this would work, it would sure save a lot of memory!

Matthew Devaney

2 years ago

Reply to George Ellis

George,

You can also step through a collection by using an “alias” (i.e. the AS operator)

For example this is totally legal:

ForAll(colStuff As tblStuff, Patch(colStuff, ID=tblStuff[@ID], Field1: “foo”))

MALLET Philippe

2 years ago

Thank you for this élégant and powerfull syntax Matthew 🙂

Matthew Devaney

2 years ago

Reply to MALLET Philippe

Philippe,

You’re welcome Mr. Mallet!

Marcel Lehmann

2 years ago

Interesting solution, good job.
I had not yet needed, but now I have to think about it fortunately not.

Important: With a collection as a basis, this works great, but if we had SharePoint as a source, we would run into the delegation problem with the Distinct function. (if more entries)

sonam

2 years ago

Hi, Matthew. I usually follow your solution. It’s really helpful. Recently, I followed this solution to remove duplicates from the collection, but I am not satisfied.

Here is my collection, i.e., CollCountry. I collect data from an Excel data table, which has some duplicate values.

Then I applied the following formula to a button to create another collection by removing the duplicates.

And here is the result:

OnSelect = ClearCollect(mySolution, ForAll(Distinct(CollCountry, ThisRecord), Result))

Matthew Devaney

2 years ago

Reply to sonam

Sonam,

I’ve tested the code thoroughly and it works. My guess is your original collection ‘CollCountry’ has more than the 5 fields visible in the table. Please check on this.

Louis

1 year ago

Reply to Matthew Devaney

Am I correct in assuming then that if a collection has more than 5 columns this will not work?

I am getting “Invalid argument type. Name isn’t valid. ‘Result’ isn’t recognized” when using this solution…code as follows:

ClearCollect(
  col_Collection1,
  ForAll(
    Distinct(col_Collection2,
    ThisRecord),
    Result))

Louis

1 year ago

Reply to Matthew Devaney

looks like ‘Result’ is not valid anymore….using ‘Value’ seems to work instead…I’ll test some more!

Matthew Devaney

1 year ago

Reply to Louis

Louis,

Thanks for calling this to my attention. The Power Apps language changed the Distinct function to output a “Value” as opposed to a “Result.” This is one of the items I must check and revise over 300+ blog pages for 😉

Matthew Devaney

1 year ago

Reply to Louis

Louis,

Yes, Microsoft is making several changes to Power Apps language behind the scenes and one of them is “Value” instead of “Result”

Mica

10 months ago

Reply to Louis

It works with Value instead of Result!!!
Thank you very mutch for your help. I was almost crasy trying to do this

Matthew Devaney

9 months ago

Reply to Mica

Mica,

Power Apps changed its programming language. Result used to work, now Value does.

Craig Stewart

1 year ago

So today I was working on gallery selections filtering another gallery and needed to get rid of duplicates. I fiddled a bit, tried a few things. Then I thought “You know who will know how to this? Matthew Devaney” I was so close in getting to what I wanted- but this guide got me through the final piece. Thanks Matthew 🙂

Last edited 1 year ago by Craig Stewart

Matthew Devaney

1 year ago

Reply to Craig Stewart

Craig,

Lol. Yes. Yes I did 😂

Daniel Kerridge

1 year ago

Hi Matt,

I had to change “Result” to “Value” to get this code to work for me.

Working Code:

//Create a collection
ClearCollect(colVehicles,
  {Year: 2020, Make: “Ford”, Model: “F150”},
  {Year: 2018, Make: “Chevy”, Model: “Silverado” },
  {Year: 2020, Make: “Ford”, Model: “F150” },
  {Year: 2020, Make: “Ford”, Model: “F150”},
  {Year: 2017, Make: “Nissan”, Model: “Frontier”},
  {Year: 2016, Make: “GM”, Model: “Yukon”},
  {Year: 2016, Make: “GM”, Model: “Yukon”}
);

//Duplicate removal code
ClearCollect(mySolution, ForAll(Distinct(colVehicles, ThisRecord), Value));

Cheers!

Matthew Devaney

1 year ago

Reply to Daniel Kerridge

Daniel,

Tim

1 year ago

Hello Matthew,
Hmm weird when i do this part or at least i think i do
I only get no results instead of all the names i want to see except duplicated ones
What do i wrong?

ForAll( Distinct( ColAllData; CUSTOMER );
LookUp( ColAllData; Trim(TextInput1_8.Text) in “field_7” )
)

For example i have 4 Bruno Guide and a few Bruno with other last names
But i get nothing
?

Kirk

1 year ago

Any idea why I’m getting these errors (screenshot shows my formula and errors) when I use your syntax exactly?…
“The function ‘ForAll’ has some invalid arguments.”
“Name isn’t valid. ‘Result’ isn’t recognized.”
“The function ‘ClearCollect’ has some invalid arguments.”
“Invalid argument type.”

Matthew Devaney

1 year ago

Reply to Kirk

Kirk,

The “Result” column name was deprecated. I think you should use “Value” instead.

Laiz

1 year ago

Hi Matthew, how are you. I have a doubt.

I created an application with power apps. On one of the screens of this app I have a Scan button. When I click this button the camera opens and I scan the barcode. This barcode migrates to a list I created in Lists.

I wouldn’t want the same barcode to be scanned twice. I would like a message to appear saying that the barcode already exists when a barcode is scanned twice.

I’m trying but I can’t know the code because I’m not in the IT field and I’m having a lot of difficulty understanding the formulas. Can you help me, please?

in my Lists in sharepoint I select exclusive values in the collumn of my interest. But in my app when I scan a duplicate number a message error appears Network error when using the Pach function. The requested operation is invalid.

How can I change this message for something friendly?

Thank you

Matthew Devaney

1 year ago

Reply to Laiz

Laiz,

Suggest you check out my article on Power Apps Patch Function Error Handling:
https://www.matthewdevaney.com/power-apps-patch-function-error-handling/

Use the Notify function to substitute with a friendlier error message. It’s all in the article.

Laiz

1 year ago

Reply to Matthew Devaney

i try to put the this code but is not working 🙁

Patch(
  CheckFlow;
  Defaults(CheckFlow);
  {‘Digitalizacao do Tubo 1 MO’: BarcodeScanner1_2.Value};
  {‘Codigo Grade’: BarcodeScanner1_1.Value};
  {Data: Now()}
);;
If(
  !IsEmpty(Errors(CheckFlow));;
Notify(
  “Está passagem já foi digitalizada! Por favor, verifique seu processo. Obrigada”;
  NotificationType.Error
);;

Chris Cundy

1 year ago

How would this work on this fairly complex Fx?

colAllSubtopics appears to be affected and duplicating data:

// Set your variables first
Set(varItem, ThisItem);
Set(varBuildingPhoto, LookUp(BuildingPhotos, Title = ThisItem.spBuilding).'Link to item');
Set(varBuildingPhotoID, LookUp(BuildingPhotos, Title = ThisItem.spBuilding).ID);


// Data collection and ClearCollect statements go here


// THIS INCLUDES ALL THE TOPIC HEADERS
ClearCollect(colSubtopics1, Distinct(Filter(SubtopicArgEvi_1,Building=varItem.spBuilding),Topic));


// THIS IS THE DISTINCT COLLECTION OF ORDER OF TOPICS
ClearCollect(colSubtopics, ForAll(Sequence(CountRows(colSubtopics1)), Patch(Last(FirstN(colSubtopics1,Value)),{Order:Value})));


ClearCollect(colTempAllSubtopics, AddColumns(Filter(SubtopicArgEvi_1,Building=varItem.spBuilding),"TopicOrder",0,"SubtopicOrder",0));
Clear(colTempAllSubtopicsB);
ForAll(colTempAllSubtopics, Collect(colTempAllSubtopicsB, {
    Top: colTempAllSubtopics[@Topic], 
    SubTop: colTempAllSubtopics[@Subtopic] & " - " & colTempAllSubtopics[@'Subtopic Breakdown'],
    Arg: colTempAllSubtopics[@Argument],
    Evid: colTempAllSubtopics[@Evidence],
    TopOrder: LookUp(colSubtopics,Value=colTempAllSubtopics[@Topic]).Order+2
}));


Clear(colAllSubtopics);


    Collect(colAllSubtopics, ForAll(Sequence(CountRows(Filter(colTempAllSubtopicsB, TopOrder=3)), 3.1, 0.1), Patch(Last(FirstN(Filter(colTempAllSubtopicsB, TopOrder=3), Value)), {SubTopOrder: RoundUp(Value, 2)})));
    Collect(colAllSubtopics, ForAll(Sequence(CountRows(Filter(colTempAllSubtopicsB, TopOrder=4)), 4.1, 0.1), Patch(Last(FirstN(Filter(colTempAllSubtopicsB, TopOrder=4), Value)), {SubTopOrder: RoundUp(Value, 2)})));
    Collect(colAllSubtopics, ForAll(Sequence(CountRows(Filter(colTempAllSubtopicsB, TopOrder=5)), 5.1, 0.1), Patch(Last(FirstN(Filter(colTempAllSubtopicsB, TopOrder=5), Value)), {SubTopOrder: RoundUp(Value, 2)})));
    Collect(colAllSubtopics, ForAll(Sequence(CountRows(Filter(colTempAllSubtopicsB, TopOrder=6)), 6.1, 0.1), Patch(Last(FirstN(Filter(colTempAllSubtopicsB, TopOrder=6), Value)), {SubTopOrder: RoundUp(Value, 2)})));
    Collect(colAllSubtopics, ForAll(Sequence(CountRows(Filter(colTempAllSubtopicsB, TopOrder=7)), 7.1, 0.1), Patch(Last(FirstN(Filter(colTempAllSubtopicsB, TopOrder=7), Value)), {SubTopOrder: RoundUp(Value, 2)})));
    Collect(colAllSubtopics, ForAll(Sequence(CountRows(Filter(colTempAllSubtopicsB, TopOrder=8)), 8.1, 0.1), Patch(Last(FirstN(Filter(colTempAllSubtopicsB, TopOrder=8), Value)), {SubTopOrder: RoundUp(Value, 2)})));
    Collect(colAllSubtopics, ForAll(Sequence(CountRows(Filter(colTempAllSubtopicsB, TopOrder=9)), 9.1, 0.1), Patch(Last(FirstN(Filter(colTempAllSubtopicsB, TopOrder=9), Value)), {SubTopOrder: RoundUp(Value, 2)})));
    Collect(colAllSubtopics, ForAll(Sequence(CountRows(Filter(colTempAllSubtopicsB, TopOrder=10)), 10.1, 0.1), Patch(Last(FirstN(Filter(colTempAllSubtopicsB, TopOrder=10), Value)), {SubTopOrder: RoundUp(Value, 2)})));


UpdateContext({IsLoading: true})

KN. R

1 year ago

I have been fiddling with a SP data source over 2000 rows. Trying to filter upon collection but getting duplicate rows in my table where i view the collection. I have edited one of these rows and patched to my OG data source and all works well.

Here is my formula:
ClearCollect(colBulkEditEdit, PlanInformation, Filter(PlanInformation, Operator = Gallery3.Selected.Result));
ClearCollect(colBulkEdit, ForAll(Distinct(colBulkEditEdit, ThisRecord), Value));

Lorenzo

9 months ago

Hi Matthew, thanks for clear and concise solutions you always provide. Is there any way to remove with PowerApps the duplicate rows from a Sharepoint List?

Matthew Devaney

9 months ago

Reply to Lorenzo

Lorenzo,

Same method as a I showed here. But you can’t go over 2,000 rows because that is the delegation limit.

Armando

9 months ago

Hey Mathew!
Thanks for the code, nevertheless for some reason the command “Result” is not recognized anymore. Do you know if it changed?

Eddie E

8 months ago

Hey Matt,
How’s things mate? Just wondering if you could shed some light on this issue I’m having with your process here.

I have an app with a SP List data source – currently 1368 records, but could increase in time. I want to do some minor future proofing of the app so am collecting the Top and Bottom of the list – ie 2 collections called doa2000 & doa4000. These both populate correctly with 1368 records.

I then join these together in collection doaJoined and then use your method. However, the resulting collection doesn’t remove the duplicates – well, I don’t think it does, because it has 2736 records in it (1368 x 2).

I’ve managed to resolve my dilemma with slightly different code where I can use ‘in’ and NOT without issue, ie

// Join the above 4000 records into 1 collection
ClearCollect(
doaALLRecords,
doa2000, Filter(doa4000, !(ID in doa2000.ID))
);

But am just curious as to why this isn’t working for me and if you know why? Might just be holding my mouth wrong, haha.

See the attached image to explain what I’m on about.

Cheers
Eddie

JED

5 months ago

I have tried this but I am still getting duplicate rows?

On visible of page is:

ClearCollect(colPOTOApprove,Filter(CRUD2_Supplier_POs, Approver=_currentUser.FullName && Approved=false && PO_Cancelled=false));

Then ITEMS property of gallery is:

ForAll(Distinct(colPOTOApprove,ThisRecord),Value)

…but I am getting two rows with the same value in my “TITLE” column?

Many thanks.

Nathan

2 months ago

Would something like this work removing records from a dataverse table? Opposed to ClearCollect using Remove/RemoveIf?
My goal is to remove records from one table that do not exist in another table.

Curtis Troutt

1 month ago

Matt:

I am using the following in the ‘OnVisible’ of my screen –

ClearCollect(colPendingUniqueDropData, ForAll(Distinct(Drop_Backup_Collection,ThisRecord),Value))

My SP List contains ‘Projects’ that may have one or many ‘Activities’; Consequently, the Project No. (Title) may have several line items. I only want the ‘base’ Project data to appear in my Gallery once.

The Collection created by the formula is returning, in this case, the Project twice (the Project has two activities). How do I fix this so that the Galley only show the Project data once.

The Gallery items formula is –

If( PJ_Job_Type_Dropdown.Selected.Value = “Drop” && IsBlank(PJ_Location_Dropdown.Selected.Value),
Sort(Distinct(colPendingUniqueDropData,Invoice_Type = PJ_Job_Type_Dropdown.Selected.Value),Value,SortOrder.Ascending))

Remove Duplicate Rows From A Power Apps Collection

Full Example: Remove Duplicate Rows

Input Collection

Output Collection

Solution Code

Did You Enjoy This Article? 😺

Questions?

Matthew Devaney

Force Sync Users From Entra Security Group To Dataverse Team

How To Enable Column Level Security In Dataverse

Power Apps YAML Code Generator

How To Validate A Power Apps Form Before Submission

Share A Model-Driven App With An Entra Security Group

Share Canvas App With A Microsoft 365 Group In Power Apps