Skip to main content

Notifications

Announcements

Welcome to the Power Platform Communities
Announcing Mentions and Quick Responses!
Give Back to the Community this Month
Join us in the Community for an AMA: December 12th Register Today
News and Announcements icon
Power Platform Community / Forums / Power Automate - Building Flows / Group an array with su...
Power Automate - Building Flows
Answered

Group an array with summing up one of the parameters

(0) ShareShare
ReportReport
Posted on by ryjan 34
Hi all, I am trying to group and sum up my array. It should sum up the amount (Anzahl) of items with equal ids (Artikelnummer), 
 
Here is input example:
 
[
  {
    "Artikelnummer": "18136",
    "Anzahl": 1
  },
  {
    "Artikelnummer": "18760",
    "Anzahl": 1
  },
  {
    "Artikelnummer": "12352",
    "Anzahl": 4
  },
  {
    "Artikelnummer": "12352",
    "Anzahl": 5
  }
]
 
 
and I expect to get this output:
 
[
  {
    "Artikelnummer": "18136",
    "Anzahl": 1
  },
  {
    "Artikelnummer": "18760",
    "Anzahl": 1
  },
  {
    "Artikelnummer": "12352",
    "Anzahl": 9
  }
]
 
The data comes from Dataverse as output of List rows action. I would prefer some solution in Dataverse (some kind of view for the table using GroupBy), unfortunately I have found any. 
Getting the output from Dataverse within List rows would be the prefferable solution but as far as I know, aggregation is not supported there. 
I have tried to use an approach with Apply to each and array variable, but faced two problems:
- the current item was not evaluated within the Condition / Apply to each
- this approach takes too long and the transformation should not take more than 1-2 seconds even if the array contains up to 100 items
Categories:
Business Process Flows
  • ryjan Profile Picture
    ryjan 34 on at
    Group an array with summing up one of the parameters
    Thanks a lot. I have used both approaches. 
  • Verified answer
    Jelle de Haas Profile Picture
    Jelle de Haas 9 on at
    Group an array with summing up one of the parameters
    You can perform aggregation directly in Dataverse using FetchXML queries, which improves performance and eliminates the need for complex processing in Power Automate.
     
    In your case you can use the following FetchXML query directly in your List Rows action. Simply replace the placeholder logical names with your actual table and column logical names:
    <fetch mapping="logical" aggregate="true">
  <entity name="yourTable_logicalName">
    <attribute name="column_Artikelnummer_logicalName" alias="column_Artikelnummer_logicalName" groupby="true" />
    <attribute name="column_Anzahl_logicalName" alias="column_Anzahl_logicalName" aggregate="sum" />
  </entity>
</fetch>
    Note: In this example, the name and alias attributes are set to the same value, which makes it easier to reference them as dynamic content in your flow. Alternatively, the alias can be different if you prefer to use a custom field name.
  • Chriddle Profile Picture
    Chriddle 7,056 on at
    Group an array with summing up one of the parameters
    For 100 items this takes less than a second:

     
    From:
    union(
	xpath(
		xml(json(concat('{"Root":{"Item":', outputs('Compose'), '}}'))),
		'//Item/Artikelnummer/text()'
	),
	json('[]')
)
    Map Artikelnummer:
    item()
    Map Anzahl:
    xpath(
	xml(json(concat('{"Root":{"Item":', outputs('Compose'), '}}'))),
	concat('sum(//Item[Artikelnummer=', item(), ']/Anzahl)')
)
     
    Result

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

Welcome to the Power Platform Communities
Announcing Mentions and Quick Responses!
Give Back to the Community this Month
Join us in the Community for an AMA: December 12th Register Today

Quick Links

November 2024 Newsletter…

November 2024 Community Newsletter…

Community Update Oct 28…

Power Platform Community Update…

Tuesday Tip #7 Community Profile Tips…

Welcome to a brand new series, Tuesday Tips…

more Community News

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 143,246

#2
RandyHayes Profile Picture

RandyHayes 76,308

#3
Pstork1 Profile Picture

Pstork1 63,884

Leaderboard

Featured topics

Start and wait for an approval action

Product updates

Microsoft Power Platform Community release plans