Power Automate: Group By, SUM, COUNT in 6 steps! No Apply To Each | Runs super fast

in my previous video I took data that looked like this and turned it into this where I removed all the repetitions counted all the repetitions and then even summed the values I did it all with one apply to each and now I'm going to show you how to do with no apply to each all in just six steps here is my source data and here the ID is unique each time but you'll see that the customer is repeating themselves same with their customer ID but they're purchasing is different each time the first step is to create a unique list of the customer IDs once I have that I can count the repetitions and do other aggregations to get the unique IDs I'm going to come over here and use a select action so I pick my select my from is going to be my only option of performance assort and I'm not doing a key value pair instead I'm going to switch to this map mode and then if I select the expression here I can pick an individual item by typing in item and then question mark and then what property I want and I'm going to select customer ID since that's what this property is and I named this customer's ID repeating click update now if I hit save and then come up here and run a test I like to open all my tests in a separate Tab and if we open this we got a bunch of repeating customer IDs let's get the unique ones if we come down here here and hit add action again I'm going to select a compose and there's a great expression here called Union which lets you combine two lists together and it'll just select the unique values so I'm going to do Union and the source is customer ID is repeating and then the second list I need to compare to it's a little weird but I just pick the exact same list again and if I hit add save if I open up up my results down here you'll see that they're nicely unique now the magic to accomplishing all of this is by using XML first we have to take our source data and put it into XML format so I'm going to type in compose select compose I'm going to call it customer performance XML setup and then just copy exactly as I'm doing it right here which is Open Bracket close bracket type in the word root colon array if you're familiar with Json you will realize that I'm just making an object here another colon close it and then inside of here let's click the dynamic expression and we're going to select our performances Source from earlier so in the end it looks like this and then let's add one more compose here and this time we're going to actually convert that XML setup to XML so I've named it customer performance performance XML and then down here you just select the expression and then inside of here if you type in XML and then select the previous item we made of setup click add and let's save it in CR results and if we scroll to the bottom select customer performance XML we get all this gobleg but if we select it all there's a website called xp.com and we can paste that into here and if we click this format button here's our data and we can use x paath Expressions to pull whatever data we want in this case I'm going to do root array so just type in this and then okay it gets me everything but if I put in bracket and I type in customer ID equal 2 like that look it found all of those individuals it's showing multiple results cuz it's an array but watch what else we can do I'm going to choose customer ID equals 1 I know that Anna Smith appears three different times here and look at this Nifty expression at the very beginning if I type in the word count here and then at the end to look it got me the total value of three and if you're not impressed yet watch what happens if I do sum sum isn't valid because it wants me select a specific value from here not just count the items so in here if I put SL and then I type in cost boom it just totaled up all the costs where the customer ID equals 1 so anus total cost that you spent is 25 we're going to use this our advantage let's bring this home this is the final action we need it's a select action so if we type in select and choose it this is going to help us build the final Json so our source is going to be the original unique IDs and then we want to make the first property of the actual customer ID so we know who's who and in here if we press the expression we just need to type in item up here and it will select that individual ID click add I'm going to name this final output I'm going to name the next one transaction count and then in here let's select the function expression here's the expression from earlier to do that I'm going to copy it hop back into here now first I need to let power automate know that I'm using X path and X path is that language we've been using out here and it needs to know what we want to use it on what's our source data so our source sta customer performance XML and then we're going to hop into here and then paste that expression like that and if we click add now don't actually do this just watch this first I'm going to hit save and show you the results if we open it up here and our results are transaction count three um pretty cool but as you can tell this is hardcoded though this won't quite work for us we are going to put it in this text editor if we want to dynamically generate the ID we're going to have to use an expression called concat concat just puts different strings together so in here we want everything up to here and then we're going to separate the one out except instead of one we want to put our item from earlier it's really easy to do something like forget to put an ending parentheses so make sure you get that and then now we're good click update then now let's save and run it now if you find all this overwhelming I don't blame you if you need a slightly simpler version I have a different video but with using one apply to each so be sure to check that out if this is freaking you out all right and down here that's already looking better but let's add our customer name here to do that we're going to get rid of the count and then at the end of this we're going to do a slash and then just type in the property we care about which is customer and then there she is repeating that many times and we don't want all of them but in power automate we can just select the first one of interestingly enough this is almost harder than calculating the sum and the count so I'm going to do customer but then this is a little bananas at the end of all this I want to put a question mark and put a zero the zero means I'm getting the first item in the array or the list because if you notice this gave us all items where the customer ID equals one so the final version looks like this don't forget to remove your count here uh Slash customer you also need to select the first item in the array like this and then just to be on the safe side even though I don't think this is necessary I like to put it all in one line and then I copy it and then throw it into here for customer name and then while we're here let's just finish up the total spent cuz it's well exactly the same as transaction count except we just have to modify a couple things in it so in here let's just change up instead of count we're going to do sum and then at the very end we also need to select the specific property the final version looks like this don't forget your bracket and then also remove the bracket at the end here and if we zoom out let's update it that green bar is a good sign if we scroll to the bottom let's look at our final output and oh we're so close everything all our totals are right transaction counts looking good let's get rid of this thing so in customer name after customer you put text and then par open close parentheses and now if we open that up we'll see everything is beautifully formatted now you may not want to work with customer ID maybe you want to work with customer name so you want to do something like where customer equals Anna Smith in this case a lot of times XML can kind of fall apart with spacing so you have to add these stupid functions like this normal space and then add another parentheses and then it starts working this is totally doable but with these little single quotes it gets kind of annoying if you really need to do this my next video takes a deeper dive into X path to accomplish this stuff thanks for watching if you want to go deeper down this x path Rabbit Hole I got a great video on how to match on different Keys which gets into really the most advanced parts of xath that I've seen if you want an easier video than this to accomplish the same thing check out my other groupy video

Channel: Untethered 365

Views: 1,522

Keywords:

Id: VSvoB7bTV6o

Length: 10min 11sec (611 seconds)

Published: Thu Jan 11 2024