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));Did You Enjoy This Article? 😺
Subscribe to get new Power Apps articles sent to your inbox each week for FREE
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.
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!
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”))
Thank you for this élégant and powerfull syntax Matthew 🙂
Philippe,
You’re welcome Mr. Mallet!
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)
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:
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.
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))
looks like ‘Result’ is not valid anymore….using ‘Value’ seems to work instead…I’ll test some more!
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 😉
Louis,
Yes, Microsoft is making several changes to Power Apps language behind the scenes and one of them is “Value” instead of “Result”
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 🙂
Craig,
Lol. Yes. Yes I did 😂
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!
Daniel,
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 😉
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
?
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.”
Kirk,
The “Result” column name was deprecated. I think you should use “Value” instead.