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

SharePoint List - group by, count and average

I have LIST 1 on which I need to do some summary statistic and put this in LIST 2, like shown below (only small sample to illustrate):

 

summary.png


Column COUNTRIES_ON_TIME in LIST 2 comes from counting values = 0 in column DELIVERY_DELAY_DAYS in LIST 1
Column COUNTRIES_DELAYED in LIST 2 comes from counting values <> 0 in column DELIVERY_DELAY_DAYS in LIST 1
Column AVERAGE_DELAY_DAYS in LIST 2 comes from taking average from values <> 0 in column DELIVERY_DELAY_DAYS in LIST 1

What do I need to use (Flow, Fromulas?) to achieve what is in LIST 2?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Hi @zwornik80,

 

You could initialize some variable to count the counties numbers and average delay time, then use the Apply to each + condition to increase it determined by their Rigon and Delay time, for example:

Annotation 2020-04-22 152435.png

 

Annotation 2020-04-22 152508.png

 

Annotation 2020-04-22 152616.png

 

Then, use these math functions to process the average days, then append these data into the "List 2" table.

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Super User III
Super User III

Hi!
So you need to consolidate continents from list 1 into list 2, right?
You can divide the task in two:
Subtask#1: build an array whose elements are the continents; and initialize variables for the counters you need. The array I am referring is:
["EUR","LATAM",...]

Subtask#2: assign the array as 'Apply to Each' input, now inside filter the items from List1 so you get only the ones related to current iteration, next increment counters, then create new item in List 2 and finally reset counters

Hooe this helps


Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Community Support
Community Support

Hi @zwornik80,

 

You could initialize some variable to count the counties numbers and average delay time, then use the Apply to each + condition to increase it determined by their Rigon and Delay time, for example:

Annotation 2020-04-22 152435.png

 

Annotation 2020-04-22 152508.png

 

Annotation 2020-04-22 152616.png

 

Then, use these math functions to process the average days, then append these data into the "List 2" table.

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

@v-litu-msft Thanks for suggestion. This is very usefull. Where in this flow I should refer to List 1 and List 2 to achieve what I need. Should I start with "Get Items" for List 1 and List 2, then fallow your steps with "Initialize variable" and "Apply to each"? How can use initialized variables to populate List 2?

Hi @zwornik80,

 

Initialize these variables before Apply to each action.

And you could use Add a row into a table to populate list 2.

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-litu-msft  You've mentioned to use Math Functions to calculate Average. I don't see proper function for calculating Average in Math section. "Compose" do not provide such option. I've tried by initializing variable for Average and wanted to update using "Set variable" with formula =AVERAGE([DELIVERY_DELAY_DAYS]) but that is not accepted. Can you suggest how to do this? 

Helpful resources

Announcements
PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Top Solution Authors
Users online (4,142)