Skip to main content

Notifications

Announcements

Welcome to the Power Platform Communities
Check out the New Agent Creator Community
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps - Building Power Apps / Ungrouping a multichoi...
Power Apps - Building Power Apps
Answered

Ungrouping a multichoice column

(0) ShareShare
ReportReport
Posted on by heshan_pandit 115
HI there
 
I have a large Sharepoint list. It involves data of an event with a unique event ID. there are several colleagues that are involved with that event (up to 3).
The Sharepoint list ID data is a number. The colleagues involved (called 'Assessment-Panel') is a multi choice column.
 
For example
event 1, Dave, Linda, Jim
event 2, Linda, Jim, Tom
event 3, Tom, Dave, Will
event 4, Janet, Linda, Gill
 
As it is a Sharepoint multicolumn list, the individuals have little colours and it all looks quite pretty
 
I would like to calculate in Powerapps (text boxes and buttons), how many events each of the individuals are involved in
 
e.g. i would like to out put (using the above example)
Dave = 2
Linda = 3
Tom = 2
Jim = 2
Will = 1
Gill = 1
 
etc
As new people come onboard the list of individuals and their frequency of activity will get longer of course.

That way I am able to know how many each person is involved with so that any work can be distributed as evenly as possible.
 
Of course, the Sharepoint list has many other columns with additional data but for this task, I do not need to collect any additional information.  
 
