Skip to main content

Notifications

Power Automate - Building Flows
Suggested answer

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

(1) ShareShare
ReportReport
Posted on by 1,408
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.
  • Nived_Nambiar Profile Picture
    Nived_Nambiar 17,076 on at
    Can i GroupBy and Count an array of items returned from externla API
    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
  • johnjohnPter Profile Picture
    johnjohnPter 1,408 on at
    Can i GroupBy and Count an array of items returned from externla API
     
    Yes for each user.. i need to do greaterthanorEqual 6 and lessThan 8 .. also greaterthanorEqual 8 and lessThan 10 .. and so one
  • takolota1 Profile Picture
    takolota1 4,781 on at
    Can i GroupBy and Count an array of items returned from externla API
    This video on efficient Group By may be helpful: https://youtu.be/z5MxbwURV68?si=Fly9Jiy2fW8aG_zz
  • Nived_Nambiar Profile Picture
    Nived_Nambiar 17,076 on at
    Can i GroupBy and Count an array of items returned from externla API
    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 ?
     
     
  • johnjohnPter Profile Picture
    johnjohnPter 1,408 on at
    Can i GroupBy and Count an array of items returned from externla API
    Thanks for the reply, will try those steps and keep you posted
  • Suggested answer
    trice602 Profile Picture
    trice602 11,772 on at
    Can i GroupBy and Count an array of items returned from externla API
    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

     


     

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

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,508

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,839

Leaderboard