Skip to main content

Notifications

Announcements

Welcome to the Power Platform Communities - Lets Get Started!
May 14th | Power Platform Community AMA: Power Apps Edition
News and Announcements icon
Community site session details

Community site session details

Session Id : YJa9I3bX3uWedQs68fqaD5
Power Platform Community / Forums / Power Automate - Building Flows / Group and count items ...
Power Automate - Building Flows
Suggested answer

Group and count items in a SharePoint List

Like (0) ShareShare
ReportReport
Posted on 11 Feb 2025 21:53:33 by Guero 64
Hi there,
 
Essentially I have a SharePoint List set out like the below, which is automatically added to whenever the managers of each of my organisations branch add a document of a certain type to their respective SharePoint Document library
 
Branch/Department Document Type Date added
Branch 1 Document A 11/02/2025
Branch 1 Document B 03/02/2025
Branch 2 Document A 30/01/2025
Branch 3 Document A 31/01/2025
Branch 3 Document B 04/02/2025
Branch 4 Document A 06/02/2025
Branch 5 Document B 11/02/2025
Branch 6 Document B 01/02/2025
Branch 7 Document C 30/01/2025
 
 
I have been asked to take this list and work it into a report format to be sent to directors in an automated email every month, wherein instead of the raw data above it would just be grouped so that you can see how many of Documents A, B, and C have been uploaded by each branch over the last 4 weeks, like so. 
 
 
Branch/Department Document Type A Document Type B Document Type C
Branch 1 1 1 0
Branch 2 1 0 0
Branch 3 1 0 0
Branch 4 1 0 0
Branch 5 0 1 0
Branch 6 0 1 0
Branch 7 0 0 1
 
 
I've made a start using the Get Items action and have pointed it to the relevant SharePoint list, but I'm not particularly sure where to go from here or how I'd go about creating the above table and implementing the counts of each document per branch, so would appreciate any guidance!
 
 
 
 
Thank you!
 
 
Categories:
Business Process Flows
  • Chriddle Profile Picture
    Chriddle 7,708 Super User 2025 Season 1 on 12 Feb 2025 at 16:05:01
    Group and count items in a SharePoint List
    If you have a limited and known set of document types, you can do the following.
    (If you don't know the document types in advance, you will need at least one loop over the projects)
     
    Select
    From:
    union(
	xpath(
		xml(json(concat('{"Root":{"Item":', outputs('Compose'), '}}'))),
		'//Item/Branch/text()'
	),
	json('[]')
)
    Map Branch:
    item()
    Map Document A:
    length(
	xpath(
		xml(json(concat('{"Root":{"Item":', outputs('Compose'), '}}'))),
		concat('//Item[Branch="', item(), '" and Doc="Document A"]')
	)
)
    Map Document B:
    length(
	xpath(
		xml(json(concat('{"Root":{"Item":', outputs('Compose'), '}}'))),
		concat('//Item[Branch="', item(), '" and Doc="Document B"]')
	)
)
    Map Document C:
    length(
	xpath(
		xml(json(concat('{"Root":{"Item":', outputs('Compose'), '}}'))),
		concat('//Item[Branch="', item(), '" and Doc="Document C"]')
	)
)
     
  • Suggested answer
    abc 123 Profile Picture
    abc 123 784 Super User 2025 Season 1 on 11 Feb 2025 at 22:37:29
    Group and count items in a SharePoint List
    The desired format is called a "Crosstab". Young people probably call it a "Pivot Table".
     
    This is a specialty of Excel, not of SharePoint. If you can have a template Excel file with the setup inside, where all you need to do is pump in the data, then email the file, then you're solid. 
     
    If you're limited to SharePoint, then you'll need to maintain the additional structure, but that will be painful if the labels are dynamic. 
     
    Finally, if you really want to look like a pro, then consider using Power BI, which is Excel on Steroids.
     
     

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 - Lets Get Started!
May 14th | Power Platform Community AMA: Power Apps Edition

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 Automate - Building Flows

#1
stampcoin Profile Picture

stampcoin 87

#2
Michael E. Gernaey Profile Picture

Michael E. Gernaey 70 Super User 2025 Season 1

#3
David_MA Profile Picture

David_MA 48 Super User 2025 Season 1

Overall leaderboard

Featured topics

Start and wait for an approval action

Product updates

Microsoft Power Platform Community release plans