How to Show Distinct Values in a Power Apps Combo Box (Text and Person Columns)

I was building a customer lookup screen for a subscription team when I noticed something odd in the combo box: Blair appeared three times, Johanna twice, and the list of account managers repeated the same three names over and over. The combo box was working correctly; it was simply showing one entry for every row in the SharePoint list, and many customers and managers appear in several rows.

What the team needed was each name once. In Power Apps, that’s the job of the Distinct function, and it works for text columns and Person columns alike.

Quick answer: Set the combo box’s Items property to Distinct('Customer Details', Title), and show the Value column (in the classic combo box, choose Value as the Primary text). For a Person column, use Distinct('Customer Details', 'Subscription Handled By'.DisplayName). Wrap it in Sort(..., Value, SortOrder.Ascending) to sort the names.

In this tutorial, I’ll show you both cases with that customer list, explain how to sort the results, and cover what changes when your list grows beyond a few hundred items.

The example SharePoint list

The list is called Customer Details and has four columns: User Name (the Title column), Country (single line of text), Subscription Type (Choice) and Subscription Handled By (Person or Group).

Because one customer can hold several subscriptions, the same user name appears in several rows, and the same account managers handle many customers. The goal is a combo box that lists each customer once, and another that lists each account manager once.

What the Distinct function does

Distinct() goes through every record in a table, works out a value for each one, and returns a one-column table with the duplicates removed. According to Microsoft’s Distinct reference, the name of that column is Value. The pattern is:

Distinct( Table, Formula )

The formula can be a column name, such as Title, or something based on a column, such as a person’s display name. You can then use the resulting table anywhere Power Apps expects a table, including the Items property of a combo box.

One thing to watch: older apps and tutorials, including earlier versions of this one, refer to the column as Result. If you’re following one of those and see an error, change Result to Value.

Show distinct values from a text column

Let’s start with the customer names. Here’s the result we’re after, with each name shown once:

Insert a Combo box on the screen and set its Items property to:

Distinct('Customer Details', Title)

Next, tell the combo box which column to show. In the classic combo box, which I’m using here, select Edit next to Fields in the properties pane and choose Value as the Primary text. If you’re using the modern combo box, set its ItemDisplayText property to ThisItem.Value instead.

If people should pick only one customer, set SelectMultiple to false. This matters especially on the modern combo box, where multiple selection is now turned on by default.

Play the app and open the combo box: each user name now appears only once, however many rows it has in the list. To show them in alphabetical order, wrap the formula in Sort():

Sort(
    Distinct('Customer Details', Title),
    Value,
    SortOrder.Ascending
)

Show distinct values from a Person column

Person columns work a little differently, because each value is a record with several fields, such as DisplayName, Email and Claims. To remove duplicates, pick the field you want to compare, which for a list of names is DisplayName. Here’s the combo box we’re building:

Insert another combo box and set its Items property to:

Distinct('Customer Details', 'Subscription Handled By'.DisplayName)

Set the Primary text to Value again, and the combo box lists each account manager once. Sorting works the same way:

Sort(
    Distinct('Customer Details', 'Subscription Handled By'.DisplayName),
    Value,
    SortOrder.Ascending
)

Keep in mind that the result is a list of names, not person records. That’s perfect for filtering a gallery by account manager, but if you need to save the selected person back to a Person column, use a combo box bound to the column itself instead.

Large lists and delegation

Distinct has one important limit: it can’t be delegated to SharePoint. Instead, Power Apps downloads the first part of the list, 500 rows by default and up to 2,000 if you raise the data row limit in Settings, and removes duplicates from those rows only. On a bigger list, names that only appear further down simply won’t show up, and Power Apps displays a delegation warning to tell you (Microsoft Learn).

The way around it is to narrow the data first with a function that can be delegated, and then apply Distinct to the smaller result. StartsWith() is delegable on SharePoint text columns, and on the DisplayName of a Person column (SharePoint delegation list), so it’s a good fit for a searchable combo box:

With(
    {
        _Matches: Filter(
            'Customer Details',
            StartsWith('Subscription Handled By'.DisplayName, Self.SearchText)
        )
    },
    Sort(
        Distinct(_Matches, 'Subscription Handled By'.DisplayName),
        Value,
        SortOrder.Ascending
    )
)

Here, SharePoint does the filtering based on what the person types in the combo box, and Distinct only has to handle the matching rows. Make sure IsSearchable is turned on so the combo box accepts typing (combo box reference).

For very large lists, another option is to keep the unique values in their own small SharePoint list, such as a list of account managers, and use that list directly as the combo box’s Items. Then no de-duplication is needed at all.

Common problems

If the combo box shows blank rows, it’s usually because the Primary text still points to a column that doesn’t exist in the Distinct result. Only Value is available, so choose it in Fields (classic) or use ThisItem.Value in ItemDisplayText (modern).

If you see an empty entry in the list, some rows have no value in that column. Filter them out before de-duplicating, for example Distinct(Filter('Customer Details', !IsBlank(Country)), Country). And if names seem to be missing, check for the delegation warning described above.

These Power Apps combo box guides go well with Distinct:

• Remove Duplicates in Power Apps Dropdown [With Different Scenarios]
• How to Set Combo Box Default Value in Power Apps? [With Useful Examples]
• Power Apps Combo Box Vs Dropdown [Know the Differences]
• How to Set/Get Power Apps Combo Box Selected Value?
• How to Filter Power Apps Gallery Based On Combo Box Selection? [With Real Examples]
• How to Save Power Apps Combobox Multiple Values to SharePoint List?

Frequently Asked Questions