Power Apps - Filter Gallery to only show Duplicate values -

It is common to want to filter your gallery to remove duplicate values, but at some point, you may want to only show the duplicate items. In this article, We will show you how to within power Apps, Filter your Gallery to only show duplicate items.

Power Apps Filter Gallery to Only Show Duplicate Values

Example Scenario-

An Asset management app has been created with a Gallery that points to a SharePoint list, Asset database. All users should only have one type of asset assigned to them, for example, a laptop or mobile phone. A filter is to be created to only see records if a user has a duplicate Asset type of mobile phone with the status of assigned. A dropdown is created at the top of the page with the filter option.

Solution –

Use the below as the items property for the Gallery

If(Dropdown1.Selected.Value = ‘Duplicate Laptops’,

Filter(
   AddColumns(
      GroupBy(
         Filter(
         'Asset Database', 
            'Asset Type = "Laptops" && 
            Status.Value = "Assigned"
         ),
         "YourUserFieldName"
         "Data"
      ),
      "Duplicates",
      CountRows(Data)
   ),
   Duplicates > 1
)

Why Show Only Duplicate Values?

Identifying duplicate entries in a data source can be useful for:

Detecting and resolving data quality issues.
Validating form submissions where unique fields should not repeat.
Creating audit or review interfaces for admin users.

Power Apps does not provide a built-in “distinct count” function like traditional SQL queries, but with a bit of creativity, you can build the logic yourself using collections and filter functions.

Step-by-Step: Filtering Gallery to Show Duplicates

Let’s say you have a collection or data source named colRecords and you want to identify duplicates based on the Email field.

1. Load Your Collection

If you’re using a SharePoint list or Excel table, load it into a collection first for performance:

ClearCollect(colRecords, YourDataSource)

2. Create a Collection of Duplicate Values

You can use AddColumns, GroupBy, and Filter to find duplicates:

ClearCollect(
    colDuplicates,
    Filter(
        AddColumns(
            GroupBy(colRecords, "Email", "GroupedData"),
            "Count",
            CountRows(GroupedData)
        ),
        Count > 1
    )
)

This creates a new collection, colDuplicates, that contains only the emails that appear more than once.

3. Filter Gallery Using Duplicate Keys

Now filter your gallery to show only records from the original collection that match the duplicate keys:

Filter(
    colRecords,
    Email in colDuplicates.Email
)

This ensures your gallery only shows records where the Email field has multiple occurrences in your dataset.

Optional: Highlight Duplicates Visually

To make duplicates stand out in the gallery, you can conditionally format items:

If(
    ThisItem.Email in colDuplicates.Email,
    RGBA(255, 200, 200, 1),  // Light red background for duplicates
    RGBA(255, 255, 255, 1)   // Default white background
)

Apply this to the Fill property of your gallery item template.

Tips for Using This Method

Performance: Avoid doing these operations on very large datasets directly from the data source. Use Collect() or ClearCollect() to work locally.
Multiple Fields: If you need to identify duplicates across multiple fields (e.g., First Name and Last Name), adjust the GroupBy to include both.
Live Updates: If your app is live and users can add/edit data, ensure colDuplicates is refreshed after data changes.

Wrapping Up

Filtering a Power Apps gallery to show only duplicate values is a great way to manage data quality and create smart applications. By leveraging collections and filters, you can build a highly efficient and user-friendly way to detect and handle duplicate entries.

This approach gives you full control over how duplicates are detected, displayed, and managed—all within the Power Apps interface.

Power Apps – Filter Gallery to only show Duplicate values