How to Remove Duplicates in Power Apps Dropdown

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“.

PowerApps dropdown distinct
PowerApps dropdown distinct

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.
Connect SharePoint to Power Apps
Connect SharePoint to Power Apps
  • Choose your connection.
  • Select the site address.
  • Choose the SharePoint list and click on Connect.
Connect SharePoint list to Power Apps
Connect SharePoint list to Power Apps

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
See also  Power Apps Cascading Dropdown Control

We can see the dropdown will come with repeated or duplicate values shown below:

powerapps dropdown remove duplicate value
powerapps dropdown removes the duplicate value

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:

Power Apps dropdown remove duplicates
Power Apps dropdown removes duplicates

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,

  1. CollMobile: The name of the collection
  2. 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.

powerapps dropdown removes duplicates from collection
powerapps dropdown removes duplicates from the collection

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:

powerapps dropdown removes duplicates from a collection
powerapps dropdown removes duplicates from a collection

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:

how to remove duplicate on Power Apps dropdown from collection
how to remove duplicate on Power Apps dropdown from collection

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:

Power Apps dropdown sort distinct value
Power Apps dropdown sort distinct value

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.
Create a new dataverse table on the Power Apps
Create a new dataverse table on the Power Apps
  • 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.
powerapps removes duplicates on dropdown using dataverse
powerapps remove duplicates on dropdown using dataverse
  • 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,

  1. contacts: The name of the dataverse table name
  2. Name: The name of the column
See also  Power BI Slicer - How to use with examples

Once the formula is applied to the dropdown, it will display all the names that are present in the table like below:

powerapps dropdown removes duplicates on dataverese
powerapps dropdown removes duplicates on dataverse

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.

powerapps dropdown removes duplicates dataverse table
powerapps dropdown removes duplicates dataverse table

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.

Power Apps distinct values on a filtered drop down from Dataverse
Power Apps distinct values on a filtered drop down from Dataverse

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.

powerapps dropdown distinct value from dataverse table
powerapps dropdown distinct value from dataverse table

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:

powerapps dropdown removes duplicates from excel
powerapps dropdown removes duplicates from excel
  • 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.
connect excel to the Power Apps
connect excel to the Power Apps
  • Next, add a data table to display all the ‘FlightNo.’. Select the data table > Edit fields > check the FlightNo. > Add.
powerapps dropdown removes duplicates from excel table
powerapps dropdown distinct from the excel table
  • Add a dropdown control to the screen and insert the below expression on the dropdown’s Items property.
Items = Distinct(FlightTable,'FlightNo.')

Where,

  1. FlightTable: The name of the excel table
  2. FlightNo.: The name of the table’s column
See also  How to Convert an Array to a String in Power Automate [3 Different ways]

Now we can see the unique values on the dropdown control like below:

powerapps dropdown distinct from excel
powerapps dropdown distinct from excel

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,

  1. ColPopulation: The name of the collection
  2. Country, Population: The name of the collection header
powerapps dropdown remove duplicates null value
powerapps dropdown remove duplicates null value
  • 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.
powerapps dropdown remove duplicate blank values
powerapps dropdown remove duplicate blank values
  • 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
Remove blank values in dropdowns
Remove blank values from dropdowns

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.

PowerApps dropdown removes duplicate null values
PowerApps dropdown removes duplicate null values

This is how to remove duplicate null values from the PowerApps dropdown.

Also, you may like the below Power Apps Tutorials:

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?
  • Hi Bijay, This is very detailed article and guide. I got this query – I got SharePoint list is called DataForHardwareALert and I have two columns in this list. 1) HardwareName 2) HardwareStatus

    Both column list is set to allow duplicate entries in sharepoint list. I can see duplicate entries of the same HardwareName(Test1) with different HardwareStatus(Offline or Online) with different timestamps, For e.g If Test1 has two entries saved where HardwareStatus is Offline but at different timestamps under the “Created” column in the sharepoint list.

    Q1) I want to use this SharePoint list to be displayed in the power apps’s gallery. I want to use the most recent (timestamp) for the same HardwareName and HardwareStatus. For e.g I got 2:00 AM and 04:00 AM entries for Test1, I want to lsit the 04:00 AM entry in power Apps gallery.

  • >