Ranking the results from a Get Items action

(0) Share

Report

Posted on by Charles-v-D

280

Hi all,

I have a Sharepoint list that holds info on references we have issued. The list looks like this:

Reference-ID Date-Issued Issued-To

001 Date x name

002 Date x name

001 Date x name

002 Date x name

004 Date x name

007 Date x name

005 Date x name

007 Date x name

001 Date x name

009 Date x name

I would like to report on the top-3 most issued references.

For the example above the result should be:

1. 001 (4x)

2. 007 (3x)

3. 002 (2x)

I have no idea if there is a predefine action or command for this.

Any suggestion would be greatly appreciated!

Thanks

Regards

charles

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (1)

Sort by

Chriddle 8,706 Super User 2026 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
Fast and easy with xPath:

Compose

Example data (I've used the same 12 data records as in your example):

[ { "Id": "001", "Date": "Date x", "To": "name" }, { "Id": "001", "Date": "Date x", "To": "name" }, { "Id": "002", "Date": "Date x", "To": "name" }, { "Id": "001", "Date": "Date x", "To": "name" }, { "Id": "002", "Date": "Date x", "To": "name" }, { "Id": "004", "Date": "Date x", "To": "name" }, { "Id": "007", "Date": "Date x", "To": "name" }, { "Id": "005", "Date": "Date x", "To": "name" }, { "Id": "007", "Date": "Date x", "To": "name" }, { "Id": "007", "Date": "Date x", "To": "name" }, { "Id": "001", "Date": "Date x", "To": "name" }, { "Id": "009", "Date": "Date x", "To": "name" } ]

Select

From

union( xpath( xml(json(concat('{"Root":{"Item":', outputs('Compose'),'}}'))), '//Id/text()' ), json('[]') )

Map Id

item()

Map Count

xpath( xml(json(concat('{"Root":{"Item":', outputs('Compose'),'}}'))), concat('count(//Item[Id="', item(),'"])') )

Compose 2

1. Id

reverse(sort(body('Select'), 'Count'))[0]['Id']

1. Count

reverse(sort(body('Select'), 'Count'))[0]['Count']

2. Id

reverse(sort(body('Select'), 'Count'))[1]['Id']

... etc

Was this reply helpful? Yes No
Suggested answer

Pstork1 69,547 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

There is no simple single action or function to do this. But it can be done fairly easily if you know a few tricks. This blog post walks through how to Group By certain fields and accumulate results. Group By and Sum in Power Automate / Flow

----------------------------------------------------------------------------------
If this Post helped you, please click "Does this answer your question" and give it a like to help others in the community find the answer too!

Paul Papanek Stork, MVP
Blog: https://www.dontpapanic.com/blog

Was this reply helpful? Yes No
Charles-v-D 280 on at

Like
a
(0)

Report
Copy link

Link copied!

@Chriddle Thank you for responding, I'm very much a beginner with Power automate... would you please be so kind how I can fill the COMPOSE step dynamically from a sharepoint list?

Thanks!

Charles

Was this reply helpful? Yes No
Verified answer

Chriddle 8,706 Super User 2026 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

how I can fill the COMPOSE step dynamically from a sharepoint list?

Just put outputs('Get_items')?['body/value'] into the Compose.

(In the Dynamic Content it's called "value List of items")

Alternatively, you can replace each occurrence of outputs('Compose') in my expressions with outputs('Get_items')?['body/value']

Note that in my selection expressions you need to replace both occurrences of the column name “Id” with your corresponding column name:

Was this reply helpful? Yes No
Charles-v-D 280 on at

Like
a
(0)

Report
Copy link

Link copied!

@Chriddle (& @Pstork1),

Thank you for your help on this!! It works like a charm!!

Regards

charles

Was this reply helpful? Yes No