web
You’re offline. This is a read only version of the page.
close
Skip to main content
News and Announcements icon
Community site session details

Community site session details

Session Id : bbOE14x7nh6RYFLLrtEYSK
Power Platform Community / Forums / Power Automate - Using Flows / SUM by GROUP from JSON...
Power Automate - Using Flows
Answered

SUM by GROUP from JSON in a flow

Like (0) ShareShare
ReportReport
Posted on 5 Sep 2023 15:25:52 by Harish1978IND 4

Hello,

 

I am new to Power automate and need help. I am getting JSOn output like below from a step

 

{
"ID": 1,
"GROUP1": "State1",
"GROUP2": "ABC",
"Population": 4
},
{
"ID": 2,
"GROUP1": "State1",
"GROUP2": "ABC",
"Population": 1
},
{
"ID": 3,
"GROUP1": "State1",
"GROUP2": "XYZ",
"Population": 0
},
{
"ID": 4,
"GROUP1": "State1",
"GROUP2": "ABC",
"Population": 3.5
},
{
"ID": 5,
"GROUP1": "State1",
"GROUP2": "ABC",
"Population": 8
},
{
"ID": 6,
"GROUP1": "State2",
"GROUP2": "PQR",
"Population": 17
},
{
"ID": 7,
"GROUP1": "State2",
"GROUP2": "PQR",
"Population": 24
},
{
"ID": 8,
"GROUP1": "State2",
"GROUP2": "PQR",
"Population": 24
},
{
"ID": 9,
"GROUP1": "State1",
"GROUP2": "LWD",
"Population": 7.5
},
{
"ID": 10,
"GROUP1": "State1",
"GROUP2": "LMQ",
"Population": 2
},
{
"ID": 11,
"GROUP1": "State1",
"GROUP2": "LMQ",
"Population": 2
}


and I want output in JSON like below so that i can save it in excel and send email.

 

Group1 - State1
GROUP2 - ABC
Population - Sum of Population by GROUP1 and GROUP2 i.e 16.5

 

Group1 - State1
GROUP2 - XYZ
Population - Sum of Population by GROUP1 and GROUP2 i.e 0

 

Group1 - State1
GROUP2 - LWD
Population - Sum of Population by GROUP1 and GROUP2 i.e 7.5

 

Group1 - State1
GROUP2 - LMQ
Population - Sum of Population by GROUP1 and GROUP2 i.e 4

 

  • Harish1978IND Profile Picture
    Harish1978IND 4 on 07 Sep 2023 at 13:47:39
    Re: SUM by GROUP from JSON in a flow

    Awesome. Thanks a Lot 🙂

  • Verified answer
    Chriddle Profile Picture
    Chriddle 7,932 Super User 2025 Season 2 on 07 Sep 2023 at 13:42:53
    Re: SUM by GROUP from JSON in a flow

    With a little help from xPath

     

    Chriddle_0-1694093602381.png

     

    With your data array in the Compose action:

    Spoiler (Highlight to read)
    [
{
"ID": 1,
"GROUP1": "State1",
"GROUP2": "ABC",
"Population": 4
},
{
"ID": 2,
"GROUP1": "State1",
"GROUP2": "ABC",
"Population": 1
},
{
"ID": 3,
"GROUP1": "State1",
"GROUP2": "XYZ",
"Population": 0
},
{
"ID": 4,
"GROUP1": "State1",
"GROUP2": "ABC",
"Population": 3.5
},
{
"ID": 5,
"GROUP1": "State1",
"GROUP2": "ABC",
"Population": 8
},
{
"ID": 6,
"GROUP1": "State2",
"GROUP2": "PQR",
"Population": 17
},
{
"ID": 7,
"GROUP1": "State2",
"GROUP2": "PQR",
"Population": 24
},
{
"ID": 8,
"GROUP1": "State2",
"GROUP2": "PQR",
"Population": 24
},
{
"ID": 9,
"GROUP1": "State1",
"GROUP2": "LWD",
"Population": 7.5
},
{
"ID": 10,
"GROUP1": "State1",
"GROUP2": "LMQ",
"Population": 2
},
{
"ID": 11,
"GROUP1": "State1",
"GROUP2": "LMQ",
"Population": 2
}
]
    [ { "ID": 1, "GROUP1": "State1", "GROUP2": "ABC", "Population": 4 }, { "ID": 2, "GROUP1": "State1", "GROUP2": "ABC", "Population": 1 }, { "ID": 3, "GROUP1": "State1", "GROUP2": "XYZ", "Population": 0 }, { "ID": 4, "GROUP1": "State1", "GROUP2": "ABC", "Population": 3.5 }, { "ID": 5, "GROUP1": "State1", "GROUP2": "ABC", "Population": 8 }, { "ID": 6, "GROUP1": "State2", "GROUP2": "PQR", "Population": 17 }, { "ID": 7, "GROUP1": "State2", "GROUP2": "PQR", "Population": 24 }, { "ID": 8, "GROUP1": "State2", "GROUP2": "PQR", "Population": 24 }, { "ID": 9, "GROUP1": "State1", "GROUP2": "LWD", "Population": 7.5 }, { "ID": 10, "GROUP1": "State1", "GROUP2": "LMQ", "Population": 2 }, { "ID": 11, "GROUP1": "State1", "GROUP2": "LMQ", "Population": 2 } ]

    Groups is a Select action to isolate only group information

    From: @{outputs('Compose')}

    Map Group1:  @item()['GROUP1']

    Map Group2: @item()['GROUP2']

     

    DistinctGroups is a Compose action to make Groups distinct:

    union(body('Groups'), json('[]'))

     

    Sum calculates the sums in a Select action

    From: @{outputs('DistinctGroups')}

    Map Group1: @item()['Group1']

    Map Group2: @item()['Group2']

    Map Population:

    xpath(
	xml(json(concat('{"root":{"item":', outputs('Compose'),'}}'))),
	concat('sum(//item[GROUP1="', item()['Group1'], '" and GROUP2="', item()['Group2'], '"]/Population)')
)

     

    Output:

    [
 {
 "Group1": "State1",
 "Group2": "ABC",
 "Population": 16.5
 },
 {
 "Group1": "State1",
 "Group2": "XYZ",
 "Population": 0
 },
 {
 "Group1": "State2",
 "Group2": "PQR",
 "Population": 65
 },
 {
 "Group1": "State1",
 "Group2": "LWD",
 "Population": 7.5
 },
 {
 "Group1": "State1",
 "Group2": "LMQ",
 "Population": 4
 }
]

     

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

News and Announcements

Now Available: 2025 Release Wave 2
Welcome to the Power Platform Communities
Announcing our 2025 Season 2 Super Users!

Quick Links

Announcing our 2025 Season 2 Super Users!

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

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 497 Super User 2025 Season 2

#2
David_MA Profile Picture

David_MA 436 Super User 2025 Season 2

#3
Riyaz_riz11 Profile Picture

Riyaz_riz11 244 Super User 2025 Season 2

Last month Overall leaderboard

Featured topics

Restore a deleted flow

Product updates

Microsoft Power Platform Community release plans