Unanswered

Group an array with summing up one of the parameters

(0) Share

Report

Posted on by ryjan

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:

Building flows

I have the same question (0)

All responses (3)

Answers (1)

Chriddle 8,436 Super User 2025 Season 2 on at

Like (1)

Report
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

Was this reply helpful? Yes No
Verified answer

Jelle de Haas 42 on at

Like (0)

Report
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.

Was this reply helpful? Yes No
ryjan 45 on at

Like (0)

Report

Thanks a lot. I have used both approaches.

Was this reply helpful? Yes No