Power Automate Flow filter array to create summary SharePoint Data

in today's show we're going to create sharepoint summary data using powerautomate aka flow the idea is we're going to use a filter array we're going to select some stuff or compose some stuff we're just going to kind of go through some motions of grabbing some sharepoint data bringing it in sorting it filtering it and then using that data to write back to sharepoint so just teach you some of those course flow skills that i think we all need to be better at but first here's our intro hi my name is shane young with powerapps 911 those guys and today we're going to talk about playing with our sharepoint data but we're going to do it in a programmatic or repeatable way using our friend power automate so the idea is this came from one of my customer calls i had this week that then someone asked me the same question the next day i was like oh this must be something you guys want to do but what she was looking for was she wanted to grab her sharepoint list so she had a big sharepoint list of data that had been populated and she wanted to be able to go through that data and she want to find all of the data or all the rows records if you will that have the same piece of metadata and then do a summary so in our case we're going to use my sharepoint employees list we're going to count all the people in each department and then we're going to write that back to the sharepoint record so we have that update and then just to show you guys a couple other tricks and kind of inspire your brains a little bit we're going to do something like maybe take the average age of someone in that department or just something like that just to kind of get your brains wrapped around how to do some math on that filter data and so to do that we're going to use the filter array action so we haven't used that one before like i barely ever use it in my life so i just thought it was good for us to work with too something fun i thought so so switch over to my desktop and take a look the first thing we want to do over here on the desktop is here's my sharepoint list and so you can see that we have a department column and i have several people that are executives it's four to be exact these are once again fake data but i have four of them i think there's two people in finance you know and so some of these departments have multiple people some only have one but what i want to be able to do is i want to be able to go through here so we're going to iterate through this data and grab nicole's record and we're going to say hey i want to count how many other people are in the same department as she is so that'll be four and then we want to store that into a sharepoint column so that was kind of the requirements and i don't have a sharepoint column at the moment for that so you know what we're going to do we're going to jump over here and we're going to say list settings then we're going to scroll down here and we're just going to create a column to store this data and so we'll just call this something simple like num employees department something like that remember no spaces if spaces are a thing you like to use your sharepoint list you should go watch my other video it talks about why they're evil so we're not going to have a space and we're going to make that a number column and then we'll scroll down the bottom of the page and click on okay i don't know why after all these years we still don't have an okay up at the top but we don't okay so that'll give us a place to put that data the other thing we're going to create one more column and so for this one we're going to calculate the average department employee wage or something so average department wage and we're also going to make that one a number as well and so the reason i want to do that is because when we get done with kind of the base demo that i planned i thought that was like one step further and i was one step further i always seem to make these more interesting so let's say okay cool okay so our sharepoint list is ready for us those columns are obviously blank so we're going to jump over here to power automate we're going to say create and for right now i'm going to use an instant cloud flow so anytime i'm trying to learn a new concept to figure out new things it's always an instant cloud flow can only manipulate sharepoint data or cause a timer or anything to happen i just want to be able to trigger the flow when i'm ready to trigger the flow to prove it works but then we could change the trigger at that point too every time a new item gets edited in this sharepoint list or every time this list changes or whatever you want to do but we're just going to manually trigger this one because what we're trying to learn is the concept not put it into a working solution necessarily yet so manually trigger it new step the very first step is we're going to get some sharepoint data so sharepoint and then here we're just typing get items there it is right at the top perfect what site address so we're going to go to our same sharepoint site powerapps videos and then we're going to go to our dear friend the employees list so this list gets you so much there you go and so we could do all these other ones now one of the things i will remind you that i don't necessarily need to do right now but you guys might if you hit show advanced options you can do a top count and you could limit this so if you were working on a data set that had 10 000 rows while you're iterating through this you don't want to process 10 000 rows right you don't want 20 minutes between tests so you either want to build something like this and explore this concept on a small list or you can say hey only grab the first 10 rows and so then now it would only get the first 10 rows out of my data set which would give me the effect of being able to iterate very quickly to do my testing and learning and then we're done we just take that out and it would get all the rows so another little handy trick to learn but there you go we're going to get all the items so now we've got all the items what i need to do is i want to be able to loop through all those items remember we want to grab every record one by one and kind of process this so i'm actually going to say a manually apply to each so type apply and there's a manually applied each i don't know why i keep saying manually but i do apply each and so we're going to say i want to loop through the value that comes out of get items so value is the the individual rows or records that are going to come out and so we're going to say hey apply to you because i want to do something once for each one of those as we go cool so what do we want to do well we're at an action and so this is where i got into some weird territory that i had never done but we figured it out so i'm gonna share with you so i want to filter the array because what i want to do so i want to for every record right so as we're looping through let's just go back over here so when i grab nicole's record right it's the first one we'll grab i then want to filter all of these data to find just the four people that make department right so if we say um filter by um executive right now right we'll see that there's one two three four records so that's what i want to do but i want to do this programmatically over in flow not over here manipulating this by hand right we could do it by hand but who's got time for that so what we're going to do first is we're going to say array to filter and so that is going to be the value of git items so if you put this and hover here now one of the things i want you guys to be very careful with this because we're going to kind of use the the data twice once that's coming out of apply to items and ones that get items it's going to be a little confusing at times so at any point that you want to make sure you've got the data that you've got right if i hover over this it tells me outputs get items body slash value so that's the value from up above right so that is this output which is what we want so i want to filter that data now it says hey i want to choose a value so what value do i want well i want to find where the department so i'm going to scroll down here and click on department and this is where you got to be smart again because we're going to say you know it's real easy like department is equal to and then oh let's scroll over here and then department equals department but if you look at this now it says all right so that is the applied age so that's nicolas department that's one of the ones we want cool is equal to items apply to each the department that it just pulled well that that that would just always be true that's no no right that's not what we want we don't want to compare department to department from apply to each this needs to be the department that comes from up here right a little weird so now what we're going to have to do we're just going to exile it's like we never needed to do that begin with but i want to show you the most common mistake i think you're going to make and you leave me comments be like it was just matching everything that's why so what we're going to do now is we're going to say i want to write an expression so we're just going to manually call this out to do this we want the item out of that we're currently filtering right so the arrays data we're currently filtering so what i'm going to do is i'm going to do item oh there's the item function a question mark i'm going to move my cursor so it's out of the way i'm going to do a square bracket a single quote and now it's going to be our column name and so in our case it is department so department just like that and then we're going to say okay so this is going to look different and this should look different right so this one is the apply to each department so in the first record that'd be nicolas the second record it'll be mine right so that's the current one from the applied each and then item department is when we're looking through this array of data which is the whole data set we're going line by line by line by line by line okay so if that makes any sense to you guys whatsoever then you're doing great because that's that's what we're after right we're now able to filter this array and so this should come out now with a little bit of um or this should have the number of rows or number records that match so we're going to say add an action here let's scroll down here we're going to use a compose for right now i misspell compose and power or flow still figured out and so in this compose what i want you guys to do is we're going to use an expression called length and so length is the same as count rows in um powerapps if you're one of my powerapps viewers but the idea here is that you can either count the length of a string so if i gave it shane is awesome right that would give you like i don't know 15 characters or whatever it wouldn't be true either but whatever and or you can give it a collection or an array and it'll count the number of rows so in our case we're gonna say i want the length and then i'm gonna click on dynamic content if this little thing ever goes ah i'm gonna go back over there i ever mentioned flow's a little frustrating to work with i tried not to say it too much but i'd say it a lot let's try skin length parentheses click out of there click back in there oh now the little pop-up's gone now we can click dynamic content and so we're going to say length of the filter array so in the case of nicole's record this should give us four because there should be four records that matched her department so we're gonna say okay that looks good all right let's uh let's do one more thing now let's just test this let's hit save obviously overflow a name um video [Music] filtered i don't know we'll say save all right so then that looks good so we'll say test we'll manually trigger the test we're going to say test up here and if you get a warning about not enough rows whatever just ignore it it's annoying we'll say run the flow and say done it's not bad few seconds of a flow and now what i'm gonna do is i'm just gonna say compose and we got three now i promised you four but i only got three why because i wasn't thinking about it but i left remember we told it up here um to only get the top 10 rows well the fourth one is in my greater row set so if you mess with this make sure that you don't get angry with yourself because i could see how i would have gotten frustrated if i hadn't just remembered i did that but so we got three of those and then if we go the next record which is also me so this one should be three and then the third record will be uh jennifer oh she's also executive so that's gonna be three also nope so yep okay there's three for jennifer and then let's do so this one um oh no this is filtered data i'm so confused people see what happens you start filtering things go away there we go so we got greg and finance so i'm a little confusing myself right now so what we're going to do we're going to do two things we're going to say edit i'm going to go back up here we're going to get rid of this so we get all the right data okay the second thing i'm going to do is i'm going to go down here and we're going to go a little further with my compose right because their end result is not this composed but i use this like i use labels and power apps so i have a way to see what's happening so that's the length then what if we just go right here and say hey i also want to add a dash and then how about we just add our friend the department again now we want to make sure it's the right departments we're going to hover items apply to each department so that would be nicole's department on the first row so then now if we save we get this little warning thing this is why i told you a second ago just ignore this bit there we go now then we'll say test we'll automatically just run that test again and so now what will happen is a we should get the numbers i expect but we also should have the label of information so right here down here so there's four executives notice this is one of 12 now there's one person in finance that's correct four executives four exec or two people in it boom right so we are actually doing the calculations that we wanted yay okay so now that we have the calculations done and we use the little compose this to validate right the compose isn't doing anything for us but it's how i think so now we're going to add an action we're going to type in sharepoint again we're going to go right here and then we're going to go down here and say oh we're just going to search for update i can't find update and we're going to say update item we're going to choose our sharepoint item our sharepoint list again so powerapps videos our employees lists perfect the id is going to be the id that we're currently getting out of the loop right there and we can hover and make sure it's the right one items apply to each id perfect so for title we're going to do the same thing we titles required so we have to push it back we're not changing we're just using the same one and so then down here number of employees department well we've already figured this out right it's just length and then put the cursor right back in the middle dynamic content and it's the length of this perfect say okay say save oh there's that silly warning again just go away warning and so then now of course we should be able to hit tests finally and get this to actually go this time okay that after like 10 seconds it looks like it worked so what we'll just do is we'll go here we will refresh this page we'll scroll to the right number of employees four one four two two right numbers we could validate all of them but i don't think you guys want to watch me do it i think you trust me that those are all correct so there you go we have solved the initial problem we set out to do which is grab all of our data do some summarization and then push it over now what i want to cover next though let's edit this thing again so that was pretty straightforward because really we're just writing the links right just a simple piece in there but one of the things that i started to think about was what if i wanted to do go further right so for example we want to get that average wage per uh employee so that's a little bit trickier because that's not just as simple as counting the number of rows that came back from the filtered array so i'm what i'm going to do is i'm going to throw another compose up here so to help us think right we know me i like composers there we go and so what i want to do here is try and loop through and so there isn't a way like in powerapps we do a sum right so sum table and column and it would just add it up that's not possible over here so i thought i compose i go to expression and i'm like all right let's go over here to the math see more and so they have min and max they'll take a filter array but all the rest of these are just let me add up single numbers multiply divide so it's not going to work for us so compose is actually not going to help us boo i'm very sad so we'll get rid of our compose delete it so what we're going to have to do is now we need to get a variable to store it so we have to loop through the data that got filtered and store that in a variable so before we create before we can use a variable we're gonna go up to the top i'm gonna say add an action i'm gonna say variable and the first thing we have to do is initialize a variable so we're going to click on that and we're just i'm going to call it var total wage something like that perfect and so then what i want to do is i'm going to make that one a so there's either integer remember integers are whole numbers 1 2 3 4 5 or float which is going to have decimal points and since we're doing hourly wage and if we look over here at my employees hourly wages we should see they got a lot of decimals so we're going to need a float okay and then the initial value will be zero great so now we're going to drop in here to apply to each again and we're going to say right here right because we need to do it before we update item but add an action and we're going to say now we want to increment a variable so there's an increment variable action which variable var total wage so that just basically adds to it right so i'm going to say hey i want to add to you and so what we want to add to you from the um filtered data so we filtered the array we had this data but i don't want its item or its body so what we're going to have to do now is we're going to have to like write our own little um logic again and we're going to need another apply to each oh all right so i'm going to go right here we're gonna say add an action we're gonna do apply to each again so funny a party just get created automatically all the time here i am just keep making them over and over again and so we're gonna say hey i need the output of our uh previous guy right so we're going to grab the item and so then that would pull right it doesn't pull the body or the data out and so we're going to say for that oh wait where did i put this in one place oh no okay oh i scared myself for a second there so it's in here it's in here right like chaos has ensued all right i did too many things so let's just delete this startup right just just the naked time of me saying that this thing just does whatever it wants to do it did whatever it wanted to do so let's try this again go back in here add an action apply oh we got a spell apply right so apply to each again all right so let's try this again we're going to scroll to the bottom and we're going to grab the body there we go so i clicked on item last time like a dodo brain we want the body and so then now we're gonna grab our increment variable and just drag this up here right you guys knew that right yay drag that up there maybe that's the little thing you took out of this video and so then for the value we're going to grab we want the current items hourly wage so from our filter array no so we want this but we don't want the current item we actually want to grab we're gonna write a formula again so we're gonna do expression we're gonna do item right we want the current item question mark square bracket like this and if we look over here i believe this hourly wage no space perfect so we're going to go hourly hourly wage just like that we're going to say okay and so then this should loop through all the ones that got and it is going to then be like hey awesome we've got that here so var total wage so now we'll go down here and say average department wage well wait but we don't want the total we wanted to take um we have to take the total and divide it right so let's do a little um little math here so go here we'll go to math see more and we're going to divide so we're going to divide we're going to divide our variable var total wage by what by the length again right we're going to do this formula a lot apparently a length oh click out of there back in there and then we want the length of um our dear friend where are you we want the length of our filter arrays body there it is say okay oh it is not happy with something i did let's check length is there that is that that looks right let's do this ctrl a let's just make sure it's because we're on the expression side no what did i do wrong you guys probably already see it so divide oh why did i oh i put the thing right so divide my variable total wages i lost the parentheses along the way by the length the body body boom how about that two all right let's hover over that again make sure that's right so div the variable total wage yep by the length and so then that should give us a um accurate look at the average department wage at least at least good enough to make you feel good now the other thing that i'm gonna i was gonna make the mistake so you can see me fix it but i'm not going to is keep in mind that as i'm looping through here um you know after we use this i need to make sure that the first time back through that this variable is back to zero so i'm just going to go down here add another action and say set variable there's variable and so we're going to say set variable var total wage we've got to zero it back out right because we don't want to like have like a rolling total we need to like do each group independently so we got to set that variable back to zero each time after we've used it so we go through the next apply to each right so for nicolo we've got a value and then for greg we got a different value so we're going to want to make sure we do that all right i feel good let's say save dumb warning again go away we'll say test test test and i click on that top one out of habit you don't actually have to choose the top and i could have left it selected where it was but this is going to loop through so hold on a sec all right so it took longer right i mean 20 seconds now so long but a really small data set so keep that in mind but i feel pretty nice that that will happen so no errors we'll refresh this we'll scroll to the right average hourly wage for this group is 40 28 average for this one 755 38. now so i would have expected right because i think nicole and i are in the same department okay so nicole and i are in the same department we have different hourly wages so that is not good so so something didn't work right all right let's go take a look at it then all right because i totally didn't try that in the pre-planning i decided on the fly for you guys that's what i get for trying things on the fly so let's see so if we go to apply to each and here so there was four records so it should have added those up so 40 28 38 70 up that looks right added 75 that looks right 253 right so divide that by 4 should be what roughly four or uh 50 would get you 260 to 240 so 64. 63. 63. 63 and some change is what i would expect that to be and so down here let's see what we got so we said all right average oh did i just look at the wrong column average apartment age is 63 that is correct hey shane keep scrolling this way now don't get hourly wage look at average people i got it right oh i thought i screwed up so there you go average apartment wage is right there and you can see that they are the same for both people that are executive which is what it should be and so you probably wouldn't ever do anything like that i get it right you're like why would you just calculate that store somewhere else for the i understand it's not about the in solution it's about learning how to do this type of data manipulation right so so no comments below about how that's a dumb scenario it was a dumb scenario but it showed you guys how to do some really cool stuff i hope um if they didn't show you how to do cool stuff then you can complain but there you go so we have worked our way through all this now it was interesting i was talking to my friend pieter earlier and he said hey you know this would this is the right way to do this uh you know if you have a small data set like that summing but he has this crazy idea in his head about like how to do it if you had a large data set how to do that something that we just did and so he tells me he's gonna write a blog post about it this week if he does i will put that down below in the description so if you're into like hey i want to do that mathy thing i want to calculate the average wage but across a thousand people then uh check out below and hopefully sometime this week there'll be a link so cool all right so there you go i like this this is interesting this is fun these are some new things i had never used filter array in a meaningful way until this week so i had to learn about it so i thought you should learn about it um as always have any questions or comments leave them below right i appreciate those hit subscribe you haven't hit subscribe yet either i'm almost to a hundred thousand i really want a hundred thousand people so i need you um if you have any ideas for other videos all that i'm always ears for those information as well i got a live training class coming up remember you also if you're a subscriber over in the training classes including the curated library which is only like 10 then you can download the working flow here um after i post it today okay anyway that's enough blah blah blah i got a kids basketball game to go coach so i'm gonna stop recording and say thanks and have a great day before you go be sure to click on the subscribe button over here so that way you'll be notified when new videos come out if you need any help or you want to work together whether your problem is big or small check us out at powerapps911 we do it all i rhymed or if you're looking for more formal training offerings we have those linked up here somewhere so check them out thanks and have a great day

Channel: Shane Young

Views: 40,540

Keywords: Shane Young, powerapps911, power automate, power automate tutorial, power automate tutorial sharepoint, power automate tutorial youtube, power automate filter array, power automate filter array output, power automate filter array sharepoint list, power automate filter array apply to each, power automate filter array variable, flow filter array, flow filter array apply to each, flow filter array output, sharepoint summary, flow item expression, jon levesque

Id: V1ThALavvf0

Length: 26min 22sec (1582 seconds)

Published: Mon Mar 01 2021