Power Apps’ Ungroup(): Combining Tables for Dynamic Dropdowns

In Power Apps, building dynamic, user-friendly dropdowns often involves fetching distinct values from a data source and then prepending or appending custom options. A common challenge arises when you want to merge literal records (like an “All” choice) with a result set returned by functions such as Distinct(). In this post, we’ll deep-dive into the Ungroup() function and show you how it elegantly solves this problem—using the real-world example of a visitor check-in app where we need to list hosts in a dropdown.

📋 The Scenario: Visitor Management App

Imagine you’ve built a Canvas App called Visitor Check-In. Your SharePoint list, Visitor Records, tracks each visitor’s details, including a Person or Group column named Host.

On your front-desk interface, you want a dropdown that lets staff:

1. Select “All” (to see every visitor for all hosts) or
2. Pick a specific host’s name (to filter the gallery by that host).

Fetching the distinct host names is simple:

Sort(
    Distinct('Visitor Records', Host.DisplayName),
    Value,
    SortOrder.Ascending
)

But how do you add an “All” option at the top of those results?

❌ Why Common Approaches Fail

Many developers try:

[ {Value: "All"} ] & Sort(
    Distinct('Visitor Records', Host.DisplayName),
    Value,
    SortOrder.Ascending
)

Table({Value: "All"}) & Sort(
    Distinct('Visitor Records', Host.DisplayName),
    Value,
    SortOrder.Ascending
)

Both produce schema mismatches or invalid-argument errors. Power Apps expects each side of the & operator to be a table with the same structure (same column names and types). Distinct() returns a one-column table (Value), but Table({Value:"All"}) is a distinct construct that doesn’t line up neatly.

✅ The Ungroup() Solution

The trick is to use Table() to create a nested table that contains both the literal record and the result of Distinct(), then flatten that nested table back into a single column using Ungroup(). Here’s the magic formula:

Ungroup(
    Table(
      { Value: "All" },
      Sort(
        Distinct('Visitor Records', Host.DisplayName),
        Value,
        SortOrder.Ascending
      )
    ),
    Value
)

Let’s break down each component of this code:

1. Distinct('Visitor Records', Host.DisplayName)
   • Purpose: Extract unique host names from the Visitor Records list.
   • Benefit: Ensures duplicate host entries don't clutter the dropdown.
2. Sort(..., Value, SortOrder.Ascending)
   • Purpose: Sorts the distinct host names in alphabetical order.
   • Benefit: Enhances user experience by providing an intuitive, well-ordered list.
3. Table({ Value: "All" }, ...)
   • Purpose: Manually creates a table with a single record for the option "All" and concatenates it with the sorted distinct host names.
   • Benefit: Provides a universal option that allows users to bypass filtering by a specific host.
4. Ungroup(..., Value)
   • Purpose: Flattens the combined table into a single-column table by merging all records.
   • Benefit: Ensures the final output is simple and can be easily bound to dropdown controls without any nesting issues.

🛠 Step-by-Step Implementation

1. Insert a Dropdown on your Canvas screen and set its Items property to the Ungroup() formula above.

2. Set the dropdown’s Default property to "All".

3. Reference Dropdown1.Selected.Value when filtering your gallery:

Filter(
    'Visitor Records',
    Dropdown1.Selected.Value = "All"
      || Host.DisplayName = Dropdown1.Selected.Value
)

Why and When Should You Use Ungroup?

• Flattening Complex Data Structures: When data sources are combined or grouped, columns may end up containing nested tables. Ungroup removes that extra layer, delivering a simplified, single‐level table.
• Enhancing Control Binding: Many PowerApps controls expect a flat table. By using Ungroup, you ensure that these controls receive data in the proper format, avoiding errors or unexpected behaviors.
• Improved Data Manipulation: Once flattened, the data is much easier to filter, sort, and manipulate. This leads to cleaner and more maintainable formulas in your app.

Benefits of Using Ungroup in Our Scenario

• Clean Data Presentation: The final output is a neatly flattened, single-column table containing both the manual "All" record and the dynamically sourced host names—ideal for use in dropdowns.
• Ease of Use: Flattening the nested table avoids the need for additional parsing or custom functions.
• Flexibility: This method allows you to easily include more custom records without reengineering the data structure.

Conclusion

The Ungroup function is invaluable when developing in PowerApps, particularly in scenarios like our Visitor Pre-Registration and Check-In System where merging static and dynamic data is essential. By using Ungroup to flatten a nested table, we can ensure that our enhancements—such as dropdown controls for filtering—are robust, user-friendly, and maintainable.

Whether you’re building a visitor management solution or any other application that deals with composite data sources, mastering Ungroup can significantly streamline your development process. Happy PowerApping!