web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Combine 3 Collections ...
Power Apps
Suggested Answer

Combine 3 Collections in 1 and Group records by Employee Name

(2) ShareShare
ReportReport
Posted on by CU17101151-0 4
I have 3 Collections
Collection 1
Collection 2
Collection 3
All 3 collections have same Employee Name and following Columns
Employee Name    Employee ID    Test ID     Test Results
 
I want to create a new collection from 3 collections and group results by employee name:
So Results will look like this
 
Employee Name    Employee ID    Test ID (Collection 1)     Test Result (Collection 1)     Test ID (Collection 2)   Test Result (Collection 2)     Test ID (Collection 3)     Test Result(Collection 3)
John Smith             10101                          101                                 100%                                      102                        75%                                        103                               90%
 
I am trying Group By and Add columns but not having success
 
Thank you
Categories:
Building Power Apps
I have the same question (0)
  • Suggested answer
    rzuber Profile Picture
    rzuber 552 Moderator on at
    How about this? 
    // Main Context Object
With(
    // Assistant Context
    With(
        {
            // Using Your Examples
            collection1: [{ 'Employee Name': "John Smith", 'Employee ID': 10101, 'Test ID': 101, 'Test Result': 1 }],
            collection2: [{ 'Employee Name': "John Smith", 'Employee ID': 10101, 'Test ID': 102, 'Test Result': .75 }],
            collection3: [{ 'Employee Name': "John Smith", 'Employee ID': 10101, 'Test ID': 103, 'Test Result': .90 }]
        },
        {
            // Copy Collections to Main Context
            collection1: collection1,
            collection2: collection2,
            collection3: collection3,
            // Unique List of Employee IDs
            allEmployeeIDs: Distinct(Ungroup(Table({t:collection1}, {t:collection2}, {t:collection3}), t), 'Employee ID')
        }
    ),
    // Create Collection
    ClearCollect(
        colCombined,
        ForAll(
            allEmployeeIDs As ThisEmployee,
            With(
                // Load Each Collection Record
                {
                    col1: LookUp(collection1, 'Employee ID' = ThisEmployee.Value),
                    col2: LookUp(collection2, 'Employee ID' = ThisEmployee.Value),
                    col3: LookUp(collection3, 'Employee ID' = ThisEmployee.Value)
                },
                {
                    // Coalesce Names In Case Employees Aren't In All Collections
                    'Employee Name': Coalesce(col1.'Employee Name', col2.'Employee Name', col3.'Employee Name'),
                    'Employee ID': ThisEmployee.Value,
                    'Test ID (Collection 1)': col1.'Test ID',
                    'Test Result (Collection 1)': col1.'Test Result',
                    'Test ID (Collection 2)': col2.'Test ID',
                    'Test Result (Collection 2)': col2.'Test Result',
                    'Test ID (Collection 3)': col3.'Test ID',
                    'Test Result (Collection 3)': col3.'Test Result'
                }
            )
        )
    )
)
    This produces this collection:
    I made a slight modification to show that the Coalesce() works.. 
    // Main Context Object
