You’re offline. This is a read only version of the page.
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
Awesome. Thanks a Lot 🙂
With a little help from xPath
With your data array in the Compose action:
Share
Report
All responses (
Answers (
[
{
"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
}
]
#1
WarrenBelz
146,658
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
65,999
Most Valuable Professional
