Can i GroupBy and Count an array of items returned from externla API

(1) Share

Report

Posted on by johnjohnPter

1,875 Season of Giving Solutions 2025

I have an external API which return this sample JSON data representing the calls each user do, i can get up to 16K items:-

[
{
"Name": "John Doe",
"DateTime": "2024-11-01T09:00:00"
},
{
"Name": "Jane Smith",
"DateTime": "2024-11-01T10:30:00"
},
{
"Name": "John Doe",
"DateTime": "2024-11-01T10:15:00"
},
{
"Name": "Emily Davis",
"DateTime": "2024-11-04T08:45:00"
},
{
"Name": "Michael Brown",
"DateTime": "2024-11-05T09:00:00"
}
]

Now i want to update an excel sheet with the those data:-

1) Count the number of calls for each user from 6am-8am

2) Count the number of calls for each user from 8am -10am

now i can all the items >> so how i can group the data by Name & Date/time then get the count? the excel sheet that i need to populate will have those main columns (highlighted in green) that need to be populated by the flow:-

Thanks in advance for any help.

Categories:

Building flows

I have the same question (0)

All responses (8)

Answers (0)

Sort by

Suggested answer

trice602 16,164 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

Hi,

You seem to have a good understanding of what you need here and if you are already working with JSON, this should be easy for you to implement.

So here I am using two key actions: Filter Array and a Compose action. In this example, I wanted to count the number of "John Doe" in my sample array you provided. The results are 2.

Here's the default view of filter array but I will also show you the basic mode which is typically where you enter your filter.

In basic mode, on the left side you will enter the expression:

item()?['Name']

equal to

John Doe

It appears like the above screen and for some reason edit in Basic mode is grayed out (bug) after running.

But the filter query is pretty easy to understand.

Next is the compose. Here it is a quick length expression to get the count.

So those are the fundamentals to get the counts for each item.

Now to dynamically run this through a large array and automatically create the filter query to check the names dynamically, first you would create a select to get the names from the array.

Then you would initialize a new array Appended_Names. Then you would use the union expression to remove duplicates so you end up with a list of names that you can loop through, get the count as needed, the use Add a row (Excel) and post your new data.

------------------------------------------------

⭐ If this was helpful, please like and/or mark as a verified answer to help others find this too!

Always glad to help! 💯💯💯💯💯

Tom

Follow me on LinkedIn - Thomas Rice, PMP | LinkedIn

Was this reply helpful? Yes No
johnjohnPter 1,875 Season of Giving Solutions 2025 on at

Like (0)

Report
Copy link

Link copied!

@trice602

Thanks for the reply, will try those steps and keep you posted

Was this reply helpful? Yes No
Nived_Nambiar 18,138 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi john,

one question - regarding the time based filter - you need to filter for each user within timeframe - 6-8am and 8-10am, so in which timerange does 8am would be inclusive ?

Was this reply helpful? Yes No
takolota1 4,980 Moderator on at

Like (0)

Report
Copy link

Link copied!

This video on efficient Group By may be helpful: https://youtu.be/z5MxbwURV68?si=Fly9Jiy2fW8aG_zz

Was this reply helpful? Yes No
johnjohnPter 1,875 Season of Giving Solutions 2025 on at

Like (0)

Report
Copy link

Link copied!

@Nived_Nambiar

Yes for each user.. i need to do greaterthanorEqual 6 and lessThan 8 .. also greaterthanorEqual 8 and lessThan 10 .. and so one

Was this reply helpful? Yes No
Nived_Nambiar 18,138 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi,

Adding some extra points to what @trice602 has answered.

Once you have got the list of names to iterate through, use apply to each to iterate through each name , filter the main array details with name and time range - get the count of each filter action and add it as new row to excel file.

like below

First filter array filter the input array based on name and whether time range b/w 6-8 am like below

Expression :

and(equals(item()?['Name'], items('Apply_to_each')), and(greater(formatDateTime(item()?['DateTime'], 'HH:mm:ss'), '06:00:00'), lessOrEquals(formatDateTime(item()?['DateTime'], 'HH:mm:ss'), '08:00:00')))

Next use another filter array to filter based on name and whether date range b/w 8-10am

and(equals(item()?['Name'],items('Apply_to_each')),and(greater(formatDateTime(item()?['DateTime'],'HH:mm:ss'),'08:00:00'),lessOrEquals(formatDateTime(item()?['DateTime'],'HH:mm:ss'),'10:00:00')))

Now use compose action to get the count of filter array like below - this is optional as for you this should be add row to excel table

Note that inputData is input json array value provided

Hope it helps !

Thanks & Regards,

Nived N

Was this reply helpful? Yes No
trice602 16,164 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hey there!

I am following up on this post can i groupby and count an array of items returned from externla api in this category building flows from 10/29/2024. I know the post is a little older, but it is a common question here so wanted to take a moment and follow-up. I see there have been 6 replies and 1 likes and asking if your question has been answered. If so, please help the community out by completing one or more of the following actions! Thanks in advance!

Easy as 1-2-3 ‼️💯⭐😎🆒🙏

1) Mark a reply as a verified answer - this is essential, and it helps others with the same general question find this thread. ✅

2) You can also mark one of more replies as a verified answer. Please take a moment and give the author credit for volunteering their time. ✌️

3) Like one or more replies. Kudos always greatly appreciated! 👍

Pro tip: if you found your own solution or workaround, feel free to add a reply to your own post and mark it as the solution too! The goal is to get this thread marked with a verified answer by anyone that finds one or more replies helpful!

Again, thanks in advance for reading and responding, always glad to help! Tom 💯💯💯💯💯

Was this reply helpful? Yes No
trice602 16,164 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

If I can help with anything else please let me know or tag me in a future post, I enjoy reading and answering questions. Always glad to help! Tom 💯💯💯💯💯

Was this reply helpful? Yes No