Ungrouping a multichoice column

(0) Share

Report

Posted on by heshan_pandit

109

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

All responses (8)

Answers (1)

WarrenBelz 143,867 on at

Like (0)

Report
Ungrouping a multichoice column

@heshan_pandit

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 109 on at

Like (0)

Report

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 143,867 on at

Like (0)

Report
Ungrouping a multichoice column

Hi @heshan_pandit

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 109 on at

Like (0)

Report

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 143,867 on at

Like (1)

Report
Ungrouping a multichoice column

@heshan_pandit

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 109 on at

Like (0)

Report

Ungrouping a multichoice column

@WarrenBelz

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 143,867 on at

Like (1)

Report
Ungrouping a multichoice column

Hi @heshan_pandit

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 109 on at

Like (0)

Report

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