This topic is pretty interesting. If you look at the requirement here, it can’t be simpler than this. I need to remove duplicate items from a table/ collection. Or even more generic requirement to remove duplicate items from a gallery.
If you are coming from programming background or database SQL experience, it’s another day at your job. However Power Apps is a platform for all and here in Power Apps we have pro developers as well as Power Users trying to work their way to build enterprise ready apps using the platform.
So coming to the requirement. How do remove duplicate items from a collection. The first function that come to our mind is using the Distinct function. After all removing duplicate is nothing but the finding distinct items of a collection.
For this demo, I am using the below collection. Remember the same shall work for Tables and Gallery items.
If you observe the collection the below item is repeating.
{ Id: 3, Name: "PowerBI"}If we set the Items property to the below formulae the results are distinct by Name property of the collection.
As you can see, PowerBI comes once as expected. Well this is the simplest scenario we can think of. But Distinct function has it’s own issues. First and foremost, you can only use one column in Distinct. Also the result returns the only column specified in the Distinct function.
Let’s twist our input collection a bit.
Now we have three items for which PowerBI is coming. However there is a twist in this. Out of the three entries two have the same value for Id field which is 3. And then one more entry for PowerBI which has Id value of 5. In scenarios like this we are looking at finding the distinct items based on the combination of couple of fields here which is Id and Name.
Basically we are looking at distinct based on couple of columns. And in real life it can be a combination of even more than two.
For cases like this we will use the GroupBy function. Let’s see how we can achieve this using GroupBy.
Whatever columns I need, I have put it in the GroupBy clause. The Group name I have provided as Grouped. It can be any name you wish to give.
Look at the results carefully. As you can see the item with duplicate Id and Name combination has been filtered out and you can only see one item with combination of {Id: 3, Name: “PowerBI”}
We have the other item listed as well with Power BI – {Id:5, Name: “PowerBI”}
You can apply the same trick if you need to filter duplicate items for your gallery or your dropdown. While Distinct can be useful where you need to find distinct values based on expression and return a single column, GroupBy is very useful when you need to filter duplicate items from a collection based on more than one filter criteria and you need to return more than one column in the result set.
Hope this helped!
Debajit Dutta
Business Solutions MVP