I have found articles on grouping and ungrouping columns but that seems to work with text data and not the multichoice data format (?JSON or table data)
Categories:
Employee Ideas app on Teams
Using Apps Using Formulas
  • heshan_pandit Profile Picture
    heshan_pandit 115 on at
    Ungrouping a multichoice column
    Thanks
     
    The other method was to filter the gallery collection. I used your suggestion in the gallery but instead of using colAssessment in the items, I 'Filter'-ed it with a Not (IsBlank( using the colleague names (Value) and so the line with a blank colleague name was filtered out.
     
    Apologies but I simplified by question from the actual scenario due to confidentiality reasons for my use (medical). There are situations where no colleague name is used.
     
    However, your suggestion and my method both work and for that I am grateful to you for your help.
     
    Heshan
     
     
  • Verified answer
    WarrenBelz Profile Picture
    WarrenBelz 148,302 Most Valuable Professional on at
    Ungrouping a multichoice column
    If you want to sort with the biggest on top and exclude zero results. I do not understand how a colleague could be blank when you are using a multi-choice column ( you cannot have a blank choice)
    ClearCollect(
   colAssessment,
   DropColumns(
      Sort(
         Filter(
            AddColumns(
               GroupBy(
                  Ungroup(
                     Sort(
                        SPListName,
                        ID,
                        SortOrder.Descending
                     ),
                     'Assessment-Panel'
                  ),
                  Value,
                  Grouped
               ),
               Events,
               CountRows(Grouped)
            ),
            Events > 0
         ),
         Events,
         SortOrder.Descending
      ),
      Grouped
   )
)
     
    Please click 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 giving it a Like.
    MVP (Business Applications)    Visit my blog Practical Power Apps    Buy me a coffee
  • heshan_pandit Profile Picture
    heshan_pandit 115 on at
    Ungrouping a multichoice column
    hi there
     
    I have spent some time with this now and a small final issue remains.
     
    If we go back to my original example, I provided an example of 4 events with three colleagues per event.
     
    Lets say that there are 20 events now. Of which 4 are as above and the remaining 16 have no colleagues involved.
     
    Running the code gives me the correct numbers of events per colleague but the 'blank' colleague or absent colleague lines get collected too. A line with 16 events would appear with No Colleague.
     
    I have tried filtering out the Not IsBlank column of colleagues within the ungroup function but it doesn't seem to work.
     
    How would i remove lines with no colleague involvement?
     
    Heshan
  • Suggested answer
    WarrenBelz Profile Picture
    WarrenBelz 148,302 Most Valuable Professional on at
    Ungrouping a multichoice column
    Just sort the lot by Events descending  - also some comments on what is happening here
    ClearCollect(
   colAssessment,
   DropColumns(            
      Sort(               
         AddColumns(       
            GroupBy(       
               Ungroup(    
                  Sort(    
                     SPListName,
                     ID,
                     SortOrder.Descending   // 1 get the newest records
                  ),              
                  'Assessment-Panel'        // 2 One record for each multi-choice field value
               ),
               Value,                       // 3 Now group across all records for this field
               Grouped
            ),
            Events,
            CountRows(Grouped)              // 4 Add a count of each group
         ),
         Events,
         SortOrder.Descending               // 5 Sort with biggest total on top
      ),
      Grouped                               // 6 Get rid of the grouped field 
   )
)
     
  • heshan_pandit Profile Picture
    heshan_pandit 115 on at
    Ungrouping a multichoice column
    This worked, I have the emails of the colleagues and there numbers of events!! Thanks very much
     
    How would I sort the names of the colleagues in event frequency order. Clearly, I would need to know who has done the most and who has done the least. At the moment the event frequency numerical order is a bit random 
     
    PS I will be spending the next few days unpicking your suggestion to learn how Group and ungroup work (this is new to me)
     
    So in the meantime, one final ask.
     
    Thanks
    Heshan
  • Verified answer
    WarrenBelz Profile Picture
    WarrenBelz 148,302 Most Valuable Professional on at
    Ungrouping a multichoice column
    It would work directly as the Items of a Gallery, but you could also collect it
    ClearCollect(
   colAssessment,
   DropColumns(
      AddColumns(
         GroupBy(
            Ungroup(
               Sort(
                  SPListName,
                  ID,
                  SortOrder.Descending
               ),
               'Assessment-Panel'
            ),
            Value,
            Grouped
         ),
         Events,
         CountRows(Grouped)
      ),
      Grouped
   )
)
     
    Please click 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 giving it a Like.
    MVP (Business Applications)    Visit my blog Practical Power Apps    Buy me a coffee
  • heshan_pandit Profile Picture
    heshan_pandit 115 on at
    Ungrouping a multichoice column

    Thank you so much for your contribution
     
    So I have put your suggestion in to a button but am a little confused on where and how the data will present?
     
    Will it be a variable that I need to put in a text box? Or is it best in a new collection? What is the best output if this code were in a button. 
     
    Thanks
    heshan
  • WarrenBelz Profile Picture
    WarrenBelz 148,302 Most Valuable Professional on at
    Ungrouping a multichoice column
    Something like this should do it - note that the maximum number of records you can apply this to is restricted by your Data Row Limit, so you might consider the additional Sort below to get the newest record set.
    DropColumns(
   AddColumns(
      GroupBy(
         Ungroup(
                Sort(
               SPListName,
                   ID,
                   SortOrder.Descending
    ​​​​​​​            ),
            'Assessment-Panel'
         ),
         Value,
         Grouped
      ),
      Events,
      CountRows(Grouped)
   ),
   Grouped
)
     
    Please click 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 giving it a Like.
    MVP (Business Applications)    Visit my blog Practical Power Apps    Buy me a coffee
  • heshan_pandit Profile Picture
    heshan_pandit 115 on at
    Ungrouping a multichoice column
    ...ANy help would be appreciated. There was some information on somehow extracting each individual and concatenating the name (in this case) separated by commas and then manipulating the counting but I cannot find that anymore). Perhaps I am not using the correct search terms
     
    Many thanks
    Heshan

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
Check out the New Agent Creator Community

Quick Links

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the May Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 791 Most Valuable Professional

#2
MS.Ragavendar Profile Picture

MS.Ragavendar 410

#3
mmbr1606 Profile Picture

mmbr1606 275 Super User 2025 Season 1

Last month Overall leaderboard

Featured topics

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

Product updates

Microsoft Power Platform Community release plans