With(
    // Assistant Context
    With(
        {
            // Using Your Examples
            collection1: [{ 'Employee Name': "John Smith", 'Employee ID': 10101, 'Test ID': 101, 'Test Result': 1 }],
            collection2: [
                { 'Employee Name': "John Smith", 'Employee ID': 10101, 'Test ID': 102, 'Test Result': .75 },
                { 'Employee Name': "Jennifer Aniston", 'Employee ID': 11011, 'Test ID': 202, 'Test Result': 1 }
            ],
            collection3: [
                { 'Employee Name': "John Smith", 'Employee ID': 10101, 'Test ID': 103, 'Test Result': .90 },
                { 'Employee Name': "Jennifer Aniston", 'Employee ID': 11011, 'Test ID': 203, 'Test Result': 1 }
            ]
        },
        {
            // Copy Collections to Main Context
            collection1: collection1,
            collection2: collection2,
            collection3: collection3,
            // Unique List of Employee IDs
            allEmployeeIDs: Distinct(Ungroup(Table({t:collection1}, {t:collection2}, {t:collection3}), t), 'Employee ID')
        }
    ),
    // Create Collection
    ClearCollect(
        colCombined,
        ForAll(
            allEmployeeIDs As ThisEmployee,
            With(
                // Load Each Collection Record
                {
                    col1: LookUp(collection1, 'Employee ID' = ThisEmployee.Value),
                    col2: LookUp(collection2, 'Employee ID' = ThisEmployee.Value),
                    col3: LookUp(collection3, 'Employee ID' = ThisEmployee.Value)
                },
                {
                    // Coalesce Names In Case Employees Aren't In All Collections
                    'Employee Name': Coalesce(col1.'Employee Name', col2.'Employee Name', col3.'Employee Name'),
                    'Employee ID': ThisEmployee.Value,
                    'Test ID (Collection 1)': col1.'Test ID',
                    'Test Result (Collection 1)': col1.'Test Result',
                    'Test ID (Collection 2)': col2.'Test ID',
                    'Test Result (Collection 2)': col2.'Test Result',
                    'Test ID (Collection 3)': col3.'Test ID',
                    'Test Result (Collection 3)': col3.'Test Result'
                }
            )
        )
    )
)
     
    That produces this:
  • Chriddle Profile Picture
    Chriddle 8,708 Super User 2026 Season 1 on at 
    ClearCollect(
    Collection1,
    { Name: "Jim", Id: 100, Result: 100 },
    { Name: "John", Id: 101, Result: 75 }
);
ClearCollect(
    Collection2,
    { Name: "Jim", Id: 100, Result: 100 },
    { Name: "John", Id: 101, Result: 100 }
);
ClearCollect(
    Collection3,
    { Name: "Jim", Id: 100, Result: 50 },
    { Name: "John", Id: 101, Result: 75 },
    { Name: "Jane", Id: 102, Result: 100 }
);
ClearCollect(
    CollectionTotal,
    ForAll(
        Distinct(
            ClearCollect(
                CollectionIds,
                Collection1, Collection2, Collection3
            ),  Id
        ) As Ids,
        {
            Id: Ids.Value,
            Name: LookUp( CollectionIds, Id = Ids.Value, Name  ),
            Result1: LookUp( Collection1, Id = Ids.Value,  Result ),
            Result2: LookUp( Collection2, Id = Ids.Value, Result ),
            Result3: LookUp( Collection3, Id = Ids.Value, Result )
        }
    )
)
  • WarrenBelz Profile Picture
    WarrenBelz 155,838 Most Valuable Professional on at
    An option if you want to hard-code the actual Test ID - main advantage of the structure is the ability to not trigger it on anything (no collections or variables required), but rather use directly in the Items of the Gallery 
    ShowColumns(
   AddColumns(
      GroupBy(
         Table(
            Collection1,
            Collection2,
            Collection3
         ),
         'Employee ID',
         Grouped
      ),
      Name,
      First(Grouped).'Employee Name',
      '101 Result',
      LookUp(
         Grouped,
         'Test ID' = 101
      ).'Test Result',
      '102 Result',
      LookUp(
         Grouped,
         'Test ID' = 102
      ).'Test Result',
      '103 Result',
      LookUp(
         Grouped,
         'Test ID' = 103
      ).'Test Result'
   ),
   'Employee ID',
   Name,
   '101 Result',
   '102 Result',
   '103 Result'
)
    If you have more tests, hard-code them as well and you would simply get a blank result in the column if they do not exist in the data. 
     
     
    Please ✅ Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider answering Yes to Was this reply helpful? or give it a Like ♥
    Visit my blog     Practical Power Apps    LinkedIn  
     
  • Suggested answer
    Assisted by AI
    VASANTH KUMAR BALMADI Profile Picture
    VASANTH KUMAR BALMADI 322 on at

    You can’t achieve this directly with GroupBy because your data is split across three separate collections. The easiest way is to first merge them by employee and then pivot the results using AddColumns + LookUp.

    Here’s a clean and working approach that I’ve used in similar scenarios.

    Assume your collections are:

    • colTest1

    • colTest2

    • colTest3

    (all having: Employee Name, Employee ID, Test ID, Test Results)

    Step 1: Create a base employee collection

    ClearCollect(
    colEmployees,
    Distinct(colTest1, 'Employee Name')
);

    Step 2: Build the final grouped collection

    ClearCollect(
    colFinalResults,
    AddColumns(
        colEmployees,
        "Employee ID",
            LookUp(colTest1, 'Employee Name' = Result).'Employee ID',

        "Test ID (Collection 1)",
            LookUp(colTest1, 'Employee Name' = Result).'Test ID',
        "Test Result (Collection 1)",
            LookUp(colTest1, 'Employee Name' = Result).'Test Results',

        "Test ID (Collection 2)",
            LookUp(colTest2, 'Employee Name' = Result).'Test ID',
        "Test Result (Collection 2)",
            LookUp(colTest2, 'Employee Name' = Result).'Test Results',

        "Test ID (Collection 3)",
            LookUp(colTest3, 'Employee Name' = Result).'Test ID',
        "Test Result (Collection 3)",
            LookUp(colTest3, 'Employee Name' = Result).'Test Results'
    )
);

    Result

    You’ll get one row per employee:

    Employee Name Employee ID Test ID (C1) Result (C1) Test ID (C2) Result (C2) Test ID (C3) Result (C3)
    John Smith 10101 101 100% 102 75% 103 90%

    Why GroupBy doesn’t work here

    GroupBy() works within a single collection only.
    Since your test data is spread across multiple collections, Power Apps has nothing common to group until you manually align the rows by employee.

    That’s why LookUp() is required to pull matching records from each collection.

    Optional (better performance)

    If you have large datasets, use Employee ID instead of name in the LookUp condition:

    LookUp(colTest1, 'Employee ID' = Result.'Employee ID')

    This pattern is essentially a manual pivot in Power Apps and is the recommended approach when combining multiple collections into a single employee-level result set.

  • BCBuizer Profile Picture
    BCBuizer 22,833 Super User 2026 Season 1 on at
     
    I think your reply might be an AI-assisted post, but I do not see any tools or sources cited. Can you update the post to include your sources, or confirm this was not produced using AI?
     
    Please refer to the Use AI responsibly section of Writing effective responses in the community:
     
    Thank you!
  • WarrenBelz Profile Picture
    WarrenBelz 155,838 Most Valuable Professional on at
    A quick follow-up to see if you received the answer you were looking for. Happy to assist further if not.
     
    Please ✅ Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider answering Yes to Was this reply helpful? or give it a Like ♥
    Visit my blog     Practical Power Apps    LinkedIn   

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Season of Sharing Community Challenge Launch!

Jump in, show your community spirit, and win prizes!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the May Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
Valantis Profile Picture

Valantis 424

#2
WarrenBelz Profile Picture

WarrenBelz 355 Most Valuable Professional

#3
11manish Profile Picture

11manish 290

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans