Creating Dropdowns with Dynamic Data in Power Apps

Dropdowns are an essential UI element in Power Apps, allowing users to select values from a list. When working with dynamic data, dropdowns must be able to fetch, filter, and display values from data sources like SharePoint, SQL Server, Dataverse, or Collections. This guide provides a step-by-step detailed explanation on how to create, configure, and optimize dropdowns with dynamic data.

1. Understanding Dynamic Data in Dropdowns

What is Dynamic Data?

Dynamic data in dropdowns means that the options are not hardcoded but instead fetched from a data source, which could be:

SharePoint lists
SQL Server tables
Dataverse tables
Collections within Power Apps
APIs fetching real-time data

Why Use Dynamic Dropdowns?

✅ Ensures that the dropdown options automatically update when the data changes.
✅ Prevents manual updates to dropdown items.
✅ Allows filtering, searching, and cascading dropdowns.
✅ Improves app performance by loading only necessary data.

2. Adding a Dropdown Control in Power Apps

Open Power Apps → Create a new Canvas App.
Insert a Dropdown:
- Go to → Insert → Controls → Dropdown.
- Rename it to Dropdown1.

3. Populating a Dropdown with Dynamic Data

3.1 Using a Collection (Temporary Data Storage)

If you want to populate a dropdown with a collection of values, use:

ClearCollect(DropdownData, ["Option 1", "Option 2", "Option 3"])

Sets up a collection (DropdownData) containing three options.
Dropdown1 Items Property: DropdownData

Now, the dropdown will display Option 1, Option 2, and Option 3.

3.2 Populating a Dropdown from a SharePoint List

Scenario: Fetching Data from a SharePoint List

Assume we have a SharePoint List called ProductsList with a column ProductName.

Setting the Items Property

Dropdown1.Items = ProductsList.ProductName

This retrieves all product names from SharePoint.
Ensures dropdown updates automatically when SharePoint list changes.

Handling Duplicates in SharePoint Data

To remove duplicate values, use:

Dropdown1.Items = Distinct(ProductsList, ProductName)

Ensures that duplicate product names are not shown multiple times.

3.3 Populating a Dropdown from SQL Server

If your data is stored in SQL Server, use a Direct Connection.

SQL Table Example

Table: Categories

Columns: CategoryID, CategoryName

Setting the Dropdown Items Property

Dropdown1.Items = '[dbo].[Categories]'

This loads all category names.

Displaying Only Category Names

Dropdown1.Items = '[dbo].[Categories]'.CategoryName

3.4 Populating a Dropdown from Dataverse

If using Microsoft Dataverse, set the Items property as:

Dropdown1.Items = Choices('Dataverse Table Name'.ColumnName)

This fetches the lookup values from Dataverse.

4. Cascading (Dependent) Dropdowns

Cascading dropdowns allow one dropdown to filter another.

Scenario

Dropdown1: Shows a list of Categories.
Dropdown2: Shows only Products belonging to the selected category.

Step 1: Setting Up the First Dropdown (Categories)

Assume CategoriesList is a SharePoint list containing category names.

Dropdown1.Items = CategoriesList.Title

Step 2: Setting Up the Second Dropdown (Products)

Assume ProductsList has a Category column linking to CategoriesList.

Dropdown2.Items = Filter(ProductsList, Category = Dropdown1.Selected.Title).ProductName

Dropdown2 will now only display products that belong to the selected category in Dropdown1.

5. Searching and Filtering in Dropdowns

If the dropdown has many items, enable searching for better user experience.

5.1 Enabling Search in Dropdowns

Select the Dropdown Control
Go to Properties → Enable Allow Search ✅

5.2 Implementing a Search Box for Custom Filtering

If Dropdown1 contains many options, add a Text Input (SearchBox) to filter results dynamically.

Step 1: Insert a Text Input

Rename it to SearchBox.

Step 2: Modify the Dropdown Items Property

Dropdown1.Items = Filter(ProductsList, StartsWith(ProductName, SearchBox.Text))

Now, Dropdown1 only displays products matching the search input.

6. Default Selection for Dropdowns

By default, a dropdown selects the first item. You can set a default value.

6.1 Setting a Default Value Manually

Dropdown1.Default = "Select a Category"

This forces a placeholder text before selection.

6.2 Setting a Default Value from Data Source

To default to a specific category from SharePoint:

Dropdown1.Default = LookUp(CategoriesList, Title="Electronics").Title

This pre-selects “Electronics” in the dropdown.

7. Saving Dropdown Selections to a Data Source

7.1 Saving to SharePoint List

To save the selected value into a SharePoint list:

Patch(OrdersList, Defaults(OrdersList), {SelectedCategory: Dropdown1.Selected.Title})

This adds a new record with the selected category.

7.2 Saving to SQL Server

Patch('[dbo].[Orders]', Defaults('[dbo].[Orders]'), {CategoryID: Dropdown1.Selected.CategoryID})

Saves CategoryID from Dropdown1 into the Orders table.

8. Performance Optimization for Large Dropdowns

8.1 Use Delegation-Friendly Queries

Instead of using:

Dropdown1.Items = Filter(ProductsList, Category = "Electronics")

Use:

Dropdown1.Items = Filter(ProductsList, Category = Dropdown1.Selected.Title)

This ensures delegation works efficiently, especially with SharePoint & SQL.

8.2 Use Concurrent() to Load Multiple Dropdowns at Once

Concurrent(
    ClearCollect(CategoriesData, CategoriesList),
    ClearCollect(ProductsData, ProductsList)
)

This loads data faster by fetching categories and products simultaneously.

Conclusion

By using dynamic data sources, filtering, cascading dropdowns, and search functionality, you can build powerful and efficient dropdowns in Power Apps. Proper use of delegation, caching, and default selections ensures smooth app performance.

Would you like an example specific to your data source (SharePoint, SQL, or Dataverse)?