cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frog64
Helper III
Helper III

Sum of multiple columns of an array

Hi,

 

I have an array called "Lines" that looks like this:

Frog64_0-1649861708995.png

 

I want to do the sum for each type.

Example: for A, 10+5+5=20. For B = 1, and for C = 7.

I tried but my loops are wrong.

Can anyone help me on this?

 

Thanks 🙂

4 REPLIES 4
Expiscornovus
Super User
Super User

Hi @Frog64,

 

You could use an increment variable action like in this thread suggested by @fchopohttps://powerusers.microsoft.com/t5/Building-Flows/Sum-an-array-column/m-p/794951

 

In your setup you first want to identify the unique Type values (A, B, C). When you have those in a collection you could loop through them and use a filter to find the values per type. With a nested apply to each you could increment the integers and calculate the sum in a temp variable.

 

Below is an example approach.

This first Select (the Select - Types) action uses the item()['Type'] expression in the Map field. I see I forgot to add that to the notes.

 

sumintegeritems.png

 

sumintegeritems02.png

With your test data it would look something like this:

testresult_sumintegers.png



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


Frog64
Helper III
Helper III

Hi @Expiscornovus  and thanks for your answer.

I am a beginner and I don't know the Select - Value. Moreover, I don't know what you put in the Increment variable for TmpInteger in the Value field.

I tried to do the following flow in order to store the result in an array so I can use it later in my flow, but my loop is still bad so my total amount is false:

Frog64_0-1649872886799.png

Frog64_1-1649872904748.png

 

Frog64_2-1649872919482.png

 

Frog64_3-1649872934600.png

 

Frog64
Helper III
Helper III

@Expiscornovus I tried to do the same flow as you but I have an error when I want to increment the variable.

Do you know how to handle this?

 

01.jpg

 

2.jpg

 

3.jpg

 

Expiscornovus
Super User
Super User

Hi @Frog64,

 

Let me clarify a couple of things by sharing all the steps from my earlier example.

 

1. Add a Manually Trigger a flow action

 

2. Add a Initialize variable action. Use SumText as the Name. Select Type String. Leave the Value empty

 

sumtext.png

 

3. Add a Initialize variable action. Use TempInteger as the Name. Select Type Integer. Leave the Value empty

 

tempinteger.png

 

4. Add a Initialize variable action. Use Lines as the Name. Select Type Array. I used this as the value

 

 

[
  {
    "Type": "A",
    "Value": 10
  },
  {
    "Type": "A",
    "Value": 5
  },
  {
    "Type": "B",
    "Value": 1
  },
  {
    "Type": "C",
    "Value": 2
  },
  {
    "Type": "C",
    "Value": 5
  },
  {
    "Type": "A",
    "Value": 5
  }
]

 

 

 

linesvariable.png

 

5. Add a Select Action. Rename the action from 'Select' to 'Select - Types'. Use the Lines variable in the From. Use the following expression in the Map field (first switch the Map field to text mode by click the button on the right side).

 

 

item()['Type']

 

 

 

selecttypes.png

 

6. Add Apply to each loop action. Rename the action from 'Apply to each' to 'Apply to each - Loop through unique type items'. In the Select an output from previous steps use the following expression:

 

 

intersection(body('Select_-_Types'),body('Select_-_Types'))

 

 

intersection_bodyselecttypes.png

 

7. Within the Apply to each - Loop through unique type items action add a Compose action. Rename the action from 'Compose' to 'Compose - Type Item'. Use the following expression for the inputs.

 

 

item()

 

 

composetypeitem.png

 

8. Within the Apply to each - Loop through unique type items action add a Filter Array action. Rename the action from 'Filter Array' to 'Filter array - Find Values for Type'. Use the Lines Variable in the From field. In the criteria use 

 

 

item()['Type'] is equal to @{outputs('Compose_-_Type_Item')}

 

 

item_type_filterarray.png

 

9. Within the Apply to each - Loop through unique type items action add a Select Action. Rename the action from 'Select' to 'Select - Value'. Use the Body of the Filter Array action in the From. Use the following expression in the Map field (first switch the Map field to text mode by click the button on the right side).

 

 

item()['Value']

 

 

select_value.png

 

10. Within the Apply to each - Loop through unique type items action add a second Apply to each loop action. Rename the action from 'Apply to each' to 'Apply to each - Loop through value items'. In the Select an output from previous steps use the Output of the Select - Value action.

 

secondapplytoeach.png

 

11. Within the Apply to each - Loop through value items action add an Increment Variable action. Select the TempInteger variable. In the value use the following expression:

 

 

item()

 

 

incrementvariable.png

 

12. Within the Apply to each - Loop through unique type items action add an append to string variable action. Select the SumText variable. Use the following expression 

 

 

Total Sum for Type @{outputs('Compose_-_Type_Item')} is @{variables('TempInteger')}@{decodeUriComponent('%0A')}

 

 

appendtostringvariable.png

 

13. Within the Apply to each - Loop through unique type items action add a set variable action. Select the TempInteger. (Re)Set the value to 0. 

 

setvariabletozero.png

 

14. Add a Compose action (outside the apply to each). Use the SumText variable for the Inputs.

 

showsumtextvariable.png



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (2,976)