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.