When using the Power Apps Dropdown Control, are there any items you wish to eliminate that are redundant or duplicates? If so, this Power Apps guide will assist you in getting rid of it.
In this Power Apps Tutorial, we will see how to remove duplicates from a dropdown control based on different scenarios, such as:
- PowerApps dropdown remove duplicates from SharePoint list
- PowerApps dropdown remove duplicates from collection
- PowerApps dropdown remove duplicates from dataverse
- Power Apps dropdown sort by distinct value
- PowerApps dropdown remove duplicates from excel
- PowerApps dropdown removes duplicate null values
What is a distinct function of Power Apps?
In Power Apps, there is a function called “Distinct” that applies a formula to each record in a table and returns a one-column table of the results that remove duplicate values. The column is called “Result.”
The syntax of the distinct() is:
Distinct(<Source>,<expression>)
Where,
- Source: The table or data source name from which distinct values will be removed.
- Expression: An expression evaluated for each row in the input table and returns values for the distinct operation.
Continue reading to learn how to use Distinct() on the Power Apps dropdown control.
Also read: How to Work With Power Apps Dropdown Blank Values
Power Apps dropdown removes duplicate from SharePoint
Here we will see how to remove duplicates from the SharePoint list while using the Power Apps dropdown control. Suppose we have a SharePoint list named “Vehicles” with a single-line column “Color“.
Now, using the above column data, we will create a dropdown control, making sure not to add duplicate data.
First, we must connect this SharePoint list to the Power Apps. To accomplish this, take the following steps:
- On Power Apps, go to Data (You can find it on the left side navigation panel).
- Then click on “+ Add data“
- Search for SharePoint in the search bar.
- Click on SharePoint.
- Choose your connection.
- Select the site address.
- Choose the SharePoint list and click on Connect.
Now, we can find the data source in the Power Apps Data section. Next, add a dropdown control to the screen. To add the Color column’s value, insert the below expression on the dropdown’s items property.
Items = Vehicles.Color
Where,
- Vehicles: The name of the SharePoint list
- Color: The name of the column
We can see the dropdown will come with repeated or duplicate values shown below:
As per our requirement, we want to remove all the duplicate items from the dropdown list. To do this, we need to insert the below expression on the dropdown’s items property.
Items= Distinct(Vehicles,Color)
Now we can see, that it will remove all the duplicate data from the dropdown like below:
This is how to remove duplicates from the Power Apps dropdown control.
Also Read: How to Set Default Value in Power Apps Dropdown
Power Apps removes duplicate from the collection
In this section, we will see how to remove duplicates from the collection while using the Power Apps dropdown control.
Let’s create a collection named CollMobile with some random columns and data on the Power Apps screen’s OnVisible property.
OnVisible = ClearCollect(CollMobile, {Name:"Iphone12",PurchaseDate: Date(2021,5,13),Owner:"James"},{Name:"Iphone12ProMax",PurchaseDate: Date(2021,7,22),Owner:"Johney"},{Name:"Iphone13",PurchaseDate: Date(2022,3,21),Owner:"Harry"},{Name:"Iphone13Pro",PurchaseDate: Date(2022,4,13),Owner:"Patrick"},{Name:"Iphone13ProMax",PurchaseDate: Date(2022,6,15),Owner:"Patrick"},{Name:"Iphone12",PurchaseDate: Date(2021,5,13),Owner:"Lily"})
Where,
- CollMobile: The name of the collection
- Name, PurchaseDate, Owner: The column names of the collection
Then, save and publish the app. Now close the app and reopen it again. We can see the collection is created on the Power Apps. Go to View > Collections > select CollMobile. You can find the collection that has been created.
Next, we will create a dropdown list using the Name column from the above collection. On the dropdown’s items property insert the below expression.
Items = CollMobile.Name
It will show all of the mobile names on the dropdown control, including duplicate values, as shown below:
To remove the duplicate value from the above dropdown, we need to insert the below expression on the dropdown’s Items property.
Items = Distinct(CollMobile,Name)
Once the above formula is applied, we can see it will remove the duplicate values from the dropdown control like below:
This is how to remove duplicates on the Power Apps dropdown from the collection.
Check out: Power Apps Dropdown Control with SharePoint
Power Apps dropdown sort distinct value
Here, we will see how to sort the distinct values on the Power Apps dropdown control.
To work with this, we are going to use the above Power Apps dropdown control that contains the mobile name from the CollMobile collection.
Now, we want to arrange the dropdown’s data in descending order. To do so, insert the following expression on the dropdown’s Items property.
Items = Sort(Distinct(CollMobile,Name),Result,Descending)
Once the above formula is applied to the dropdown, we can see it will display the items in descending order like below:
This is how to sort the distinct value on the Power Apps dropdown control.
Power Apps dropdown removes duplicate dataverse
Here, we will see how to remove duplicates from the dataverse table via dropdown. Let’s build a new dataverse table on the Power Apps. To build this, the following steps are:
- On the Power Apps, go to data > Add data > create a new table.
- Give a table name (ex: Contacts) and click on Create.
- Click on the New column to add a new column to the table. Give a display name and data types. (ex: Contact – Number)
- Add some random data to the dataverse table. Then click on close. The table is shown in the Data section.
- In the above table, we can see there is repeated name with different data(ex: John).
- Next, add a dropdown control to the screen and use the above Name on that dropdown control. To do so, insert the following expression on the dropdown’s items property.
Items = contacts.Name
Where,
- contacts: The name of the dataverse table name
- Name: The name of the column
Once the formula is applied to the dropdown, it will display all the names that are present in the table like below:
To remove the duplicate value from the dropdown, we need to insert the below expression on the dropdown’s Items property.
Items = Distinct(contacts,Name)
We can see, that now the dropdown will appear with the unique values.
To see the related value of the selected item, we will insert a gallery control onto the screen and inject the below formula into the gallery’s Items property.
Items = Filter(contacts,Name = Dropdown_Name.Selected.Result)
Where Dropdown_Name is the name of the dropdown control.
Now, launch the app in preview mode or press F5 to launch it. Choose an item from the dropdown menu (ex: John). As we can see, it will display all items with the name John.
This is how to show distinct values on the Power Apps dropdown from the dataverse table.
Also, check: Power Apps Gallery Group By [With Real Examples]
Power Apps dropdown removes duplicate from excel
Here, we will see how to work with the Power Apps dropdown removes duplicates from excel.
Suppose, we have prepared an excel sheet with some sample data and format that sheet as an excel table like the below:
- Add this excel table to the Power Apps. Go to Data > Add data > Excel Online(Business) > Choose the data set > choose the excel file > Connect. Now, we can find the excel sheet on the Power Apps.
- Next, add a data table to display all the ‘FlightNo.’. Select the data table > Edit fields > check the FlightNo. > Add.
- Add a dropdown control to the screen and insert the below expression on the dropdown’s Items property.
Items = Distinct(FlightTable,'FlightNo.')
Where,
- FlightTable: The name of the excel table
- FlightNo.: The name of the table’s column
Now we can see the unique values on the dropdown control like below:
This is how to show distinct values from excel on powerapps dropdown control.
PowerApps dropdown removes duplicate null values
Here, we will see how to remove duplicate null values, or blank values from a Power Apps dropdown control.
Suppose, we have created a collection on the Power Apps screen’s OnVisible property that contains some blank values like the below:
OnVisible = ClearCollect(ColPopulation,{Country:"USA",Population:"7896300"},{Country:"UK",Population:"702560"},{Country:"Japan",Population:"566305"},{Country:" ",Population:"758691"},{Country:"USA",Population:"76300"},{Country:" ",Population:"7586457"});
Where,
- ColPopulation: The name of the collection
- Country, Population: The name of the collection header
- Next, save and publish the app. Then close and reopen it again. We can see the collection is created in the Power Apps. Go to view > Collection.
- Add a dropdown control to the screen and insert the below expression on the dropdown’s Items property to display the list of countries in it.
Items = ColPopulation.Country
But we want to remove the blank items from the dropdown and only display the unique value. To achieve this requirement, we will insert the below expression into the dropdown’s Items property.
Items = Filter(Distinct(ColPopulation,Country), Not(IsBlank(Trim(Result))))
Once the formula is applied, we can see it will remove the blank values as well as it will display only distinct values within the Power Apps dropdown control.
This is how to remove duplicate null values from the PowerApps dropdown.
Also, you may like the below Power Apps Tutorials:
- How to use Power Apps Combo Box Control?
- Power Apps Cascading Dropdown Control
- Power Apps Gallery Patch [With Various Examples]
- Power Apps Gallery SharePoint [With 15+ Examples]
- Power Apps Gallery Filter [With Real Examples]
- Power Apps Data Table [Complete Guide]
- Power Apps Radio Button
- Power Apps SharePoint Button
- How to use Power Apps List box control?
Conclusion
From this Power Apps tutorial, we have learned all about how to work with duplicate values. We also discussed the following topics:
- What is a distinct function in Power Apps?
- Working with the powerapps dropdown removes duplicates from the SharePoint list.
- The powerapps dropdown removes duplicates from the collection.
- How to work with PowerApps Dropdown to remove duplicates from Dataverse
- What is Power Apps dropdown sort distinct value?
- How to work with PowerApps Dropdown to remove duplicates from Excel
- How to remove duplicate null values from the PowerApps dropdown?
After working for more than 15 years in Microsoft technologies like SharePoint, Office 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (9 times). I have also worked in companies like HP, TCS, KPIT, etc.