Group and count items in a SharePoint List

(0) Share

Report

Posted on 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:

Building flows

I have the same question (0)

All responses (2)

Answers (0)

Suggested answer

abc 123 789 Moderator on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Chriddle 8,672 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
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"]') ) )

Was this reply helpful? Yes No