Power Apps – Filter Gallery to only show Duplicate values

When building enterprise applications in Power Apps, data quality is critical. One common scenario in asset management, HR apps, or CRM systems is identifying duplicate entries. While removing duplicates is often the goal, there are times when you need to display only duplicates for auditing, validation, or troubleshooting purposes.

Power Apps doesn’t provide a native “distinct count” or “duplicates” function like SQL, but by leveraging collections, GroupBy, AddColumns, and Filter, you can efficiently detect and display duplicate records in your gallery.

In this article, I’ll walk you through real-world techniques for filtering a gallery to show only duplicate values, with practical examples and performance tips learned from building enterprise Power Apps solutions.

Why Show Only Duplicate Values?

Showing duplicate entries is valuable for several reasons:

1. Detecting data quality issues: Quickly spot duplicate asset assignments, user accounts, or inventory items.
2. Validating form submissions: Ensure fields that should be unique, like serial numbers or emails, are not repeated.
3. Creating audit or review interfaces: Admin users can review duplicates for corrective actions.
4. Preventing business errors: Duplicates in inventory, payroll, or CRM data can lead to financial or operational issues.

By highlighting duplicates, you give users actionable insight, allowing your Power Apps solution to proactively maintain data integrity.

Scenario Example: Asset Management

Imagine an Asset Management app where each user should have only one type of asset assigned—for instance, a laptop or mobile phone.

We want to create a gallery that only displays records where a user has duplicate asset types assigned. For example:

A dropdown filter allows admins to select which asset type to check for duplicates.

User A has two mobile phones assigned.

The gallery should display only these duplicate entries.

Step 1: Load Your Data into a Collection

For performance and flexibility, it’s best to load your SharePoint list or Excel table into a local collection first.

ClearCollect(colRecords, 'Asset Database')

• colRecords – the local collection storing all assets.
• Using ClearCollect() ensures the collection is refreshed each time the screen loads, which is important if your data changes frequently.

Step 2: Identify Duplicate Values

Power Apps doesn’t have a native “duplicates” function, but you can combine GroupBy, AddColumns, and CountRows to detect duplicates.

For example, to find duplicate asset assignments by user:

ClearCollect(
    colDuplicates,
    Filter(
        AddColumns(
            GroupBy(
                Filter(colRecords, 'Asset Type' = "Mobile Phone" && Status.Value = "Assigned"),
                "User",
                "GroupedData"
            ),
            "DuplicateCount",
            CountRows(GroupedData)
        ),
        DuplicateCount > 1
    )
)

Explanation:

• Filter(colRecords, 'Asset Type' = "Mobile Phone" && Status.Value = "Assigned") – narrows down the dataset to the asset type you want to check.
• GroupBy(..., "User", "GroupedData") – groups records by the user field.
• AddColumns(..., "DuplicateCount", CountRows(GroupedData)) – counts how many records exist per user.
• Filter(..., DuplicateCount > 1) – returns only groups where duplicates exist.

This creates a collection of duplicate records, which we can use to filter the gallery.

Step 3: Filter Gallery to Show Only Duplicates

Now that we have a colDuplicates collection, we can filter our gallery to show only the matching duplicate items.

Filter(
    colRecords,
    User in colDuplicates.User
)

• This ensures that only records associated with users who have duplicates are displayed.
• You can dynamically replace User with any field you want to check for duplicates, such as Email or Asset Number.

Optional: Highlight Duplicates in the Gallery

For better visibility, consider conditionally formatting the gallery to highlight duplicates:

If(
    ThisItem.User in colDuplicates.User,
    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 the gallery template.
• This visual cue improves usability, especially for audit and review scenarios.

Handling Multiple Fields

Sometimes duplicates are determined by more than one field. For example, you may want to find duplicates based on both First Name and Last Name.

Modify your GroupBy to include multiple columns:

GroupBy(
    colRecords,
    "FirstName",
    "LastName",
    "GroupedData"
)

Then follow the same AddColumns and Filter steps to create a collection of duplicates.

Performance Considerations

Working with duplicates in Power Apps can be resource-intensive if your dataset is large. Here are some real-world tips:

1. Use Collections: Always bring data locally with ClearCollect() to reduce repeated calls to SharePoint.
2. Index SharePoint Columns: If filtering by fields such as User or Asset Type, ensure they are indexed to improve lookup speed.
3. Limit Non-Delegable Functions: GroupBy and CountRows are not delegable, meaning Power Apps can only process 500 records by default. Increase this limit in app settings or optimize your data source.
4. Batch Processing: For extremely large datasets, consider processing duplicates in batches or using Power Automate to pre-process the data.
5. Live Updates: If users are adding or modifying items, ensure colDuplicates is refreshed after any change to keep the gallery accurate.

Step 4: Dynamic Dropdown Filter

To give users control over which duplicates to display, add a dropdown menu:

Dropdown1.Items = ["Duplicate Laptops", "Duplicate Mobile Phones"]

Then, in the gallery’s Items property, wrap your filter in an If() statement:

If(
    Dropdown1.Selected.Value = "Duplicate Mobile Phones",
    Filter(
        AddColumns(
            GroupBy(
                Filter(colRecords, 'Asset Type' = "Mobile Phone" && Status.Value = "Assigned"),
                "User",
                "GroupedData"
            ),
            "DuplicateCount",
            CountRows(GroupedData)
        ),
        DuplicateCount > 1
    )
)

• This approach makes the app dynamic and reusable for multiple asset types.
• You can extend this to other asset types without modifying the core logic.

Real-World Applications

This approach is particularly useful in enterprise Power Apps projects:

1. Asset Management: Identify users with multiple laptops or phones.
2. HR Systems: Detect duplicate employee records by email or ID.
3. Inventory Management: Find duplicate inventory items or serial numbers.
4. Audit Applications: Highlight duplicates for administrative review.

From my experience, visualizing duplicates reduces errors and improves compliance significantly, especially in large organizations where data quality can be a challenge.

Conclusion

Filtering a Power Apps gallery to show only duplicate values is a powerful tool for data quality, auditing, and asset management. By leveraging collections, GroupBy, AddColumns, and Filter, you can:

• Detect duplicates efficiently without SQL.
• Provide dynamic filtering using dropdowns.
• Highlight duplicate records visually.
• Maintain performance even with moderately large datasets.

With these techniques, you can build robust, user-friendly apps that help organizations detect and manage duplicates effectively, all within the Power Apps interface.

Daniel Lutton

From my early days on the helpdesk through roles as a service desk manager, systems administrator, and network engineer, I’ve spent more than 25 years in the IT world. As I transition into cyber security, my goal is to make tech a little less confusing by sharing what I’ve learned and helping others wherever I can.