In some cases, the collection may have duplicate data that needs to be removed before consuming it. This blog will discuss how to remove duplicates in a collection.
Sample Collection
In the above example, we need to remove the duplicate item which is ID : 3
Steps
- Need to find distinct based on some column (Example: CreatedDate)
- Go through the distinct collection and get the first record for the same column
1. Get Distinct (Collection Name is colSample)
ClearCollect(
colDistinct,
Distinct(
colSample,
CreatedDate
)
);
Output for the above command
2. Clear the collection before collect
Clear(colFinal);
3. Loop through the Distinct collection and get the first item from the main collection
ForAll(
colDistinct,
Collect(
colFinal,
First(
Filter(
colSample,
CreatedDate = Result
)
)
)
);
Output for the previous command
How to test it?
- Create a new empty screen
- Add button or icon
- Place the below code OnSelect property
Complete Command with Sample Data
ClearCollect(
colSample,
{
CreatedDate: “08-05-2021”,
Title: “PowerApp”,
ID:1
},
{
CreatedDate: “08-06-2021”,
Title: “UserGroup”,
ID:2
},
{
CreatedDate: “08-06-2021”,
Title: “UserGroups”,
ID:3
},
{
CreatedDate: “08-03-2021”,
Title: “UserCommunity”,
ID:4
}
);
ClearCollect(
colDistinct,
Distinct(
colSample,
CreatedDate
)
);
Clear(colFinal);
ForAll(
colDistinct,
Collect(
colFinal,
First(
Filter(
colSample,
CreatedDate = Result
)
)
)
);
Thank you soooooooo much for sharing.
I am trying to distinct my collection over ProductName so the collection doesnt show the same productName several times on the list, but I need to have the ProductQTY to show how many of the product has been ordered.
By using your code I only show the fist PruductQTY of the distinct product. Would you pls help me to have the distinct productName with sum of the ProductQTY?Regards
AngellaHi Angella
Thanks for reaching me. Here is the code to get sum of the product. In this ColProduct is the source data and ColProductSum is grouped in one
//Source Data
ClearCollect(
ColProduct,
{
ProductQty: 10,
ProductName: “Apple”,
ID: 1
},
{
ProductQty: 5,
ProductName: “Orange”,
ID: 2
},
{
ProductQty: 4,
ProductName: “Apple”,
ID: 3
},
{
ProductQty: 6,
ProductName: “Orange”,
ID: 4
},
{
ProductQty: 7,
ProductName: “Kiwi”,
ID: 5
}
);//Final
ClearCollect(
ColProductSum,
DropColumns(
AddColumns(
GroupBy(ColProduct,”ProductName”,”ProductNameGrouping”),
“SumOfValue”,Sum(ProductNameGrouping,ProductQty)
),
“ProductNameGrouping”
)
);Thanks
Stalin Ponnusamy
Hi, Thank you so much for the code, can i do something like in a collection if there’s any duplicate row , one of them need to be marked as excluded by the user in gallery , if somehow user doesn’t mark it as excluded , then it should give a validation and wont let the user to submit the list
