Dropdown boxes are really useful when used in conjunction with a Gallery to drill down and reduce a large data set to a much smaller one, so specific Records can be identified

The best way to construct Dropdown boxes is to create their content from the data they represent.  For this to work effectively, the contents of the Dropdown must be formatted properly

Formatting the Contents of Dropdown Boxes

Using functions in Dropdown Boxes

Though Power Apps has a number of functions that can be used to reduce the number of records displayed in a Gallery, not all of them prove useful with a Dropdown box

Very Useful Functions

Distinct evaluates a formula and returns a one-column Table of the results with duplicate values removed. The name of the column is Result

Sort & SortByColumns sort a Table based on a particular formula or format, such as alphabetical order. Of the two, SortByColumns is the more flexible function and I will be using SortByColumns in this article rather than Sort

Occasionally Useful Functions

Filter finds all the Records in a Table that satisfy a particular formula and discards those that don’t. The Filter function can be sometimes be useful in a Dropdown box depending how it is used

Not-So-Useful Functions

LookUp finds the first record in a table that satisfies a formula.  As this may exclude valid records it is of limited benefit in this scenario

Search finds records in a table that contain a particular string.  Whilst this would be helpful, as it requires a parameter to be input so doesn’t lend itself particularly well for use with a Dropdown box

Filter, Distinct & SortByColumns

In this article I cover how to use a combination of Filter, Distinct and Sort to drill down into the Records in a table to help find the particular Record you are looking for

The best order to use these 3 functions is:

  1. Filter the data first to remove unnecessary records
  2. Use the Distinct function to remove duplications
  3. SortByColumns can then order the remaining records into your preferred structure

This can be represented as a nested formula with Filter at the centre and SortByColumns being the outer function: SortByColumns(Distinct(Filter())).  Structuring the formula in this way has the advantage that where possible (depending on the Data Source), Power Apps will delegate the execution of the Filter operation to the Data Source, reducing the risk of a truncated data set being returned

Creating the Dropdowns

Below is the Table of data. The Continent Column will be used in the dropdowns and you can see there a 5 continents in the table with multiple entries, North America, Europe, Asia, South America and Africa

Hard Coded

Items: ["Africa","Asia","Europe","North America","South America"]

The easiest but the least flexible way to specify the contents of the Dropdown is to hard code.  The upside is you can be very specific about the contents of the Dropdown and the order in which they are displayed.  The downside is that if a Country in a new Continent is added to the data set, such as Australia, the app itself needs to be updated

All Records

Items: Country.Continent

Here all the records in a the Continent column are displayed, one for each record.  Not a great user experience!

Distinct Records

Items: Distinct(Country,Continent)

An improvement on the previous option, but we should sort the Continents alphabetically

Sorted & Distinct Records

Items: SortByColumns(Distinct(Country,Continent),"Result")

Now the data is sorted alphabetically this is a much better solution

Sorted, Distinct & Filtered Records

Here a Filter is added that only selects the Continents that start with “A”

This been hard coded into the dropdown (which is why using the Filter function isn’t alway useful with a Dropdown box.  But for certain circumstances it may be an viable option.  A better way would be to use a Dropdown box combined with a Text Input box containing a Filter or Search function to work together to refine a table   

Items: SortByColumns(Distinct(Filter(Country.Continent,Left(Continent,1)="A"),Continent),"Result")

References

Other Combinations. Included for Completeness...

Filtered Records

Here just the Filter function is used.  Of limited use with removing the duplicates

Items: Filter(Country.Continent,Left(Continent,1)="A")

Sorted & Filtered Records

Again, without the use of the Distinct function, the duplicates remain and iby itself this is of limited use

Items: SortByColumns(Filter(Country.Continent,Left(Continent,1)="A"),"Continent")

Distinct & Filtered Records

More useful, but ideally would be sorted too, as in the earlier example

Items: Distinct(Filter(Country.Continent,Left(Continent,1)="A"),Continent)

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top