Group and count items in a SharePoint List

Like (0) Share

Report

Posted on 11 Feb 2025 21:53:33 by Guero

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

All responses (2)

Answers (0)

Chriddle 7,738 Super User 2025 Season 1 on 12 Feb 2025 at 16:05:01

Like (0)

Report
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 784 Super User 2025 Season 1 on 11 Feb 2025 at 22:37:29

Like (0)

Report

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.

Community site session details

Group and count items in a SharePoint List

Helpful resources

News and Announcements

Quick Links

Announcing the Engage with the Community forum!

🌸 Community Spring Festival 2025 Challenge Winners! 🌸

Warren Belz – Community Spotlight

Leaderboard > Power Automate - Building Flows

Featured topics

Product updates

Community site session details

Group and count items in a SharePoint List

Helpful resources

News and Announcements

Quick Links

Announcing the Engage with the Community forum!

🌸 Community Spring Festival 2025 Challenge Winners! 🌸

Warren Belz – Community Spotlight

Subscribe to this forum!

Select categories

Leaderboard > Power Automate - Building Flows

Featured topics

Product updates