Skip to main content

Notifications

Community site session details

Community site session details

Session Id : JANmuZR2jqlRVzUwMjSEVO
Power Apps - Building Power Apps
Answered

Help with summarising a collection with counts

Like (0) ShareShare
ReportReport
Posted on 27 May 2021 09:05:23 by 26

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:

PersonItem
BobA
BobA
BobB
BobB
BobA
BobA
BobB
BobC
BobC
BobC
BobA
BobB
SarahA
SarahC
SarahD
SarahD
SarahD
SarahA
SarahD
SarahC
SarahE
SarahD
SarahA
SarahA

 

And I want to turn this into a summarised table like this:

PersonItemFrequency
BobA5
BobB4
BobC3
SarahA4
SarahC2
SarahD5
SarahE1

 

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

  • WarrenBelz Profile Picture
    146,881 Most Valuable Professional on 27 May 2021 at 10:02:30
    Re: Help with summarising a collection with counts

    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.

  • desreed Profile Picture
    26 on 27 May 2021 at 09:58:25
    Re: Help with summarising a collection with counts

    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.

  • Verified answer
    WarrenBelz Profile Picture
    146,881 Most Valuable Professional on 27 May 2021 at 09:51:28
    Re: Help with summarising a collection with counts

    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.

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

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Power Apps - Building Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 42 Most Valuable Professional

#2
mmbr1606 Profile Picture

mmbr1606 41 Super User 2025 Season 1

#3
MS.Ragavendar Profile Picture

MS.Ragavendar 36

Overall leaderboard
Loading started