How to group and calculate sum of Power Automate array items

[Music] hello let's say you're building a workflow using powerautomate and inside this workflow you have a list of items that you get from any data source sharepoint dataverse rest api custom connect whatever doesn't matter now you have an array of records that you want to do some calculations on it something like data summary for example grouping creating total or anything like that and power automate by itself doesn't provide anyone of that of course there are third-party components that you can buy them separately they are usually very cheap and they will do the job for you but let's say in your company they are in rush until they get approval for that extra license and all those let's say paperwork takes time and you are in rush for delivering your project and now you want to do something really quick so this is what you can do inside power to meet to get this done we have a list of items and i want to group them by username and i want to calculate the total of the minutes in our duration field for every single user this is not the ideal solution i'd rather do it in a much better way when you have large number of records power automate is not made for this keep all these things in mind in case you want to implement it in a real project so let's get into it and enjoy the show let's start with our setup and see what we have for this video i created a flow with a manual trigger so you push a button so everything will work i created two variables one of them is user duration list the other one is user duration summary this is our input whatever we do should go into this variable they are both arrays and finally after we are done i put the content of this variable inside compose so we will examine and see what we got as a final result let's take a look at the project this is a flow that i created i called it grouping records in powerautomate as i mentioned we have a trigger no parameters nothing inside the first variable i call the variable user duration list the type is array and the content that i put inside it is something like this it's just a string that represents a json array with square brackets the user user a user b user a user c so it's a mix of user a b and c and the duration in minutes if you have like 200 100 130 and that's it the other array that we have is empty there is nothing in it and the result of our work should populate and put the content inside it our goal is to produce something like this in the user duration summary out of this content that we have in user duration list but how are we going to do that with just basic actions that we have inside power automate all right before we really get into this video i want to mention that this video is packed with formulas expressions data operations that they are typical in power automate if you are not familiar with this this video may make your head spin so if you feel like that there are two courses that i have on udemy the first one is json crash course i will put the link in the description that's a free course which is basically the core of working with expressions in powerapps and power automate the other course is master power automate expressions in two hours i also put the link in the description away the free course coupon that i put it somewhere on the screen and it is valid only for five days after this course so if you are lucky one enjoy it if you are not still there is a course discount voucher in the course description check it if you are interested now let's get into work there are four steps that we need to take first we need to get the distinct list of users i create an array of strings which includes user a b and c or whatever else the user that you can find in the initial list then i want to go through every single user and filter the user duration list the initial list that we had based on this user so that i can calculate the sum of duration based on that then this list goes to the next step and i need to calculate the total that i can simply use any loop or apply to each action to take care of this after every record is calculated per user i will add it to the user duration summary because it's an array i can simply use append to array variable action and this will do the trick for us now let's get to work we start with the first one which is get a distinct list of users to get a distinct list of users we go back to power automate and i need to navigate through all the items that are coming from this array which is user duration list and i want to feed it into an apply to each so i would say apply to each let's see if we can find it there we go it's looking for an input the only input that we have at the moment is this one user duration list so i click on this guy and user duration list is here right i really don't like this ui let me just reduce the size a little bit probably we will see better all right and this applied to each let me just rename it properly and i want to call it get distinct users distinct and obviously when i create a distinct list of users i need to put it in a variable but i cannot create variable inside apply to each i initialize another list here i say add an action any visualize variable there we go the name is going to be this thing user list and i also get a copy of it and i rename the action so i can easily find track and maintain it this one again should be an array i don't put anything inside it as i pick the users inside this loop i will add the users one by one to that one every user that this guy reads i need to check if it is in this array or not obviously the first user that i pick will not be found here because this list is empty but after that it will check regardless we check from the very beginning i need to use a condition i picked a condition here and i want to say if that distinct user list does not contain the current user that is being processed add it to the list otherwise skip this is how we do it i click on this guy and i pick the variable distinct user list and instead of equal i say contains and now i want to get the user of the current item here is a little bit tricky when i click on this guy i can pick the current item here right but this guy is not going to help me i want it to look like a formula see if i click on this guy it brings it here which is not going to be helpful for me because this one is a record like this which i don't want all of it i want whatever the whole record is dot user so instead of current i want the formula of this one that most of the time people cannot recall the formula here's the trick to work with it i need to have the formula shape of it in an expression so i click on the expression just like before i go here i put a space here so whatever that i add here now it looks like a formula i click on dynamic content and then i scroll down to the very end and i do not find anything why well because power automate thinks that it's smarter than us big mistake but we know how to go around it the simple trick is just ignore the sky for a second assume that it's not there just collapse this condition i get the current item and put it inside a compose because we cannot use a variable inside it so i just add another action here i would say compose and a bingo but i want this compose before this condition just drag and drop the condition and put it after compose now this compose can contain the current item so i just rename it to current record of initial list all right i bring it here now i can easily add this current item to this one inside this compose just make sure there is no space or anything before or after that we are good now if i go to the condition i can say when distinct user variable contains this compost that i can easily refer to under expressions i click on the expression i push one tiny space here then i go back to dynamic content and now if i scroll down you will find the output from the current record of initial list i click on it don't forget to remove this space from the beginning we just use that to trick the designer now after i have that i can say dot i'm looking for user user of course the intellisense does not find it so don't worry about it just type trust your typing and click ok so now what happens is that this condition returns true if this user list contains the current user that we are trying to look for if not we want to add it so yes we just ignore it if no add an action and i want to add it to the list of distinct user distinct user is an array and now i can add a string to an array and i look for append to an array variable remember we were building an array of strings i click on this guy and the array that we want to add this user to is going to be distinct user list and the value that we want to add to it is going to be exactly the same thing that we added here so i click on this guy i copy the expression i come back here do not paste it here directly when the value is selected click on the expression because what we have is expression not something visually understandable so i just paste the expression here and i click on ok now after this job is done after this loop is complete it should have a distinct list of users in a variable called distinct user list let's examine and see what's going on here this composes we don't need it at the moment i just use it to put my distinct user list and see if i got it right so let me just save it and i test it manually test run flow done okay it's running you see how long it takes to complete that's why i don't like this approach anyway i click on compose and now i have user a user b user c step one complete now for each user i need to filter the user duration list so i have the username i should use a filter action to get the initial list and filter it by the user that i have a distinct list of them one by one so i go back here i click on edit i don't need this compose at the moment but after i get the distinct user i want to use another apply to each and i want to say apply to each i add it here and this one is going to be rename call chelate total every item that comes here now it's coming from the distinct user because we need to do the calculations for every single user one by one you want to make your life a whole lot easier when you're working with expression always keep it as an advice every time that you get into a loop if you want to work with the current item put it in the compose action and after that writing expressions is whole lot easier so i come back here again and calculate total i want to add another action right in the beginning i add compose and i add this guy here this compose let me just rename it it's going to contain current distinct user and the content inside it just like before i get it as the current item from the calculator which is this loop that we're working with so i have the current user i have the users one by one at the moment i really don't need to filter anything or write any other weird expression because this guy every item represents one user as a string which is great now i come back here i want to filter this guy user duration list by the user that i have filter yes inside power automate we have filter scroll down filter array and this time i want to rename it to filter list by user okay the array that i want to filter is going to be user duration list the big list that we had in the beginning the filter condition that i have i want to make sure that the current user this is the current distinct user equals to the items inside this list that the user is for example user a or whatever that that the current item is in that applied to each so i come back here and i write another expression here i use item which refers to current item in the filter expression and i say dot user because i want to compare the username that i got from that distinct user list to the user of every single item and if it is match filter should show it so if i click on ok we are good let's save and test it quickly just to make sure we are on the right track all right let's test it test run flow done and it's gonna work now see what we got let me just expand it and filter list by user for the first iteration the current user is user a and this filter returns user a user a user a everything is user a while the input includes all the users great so we completed this step and we have done it for all the users look at that if i go to next all of a sudden i have everything for user b and if i go to the next step i have everything for user c we're getting closer now we need to calculate the total duration for every user which is easy when i have a list of one single user that includes all the durations i need to iterate through everything and add them to one variable a variable sure because it's variable i gotta add it somewhere in the beginning let me just click on edit but this is a temporary variable so i use any shelies variable and this initialize variable the variable name is going to be temporary sum the type is going to be integer and the initial value of this it should be 0 but it doesn't really matter because we have to reset it every time that the loop goes for a new user so let me just scroll down and in the calculate total right after i get the user i want to set variable and the variable that i want to set is going to be temporary sum and i want to set it to zero all right i'm not sure if i renamed it yeah i didn't so temporary sum let me just rename this guy and just add this guy here great so let me just save it again before we lose the work that we have and we go down so when i have the filtered list of the items by user for every single user the job is very easy i just need to add another apply to each right and this applies to each let me just rename it to calculate per user total and the value that i want to get from this guy is the output from this filter because now this loop should go through every single item for the current user that we filter the initial list based on right so so the filter that i have is called filter list by user i click on this guy i go filter list by user and i pick the body which is an array just like before i add a compose here compose i put the current item inside it because i want to work with the formula so rename it i call it per user current item and i want to put the calculate per user total current item inside it i scroll down here scroll down calculate per user total just make sure you pick the right one calculate per user total i get the current item here i need to add the duration of the current user to the temporary variable that we had that variable was an integer right so i have something called increment if i scroll down you will find it here increment variable i can say rename it to add duration to temporary total the name is going to be temporary sum and the value that i want to add is the current item which is per user current item dot duration again the same trick i click on variable i go to the expression i put one space here i go back to dynamic content scroll down and i will find this guy per user current item per user current item and i put the output here dot duration and don't forget to remove this space deuteration properly typed i click on ok and i click save so so far we calculated the duration sum but we need to create this record and add it to the user duration summary how do we do that just like this so calculate per user total is complete now right after that i use append because remember this guy is an array i can append one record to an array for every single user so i bring it back here append append to array variable i'll rename it and i can say add record to duration summary i pick my user duration summary and i create that record here just like this the user is going to be i put double quote in front of it the user is going to be the user that we are working with it inside this calculator which is going to be current distinct user so current distinct user gosh where is it current distinct user output right after that i would say duration sum and the value don't forget these double quotes i put the cursor right inside the double code and the duration sum is going to be my temporary variable which is going to be the temporary sum and just make sure i reset the variable in the beginning so every time we set it to zero fantastic and now all we need to do this compose i remember we put the distinct user list let me just remove it and put the final result inside it which is gonna be user duration summary just to double check if we achieve the correct result let me just save it and time to test moment of truth manually run flow done that's why i don't like this approach see we have like not even 10 records and it's going to take four seconds to do this four seconds or even more than that to calculate the totals eight seconds yeah come on and finally it is done let me click on compose and see what we got inside it so we have user a 950 user b 490 and user c 1110 we are done and now we have everything in a summary array and that was it if you enjoyed this video leave me a comment or push the like button and let me know that this kind of content is something that you enjoy i always welcome new ideas about the new videos if you are looking for something that you want me to cover leave it for me in the comment section and i will see you soon in the next video thank you for watching [Music] you

Channel: Alireza Aliabadi

Views: 13,805

Keywords: power automate calculate sum, pwoer automate calculate sum, power automate caluclate sum, pwoer automate caluclate sum, Power automate subtotal, Power automate tutorial, Power automate data grouping, Power automate sum, flow sum, flow subtotal, Power automate subtotal grouping, Power automate flow tutorial

Id: e8gKLBDHQHc

Length: 24min 54sec (1494 seconds)

Published: Mon Aug 15 2022