Hello,
I have a collection which includes someone's name and a column with an item reference in there. It will include one row per item associated with someone's name, and there can be multiple records for the same person / item combination. The use-case is I'm attempting to log the user interactions (clicks) in parts of my app, and I want to use that to see the top x clicks for each user. I can do this easily outside of PowerApps. I think I can also do it in a gallery using labels and formulas. But what I really want is to do it within a collection so I can then make use of the calculated data within the app (e.g. show to the user the top x items they have clicked on based on the log).
A simplified example is this:
LogTable:
Person | Item |
Bob | A |
Bob | A |
Bob | B |
Bob | B |
Bob | A |
Bob | A |
Bob | B |
Bob | C |
Bob | C |
Bob | C |
Bob | A |
Bob | B |
Sarah | A |
Sarah | C |
Sarah | D |
Sarah | D |
Sarah | D |
Sarah | A |
Sarah | D |
Sarah | C |
Sarah | E |
Sarah | D |
Sarah | A |
Sarah | A |
And I want to turn this into a summarised table like this:
Person | Item | Frequency |
Bob | A | 5 |
Bob | B | 4 |
Bob | C | 3 |
Sarah | A | 4 |
Sarah | C | 2 |
Sarah | D | 5 |
Sarah | E | 1 |
Where 'frequency' is the count of the rows in the log table with that person / item combination.
I have gone round and round in circles trying groupby and some forall loops, but can't quite crack this. I can get the second table without the counts (i.e. the distinct person / item combinations from the log table) but can't work out how to then add the column of counts.
Any help would be really gratefully received. Many thanks
Thanks @desreed ,
The data column (or whatever you want to call it - I just use Data) is a table containing the records from all the other fields that match the grouping.
Thank you @WarrenBelz . That's sorted it.
I created a new collection with that code in. It left me with a 'data' column I didn't need, so added in a dropcolumns function to get rid of that, and it's sorted. Perfect! Thanks so much.
Hi @desreed ,
This should do it
AddColumns(
GroupBy(
LogTable,
"Person",
"Item",
"Data"
),
"Frequency",
CountRows(Data)
)
Please click Accept as solution 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 Thumbs Up.
WarrenBelz
42
Most Valuable Professional
mmbr1606
41
Super User 2025 Season 1
MS.Ragavendar
36