SUM by GROUP from JSON in a flow

(0) Share

Report

Posted on by Harish1978IND

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

Categories:

Using flows

I have the same question (0)

All responses (2)

Answers (1)

Verified answer

Chriddle 8,515 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
With a little help from xPath

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 } ]

Was this reply helpful? Yes No
Harish1978IND 4 on at

Like (0)

Report
Copy link

Link copied!

Awesome. Thanks a Lot 🙂

Was this reply helpful? Yes No