Power Apps Drop Down with Distinct Values and 'All' Option

Introduction

Dropdown controls are essential for filtering and navigation in Power Apps. A common requirement is to display only distinct values from a data source (like a SharePoint list) and include a default ‘All’ option for easy selection. This post shows how to build a Power Apps dropdown with unique values and a default ‘All’ using PowerFx.

Scenario

Suppose you have a SharePoint list called FolderMappingDetails with a column named Database. You want your dropdown to:

  • Show only unique database names
  • Include an ‘All’ option at the top
  • Work seamlessly for filtering or reporting

PowerFx Formula

Here’s the PowerFx formula to achieve this:

Ungroup(
    Table(
        {databases: Table({Result: "All"})},
        {databases: ForAll(Sort(Distinct('FolderMappingDetails', Database), Value), {Result: ThisRecord.Value})}
    ),
    databases
)

How It Works

  • Distinct(‘FolderMappingDetails’, Database): Gets unique values from the Database column.
  • Sort(…, Value): Sorts the distinct values alphabetically.
  • ForAll(…, {Result: ThisRecord.Value}): Wraps each value in a record for dropdown compatibility.
  • Table({Result: “All”}): Creates a record for the ‘All’ option.
  • Ungroup(…, databases): Flattens the table so all options appear in a single dropdown list.

Step-by-Step Guide

  1. Connect your SharePoint list (FolderMappingDetails) to Power Apps.
  2. Add a Dropdown control to your app.
  3. Set the Items property of the dropdown to the formula above.
  4. Set the Value property of the dropdown to Result.
  5. Use the dropdown for filtering (e.g., show all items if ‘All’ is selected, otherwise filter by the selected database).

Practical Tips

  • The ‘All’ option is useful for resetting filters or showing all records.
  • You can customize the label (‘All’) to suit your app’s language or context.
  • This approach works for any data source, not just SharePoint.
  • For large lists, consider delegable queries for better performance.

Example Output

Dropdown with All Option

Conclusion

Adding a default ‘All’ option to a distinct dropdown in Power Apps is simple and powerful. Use this pattern to improve user experience and make your apps more flexible for reporting and filtering.