hi there folks my name is Damen bird and in today's demonstration I'm going to show you how you can count and sum in power automate super efficiently no apply to each and you'll come away with some amazing ideas for super efficient flows I promise you all of that so if you haven't already make sure you like And subscribe and without further Ado let's jump into the demonstration so I'm going to kick this demo off from a SharePoint list we can see on screen I have an employee expenses list we have multiple expenses from different employees there are 50 different expenses and what I'd like to do is via power automate get all of those items to sum up the total of all those expenses and we can see at the bottom we already have a sum in this list of 11,590 so that's what we'll compare against and then I'd like to group by the distinct employees that are in this list and also sum up the total of their expenses as well as getting a count for the number of expenses each employee has now I also have a new View group by summon count and we can see an example here we have employee number one John Smith he's got three expenses and um I haven't got a total for that on screen actually but it amounts to $750 the $500 250 so we're going to do this in power automate now the reason I showcase these ideas in my videos is I like to show you some efficient techniques within power automate that will try and keep you within the limits of your life license now I'm just bringing up this document here about the license user request limits if you are a standard user with an Enterprise agreement an E3 E5 what's called a seeded license then you actually get 6,000 API calls in a 24-hour period now if you start building lots and lots of flows and they're really really inefficient you're going to hit these limits and your flows will get throttled and they'll start failing you'll get 429 errors and you'll be thinking what's going on here power ultimate is rubbish well actually it's because you're building in efficient flows and you're not meeting the limitations of your license so this is very much worth being aware of when you're building out a flow and you'll see loads of examples in my videos showing you these amazing techniques for efficient flows so this is the flow we're going to build I'm going to do it step by step you can see there are only seven actions I get the items and then using a combination of selects and composers I then will sum I'll Group by and I'll sum those individual groups as well as well as counting the number number of expenses per employee and this amounts to seven API calls all the way from the get items down to the final select is seven API calls that is it no apply to eaches and no need to initialize any variables we can also see that if I pop open in this raw output this is what we're looking to achieve I have an array that has the employee ID the name their email the total cost which remember was $750 for John Smith and the count so they've got three individual expenses so I've now jumped over onto a new flow I have the manual trigger and I also have get items for all those employee expenses in that new list I've got the employee expenses list and the first action I want to add is a select and select is an incredible action to become familiar with what it will allow me to do is it'll select from an array so as in a table of data in this case all the items from our list and then if I switch to this text mode I can go and insert a value and that will give me an array of those values and the aim here is to get an array of all the employee numbers throughout this list so I've got 50 items I know I've got 15 separate employees albeit there are some duplicates this will give me an array of all of the instances of those employee numbers so all 50 there'll be duplicates but what I can do is using a little technique I can get those distinct with a compose action so I add in my compose you'll have seen this in some of my other videos um if I go into the expression editor here and use the Expression Union with the open and close brackets if we compare the output of the array from the select against itself it'll actually give us the distinct values so we'll go from 50 down to 15 and it's that that we can then use to do the group by because I now know who those distinct employee ID are so I'll add that I'll actually save and test and we can quickly review the history with our flow complete if I pop open the get items we can have a look at the raw outputs and that will pop open on the right hand side here and we can see all the data that's coming back it's all part of this body value array with the square brackets and our first item here is bringing back loads of data loads of key values lots of stuff we don't potentially need and we can have a look at how to reduce that in the uh next iteration of our flow build but more importantly if I go into my select what the select is doing is it's actually taking that as an input so all that data that's come in from our get items and then it's turning it into an array of remember just those employee IDs so we will have 50 different numbers in here or strings um and I know from this there are 15 unique values so that's where we cleverly use that Union expression in the compose and you'll note that if we look at the output now we just have those 15 unique numbers they're not in any order so maybe also be quite useful to sort those so back into edit in the get items one of the ways to reduce the amount of data that's brought back is actually to have a look at some of these Advanced parameters and we have um a limit columns by view so I have this group by summon count um that's one of the views I've created I could use the uh all items in all honesty it's the same in terms of columns if I go into my compose remember remember we've got that Union and I I could actually sort that data using the sort expression so I will wrap that Union in sort and that will now bring through those IDs in ascending order if I want to reverse the order there is also a reverse expression the next part sounds pretty horrible but it's all about converting this array from the get items into XML so all you have to do is insert a compose we can give it the the name compos root because at this point all we're doing is creating an object so the squiggly open and close brackets followed by the word root in quotes here and then some more squiggly brackets after our semicolon that I've just inserted a key which you can call anything but I'm going to call values and then another semicolon followed by the body value that comes back from our get items so all this does is it gives the structure required in order to convert the Json into XML and that's what gives us the power to do a lot of the searching and grouping by and summing without using an apply to each now you'll note that I'm getting an error it's because I've forgotten to put my closing bracket there so the bracket should match I've got two opening and now I've got two closing and that should be all happy next up what I need to do is I need to convert that into XML again the easiest way to do that is to use a compose you could combine a lot of these Expressions into one but for e and because it's only one action I'm doing it as separate actions just to try and help the understanding but we use this expression XML I go into my Dynamic content I choose the output from the route that we've just created which is our object in uh Json this will convert it into XML I hit add and we'll give this a name of compose XML now when we looked at our SharePoint list at the beginning we had a total sum for all of those values and just summing up a big array of numbers can be very intensive on API calls if you've got 100 different numbers and you're trying to Loop through them using a variable and apply to each of course that's going to be 100 API calls when you're updating your variable so this trick alone is going to save you an awful lot of API calls just for summing up numbers in an array and in order to do that again you won't be Sur Tri to here I'm going to use a compose action I'm going to give that the name compose total and we're going to see a bit of XML now so I'll go into the expression bar here and I'm going to type in X path and what x path allows me to do it allows me to pass in an input which must be XML so the output of our compos XML and then as a second parameter we need to pass in an expression so you'll see I've put in double quotes here and in order order to sum up those values I need to use an expression called sum so I'll type in sum and then we need to identify the elements in our XML now this sounds again complicated but I'll show you a little tool that I use is incredibly handy but for this I want to get with a wild card two double slashes root which is the name of the key that we have in the object above followed by two more forward slashes to hit the cost element or the cost key that we have it's the name of the column basically that I have in the SharePoint list or the data source so we'll have a look at this in the history it'll make a lot more sense then but if I hit the add button it's accepted everything is happy and we can go ahead and save test and have a look at the flow history so with the flow complete remember we made some changes to the get items to reduce the amount of data that is being returned so I have a look at the raw outputs and we have a look at that body value if you can cast your memory back we did have a lot of extra information in there there are still some system values but a lot of the data related to who owned it and who created it Etc has been removed so it simplified that data structure if I go into the compose that we've got here remember I Ed that sort as well so these numbers are now not only distinct but also sorted from 0 through to 15 then if we have a look at our composed route remember what we're doing is we're taking that array that comes in from get items and we're just simply changing the structure so if I look at the raw outputs all I've done is added in a root and a values and then I've passed in that array of data that comes back from get items and because I've managed to do that I can then look at the compose XML and pop open the raw output here well it doesn't display very well but if we have a look on the left hand side you can see what looks like XML and it's this that allows us to then use x path and I'm actually going to copy this and paste it into a tool and show you why I used that sum expression so I'll take advantage of this new copy feature here and copy that to my clipboard we'll have a look at this composed total which should be that 11,590 it's currently zero so I've obviously got a mistake in there somewhere but this is perfect chance to go and explore why so the next tool I'd like to show you and I've shown before in some of my videos it's X paather it allows me to paste in some sample X ml which I have on my clipboard I can format and hopefully there we go we've got all that data which is all the items from our SharePoint list and at the very top you can see an example of an X path expression now if I hold down the control button I can actually select different elements and different strings and you can see the expressions are being constructed there and showing me how you select different values so this is what we're ultimately creating we're passing across an expression into Power automate via that X path in order to sum up the total now whilst the sum is returning zero if I go back into edit and have a look at the expression that I've passed across I can go and grab the sum and the expression and I can pass it into the query at the top here I'll just remove the quote and you can see in fact yes the value being returned is zero now the reason I believe that is possibly happening is because it's K sensitive and cost should have a Capital C which that is the reason we can see now we've got 11,590 being returned so very easy fix for our flow all I need to do is put in an uppercase C and that will then allow us to get back the total we can go ahead and save and test that so if you look at the latest run the latest value in that composed total is indeed 11,590 so case sensitive make sure you keep an eye out for that when creating these XML Expressions so next next thing I'd like to do is to create a group buy and with that group buy i' then like to get the number of expenses and also the total of expenses so this is slightly more complex I'll put the flow into the edit mode again and I'm going to use a select so this select is going to use the distinct employee numbers and we've got that of course in the compose action that we created at the top here so the compose outputs we have in this particular one here sorted in numerical order the union which is a distinct values and I'm using that as the input for our select and then what I'd like to retrieve back is the employee ID and I'm just putting in place some placeholders their name their email address because of course I might want to email them individually I want the total cost and I'd also like the count of transactions so the employee ID is actually relatively straight forward to retrieve back because we're passing in an array of all those employee IDs the expression for the employee ID is actually just item open close brackets so I can type in item open close brackets and that will get me the employee ID because this Loops through the array and then hunts down those values that we provide as Expressions so I can add that in there no idea why it's showing is a body value but I'll put that down to maybe a bug and hopefully when it's saves it will correct itself when it comes to the name what we're looking to do is we want to use x path and the X path is going to be very similar to the expression that we created for the total so to get myself started with an expression I'll pop open the expression tab here for the total I'm going to copy that expression go back into my select into the value here and then we'll paste that expression in So at the moment it is going to use x path on that compos XML and it will sum up the cost but of course what I want is the name if we jump across on to X paather and I'll hold down control and if I want to get someone's name we can see we construct an expression like so so values name and then text and these integers here represent the element that's been selected so the first and the first name and the first text value as well that allows me to get Michael Shen back so courtesy of chat GPT and a bit of experience with XML I'm going to write the following expression where I'm getting the values which is basically everything but then we're going to search by the employee number which is one of the cells or one of the elements within our XML equaling 001 if I put in place that closing bracket we can see on the right hand side it gets all three examples of the data where the employee is equal to 001 then with that I want to get back just their name so for slame and I only want the first example of their name so I can actually wrap this in the opening and closing brackets here and select the first which just what happens to be the number one we can now see we've got John Smith so that gets us back the string I can also convert it into text with this uh expression here and this will set us up nicely for our power automate flow so I'll copy that I'll jump into Power automate and I will replace this whole expression here which is equal to sum with the expression that I've just built that will get us the name of that individual and I can also at the same time copy this because it's going to be almost identical for the email now you're probably thinking at this point this is not Dynamic this is going to get employee number 001 for all Loops well that's true but we'll stick with this for now and we can work on it into the expression bar again I'm going to paste this in and rather than the name I'm now going to get the email simple update there and we can add that in now the cost is the total cost and so again we're going to use an example of summing and probably the best way to demonstrate this is to jump back onto X paather and we'll adjust this expression ever so slightly so I'm going to take that off at the end here I want to get the cost and hopefully we're seeing some numbers there on the right hand side based on employee number and all I want to do is I want to summ it so if I prefix it with sum we can now see we've gone from the three individual values to 750 and that based on the employee number gives us a total cost again I can copy that jump across into my flow here into the expression bar and if I use my uh Windows notepad with Windows V I can actually bring up the previous X path expression and I can overwrite that with the expression that I've just built which is the summing there we go and we can hit add now the count is again same idea all we want to do instead of a sum is to count if I go back into X pather we can see I've got sum if I change that to count I now have three so I know there are three numbers so back into Power automate I can actually just copy this expression here and I can paste that expression in but replace the word sum with count so these functions count some they are case sensitive so make sure they are lowercase so we'll hit add and we can save and test and see what we get back so with the flow complete we can see the input is that array which is our distinct employee IDs and then as part of the output we can now see we've got the name and the email albeit those have come back as arrays so there's a slight change I need to make to the Expressions but we can see we have the cost and the count and the cost and the count is the same for all of them because it's not Dynamic same with the the name and the email but we're now going to use this employee ID the 001 to dynamically update the expression that we're using in X path so previously I had a fixed value of 001 so we're always returning John Smith but now we're going to make it Dynamic and use the number in each of the loops within our select so back into edit first things first what I'd like to do is make sure that I'm actually getting back just the name name and just the email so because it's returning an array you've probably seen this before I want to get the first value back so in square brackets I put in a number zero and that will get our first the alternative is to obviously wrap the expression in first so I'll show you both examples depending on your preference if I go to the start of this expression which my mouse is not liking I can type in first and I can put in a closing bracket there we go and that is exactly the same idea as putting question mark and zero in square brackets so what we really need to do now is to make this number 001 Dynamic and in fact let's pop open this expression bar perfect so we can see our whole expression here and what we want to do is make that 001 Dynamic so let's try and break this expression up a bit within our text editor so everything up until this bracket here is in relation to the string that gets ped so this string that I'm highlighting is the one that gets passed to our X path expression in order to get the employee equal to 001 but we're going to create a concatenation so there's an expression concat and I can type that in here concat with an opening bracket it's kindly put in a closing bracket which I don't need but I'll remove that and go to the very end and put my closing bracket so that marks the end of my concatenation as things stand but what I want to do is I want to create a concat ation with the dynamic value item in the middle here so I've highlighted 001 I'm actually going to remove that with a comma and I'm going to put in another comma as a placeholder for item we can put in item with the open close brackets there and we need to finish off this concatenation so it needs two strings the first of which will take us up to this equals so I can put in a single quote it's kindly put in two but I'll remove one there we go and then we want the third string which is after the item which is our employee number to start from here so I can put in a single quote again it's kindly put in two but I'll remove one and so now we've got a concatenation of this opening part of the expression the dynamic value for item which is our employee number and then the final part of our expression and then the very bit at the end here as we remember is to get the first value from the array that's returned because in the test we saw that an array was being returned now if I hit up update everything should be good and we just need to do exactly the same thing to the next four Expressions so I'll go I'll go a bit FAS this time hopefully a concat Open brackets I'll go ve very much to the end here and put in a closing bracket and then 001 I can delete I can put in some quotes and I can put in a couple of commas and item open close brackets and hopefully again we can see from this that we have our concat we have our first string we have our Dynamic value and then we have our second string up to there everything being good H update it's happy and then let's just do the same to these other two I quite like this new feature being able to pop open this expression bar and uh also work off of different return lines so the sum again if I put in a concat an opening bracket remove that one that's inserted sticker another bracket at the end to close off that concatenation I can highlight that 001 I'm using things like shift and control and my cursor Keys here to do the highlights there's no mouse involved and we can put single quote and also another single quote there it's putting in two and then stick item in the middle and then last but not least same again I'll pop up in that expression bar I'll put in Con cat jump to the very end put the closing bracket and we can highlight the 001 put in the single quotes a couple of commas type in item with open close brackets update and let's save and test so interestingly when I've come to save that I've got an error message and after a bit of exploring and looking at the new designer um versus the old designer I have discovered that by inserting return lines is actually under the under the the Bonnet for sln being added in and it causes these Expressions to be invalid because when I was explaining it I was putting in returns and making it easier for you to view but actually it's creating an invalid expression so if you remove any return lines like so and we'll remve that return line I can then update the expression and hit save and we'll find that actually power altimate will now accept that expression but obviously complain about another so let's have a quick look if pop up in the other Expressions expand the bar so there's nothing wrong with that one have a look at this one expand it there is a return there so maybe I can remove that one perfect hit update I'll check this one as well expand it there are still return lines in that one so if I just remove those return lines hit the update and save fingers crossed we now have a happy flow and we can go ahead and test so you'll see that the flow has run incredibly fast we have all of those actions now that we're we're totaling up that initial total of 11,590 which is all of those items and it would just be as fast with a thousand or 10,000 potentially maybe slightly slower but not as slow as an apply to each and if we now have a look at the select where we've got that input again with the employee numbers from 00 01 all the way to 0015 if I pop open the raw output we can now see we've got all of our different employees their names their totals their counts because we've now made those X path Expressions Dynamic by passing in their employee ID as part of that concatenated expression now if you're building this and really struggling to get XML to work my suggestion would be to of course use x path to explore the Expressions but then if you're getting loads of errors in power automate what I'd maybe suggest you do is instead of doing everything in the one expression here you see I've got X path and uh the output and the concatenation is actually just to create an array using concat so you could just generate the expression so if I was to give you an example all the way up to this point here this is a concatenation that creates an expression and in fact if I was to copy that right now and put that into a new field I can say XML put the expression into the expression bar here and add it if we save and test it I'll show you what I mean so the the flow run if I look at the output again I can now see that I've actually got an XML expression here and this one is set up for employee number two so if I was to copy this to my clipboard I can jump back onto X paather and I can test out that expression and we can see it returns Mary Johnson so I know that I've generated a valid X paath expression using power automate I validated it in X paather and uh I could now take that value and or take that expression that I've created and then add it into the full X path expression to retrieve those values so I'll just get rid of that X path expression there the things you can do with this data because it's now nicely structured one of the first things you could do is just to create an HTML table so of course with a create HTML you pass in an array and our select is an array and that will give us a really nice summary of all of our totals the count and the employees and the employees email addresses all into a table which of course you could then pass to an email and send out to the team the other thing you can do is you could Loop through so I could do an apply to each and with that apply to each I could Loop through the output of our select and then that would allow me to email each of those employees so I can put that output in there I can add in an action to send an email and with that send an email action I could then send each member of Staff an update with regards to their expenses the fact that they had x amount of expenses and their their totals were were y so in order to do that you've got two options you can either use par Json and the way you do that is you would add in a parse Json you would pass in the sample output from your history and that would create your Dynamic Expressions the other way is just to write expressions so because I've passed the output from this select into my apply to each in terms of accessing the values in my send an email all I need to do is create Expressions based on this map so it would be item open close brackets and then their name or their email or the cost or the count so in order to do that as a demonstration if I go into two enter custom value and if I type in item open close brackets question mark square brackets single quotes I can type in email this is not Cas sensitive so you don't need to worry so much here um and I can add in the email the subject could be the employee ID so I could put in employee ID and I could also add in some text say your expense summary for instance we can then go into the body and say that your three or four expenses so we can use item open close brackets and count that'll get us the number so this will say your three expenses for however many dollars which was uh which was cost so I can I'm pasting it an old expression so cost so item cost so your three expenses for $750 will have been received and will be reviewed in due course now of course you could take this a lot further you could generate an approval process for each of those users and if you wanted to still bring in the expenses for that employee as another table then you could use a filter array action based on their employee ID so what don't we just demonstrate that if we add in a filter array action at the top here we can go and filter our original array of data from our SharePoint list the body values we're checking to see where the employee ID employee number here is equal to the m ID which is part of this apply to each Loop now the difference here if I was to write an expression rather than it being the item open close brackets the filter array actually has its own context for item because we have as you can see here item employee number that that refers to this array so if I was to type in item here it refer to this level within this filter array so what I actually need to use in the dynamic content is current item so you can see here current item and then in square brackets I can put in M ID because I'm wanting to get the employee ID from within this apply to each not from within this array here so I can hit add that will now compare the employee ID in this array against the employee ID in this Loop for the apply to each we should get back three in the first Loop and however many in the next Loop and then I want to create an HTML table with which will be a nice personalized HTML table for each of the employees to let them know their expenses if I go into the dynamic content here we can see that we're getting the body back is one of the dynamic values this is in fact a bug I've reported it um what we need to do is go into the functions or the expression editor into Dynamic content and you'll see that you have outputs is now one of those options so this is the expression you're look looking for it might be there first time but if you go and add that in that will create us a lovely HTML table based on the filter values and we can add that into our email here and say here is a summary of your expenses and with that we can add in the dynamic value for the output from our HTML table so this pretty much concludes the demonstration I'll save it I'll test it we'll make sure that everything that comes back is as expected but you have seen now how to sum up a large array of numbers we've managed to group them by getting the distinct values and then with those distinct values we're using X path to both sum and count the number of expenses from that large array using six actions some Advanced Expressions but will give you an incredibly efficient flow so our flow almost completed I can see that the filter array is completed and if we have a look we've got all this is input but if I look at the output hopefully it is just based on employee number three so we can see this one here is for John Smith also John Smith and also John Smith so certainly the filtering is working and if I step through the different Loops we should hopefully again see in the outputs we got different groups so Sarah in this case we've got all of Sarah's expenses so certainly the filter has worked but the create an HTML table has failed because the property is of type object so that means I'm passing through an object rather than an array and we can have a look at the input here and we can see we have a from and a body so we actually want to pass this through not all this stuff prior which is an object so wrong expression even though I said it was one to look out for I've still not got the right expression and if we go into the edit again jump in here we are using outputs and in fact I think it should be um body so if we delete that I'll just quickly look at the dynamic content again we've got body list of items if I hover over that I will or look at the code view we can see the expression which is outputs of body value which I don't believe is correct and so I can see that that has failed also if I go into edit and then we'll go into the classic designer and see what dynamic values are available here into the apply to each into our create HTML we can remove this expression and we can see the filter array has a body which sounds a lot better so the expression is body with the name of the filter array in single quotes we can save and we can test it in the trusty classic and reminisce about our old friend classic designer so the apply to each is running there are 15 Loops I could of course turn on concurrency if I wanted to speed this up but we can see it's completed successfully if I go into my send an email action I can see that those various emails have been sent I really hope that Sarah Miller doesn't exist at internet. XYZ and if I jump into my Outlook email we can see in my send items I have all of these different emails to those different individuals um with their summary of their expenses yes that table is pretty horrific um and of course if you want to edit that table you can do so with the advanced options but ultimately what we've demonstrated now is the summary by using the group buy we've managed to get that there are four expenses $820 in total and then we have our table with those expenses the 40 300 3 and 180 which is 820
