Effective List Grouping in Power Automate (Part 1)

[Music] i'm going to jump in and i'm going to move um kind of fast but don't worry um one there's it's it's it's cool but it's simple uh it's well documented so i'll show you some links to uh slower versions and then quick plug for uh jamie you're doing this too right the collab 365 thing that's going on tomorrow dynamics day there's actually a full version of what you're about to see tonight um on that at 6 00 a.m pacific time tomorrow but it's also on my blog so i'll show you the links to all of that once we kind of finish and to kind of set the stage i am a big proponent of getting people to learn and teach power uh power automate and probably power apps a bit different because it's so modular because um you know the bits and pieces are really more important than the final thing i try to get people not to spend a lot of time on the title of the talk right don't pay attention to the final dish focus on the ingredients and so in my example i'm going to do a thing with a sharepoint list but this will just as easily work with the dynamics 365 list of items or something else the intent is to take a field and group uh on on that field and and do some stuff with it okay and so we'll walk through that in my example i've got uh my sharepoint list and you can see that i've got these different things under title car vote playing ship etc and each one of those is kind of assigned to what we'll call a rep right so like this might be um similar to a account by rep list right so i've got you know one to three different reps and a bunch of different accounts so there's some duplicates over here if i were to do a flow lowercase f um to send a list or send the the accounts to each rep what it might look like is you know the trigger whatever that is a recurrence or whatnot get the items and then send the email but if i do that then what's going to happen is joe's going to get one email for car lisa's going to get an email for boat and bob's going to get an email for plane bob's going to get another email for ship and then joe's going to get another email for truck so you can see how that's less than ideal right so what we want to do is make it so that joe gets one email with all of his stuff lisa gets one with all of her stuff and bob gets one with all of his stuff right and we'll do that um i'll show you one way there's lots of different ways another reason i really like power automate is there's no single approach to doing a thing so if you know of a better way that's fantastic blog about it share it uh tell your story people want to hear it okay so diving in um i've got just my power automate home screen um and i'm just going to start you know building a flow i'm going to do a manual trigger just for starters but you could set this on a recurrence so like if i wanted this thing to go out every day right and so the first step is going to be to get my items from sharepoint i'm going to choose my list and keep in mind again the data source doesn't matter this could just as easily be anything the whole intent is to get that uh that list of things and then do some stuff with it okay so don't don't necessarily worry about this part of it it's the other stuff that's really fun okay so right now if i you know ran this flow i would essentially get back all of this stuff right so the next step that i want to do is is really just focus on the thing that i want to group by which in this case is that email column and then i want to get just the unique values right so the way i do this is i just use a select and i grab it from my list of items and i'm going to call this uh field unique email and then i'm going to grab that as the value right here okay and so now if i ran this i would get you know i get seven rows but it would just be the email okay and so now i still want to get the the unique ones and this is just a warning saying hey you're looking at a list you're going to get back a lot of results if you used an odata filter or some kind of filter you might minimize the results and make your thing more performant i've got seven rows i think he can handle it okay so the next step is to find the unique values and so to do that i'm going to do a compose and i promise this is going to be as cody as this thing is going to get and and it's really just one function and it's called union and there's other ones that will do the same thing um but but this is the one i use until somebody says hey try this better one and what i'm gonna do is do union with the output of my select twice and what union does is it compares two arrays right two different lists and returns a new array with just the common values of that thing so if i splash a list against itself it's going to give me only the unique values right for that list and so now if i ran this thing i should get going back to this list just the uh three different emails right joe lisa and bob that's it so why don't we test that just for fun to see if that's actually working any questions so far i know i'm moving fast but like i said this is being recorded it'll be shown tomorrow at six it's on my blog there's lots of stuff so it's really just to kind of pique your interest and say yeah i could find a use for that right so again here i've got my get items it's so big it wants me to download a list but here's my output and you can see i've just got the three emails right now that's cool because you know that's the first step but what i want to do is essentially go through this list go through this list here and run that filter for each one of these unique email addresses and do something with it okay so in my next step i'm going to do and apply to each and what apply to each does is it takes your um you know whatever it takes a list right and goes looks at the first item and does whatever's in this box um to that first item and then once it finishes that it goes to item number two does it everything in that box to item two and so on okay so uh i'm gonna use the output from my compose and just like derek in a production environment or in a real life scenario i would rename everything so it was pretty but we're moving fast um the guy jeremy gave me 15 minutes so that's why i'm talking so fast and so what we're going to do is uh we're going to filter our list right so our get items list we're going to do a filter array from that and we're going to choose the the value and i hope i'm doing this right it's been a while since i've done this but we're going to say where the email is equal to the the whatever current list item i'm looking at right so here's one more little expression it's going to be items to say i want to look at this item and then what array you can see up here it's giving me a little hint it says what's the loop name well because i was sloppy and didn't rename anything it's still called apply to each right and the field that i want to look at is the unique email i think i've got that right okay and so now it's basically going to filter hopefully fingers crossed this list of things by whatever item i'm looking at which remember is those three unique values okay and because that list isn't going to be super useful to me unless i do something with it i'm going to want to send that as an email to each of the different reps so but before i do that i want to make it look pretty so i'm going to take the output from that thing throw it in this uh create html action keep in mind how much coding i've done so far okay and i'm gonna have it create the columns automatically and then i'm gonna have it send that email to me but in the real world i would have this email go to the reps right each individual rep joe lisa and bob and report for what i just put what should i do i should do their name right so or at least their email address i could how are we doing should i do like a real quick function or should i just kind of go through and show the thing yeah okay let's see the function okay so the function that that i want to use is i want to take their email address okay so going back to this thing here okay since i know the the data well enough to understand that their first name is the first half of that email address right i'm going to take their email address and grab everything before the ad okay so first let's grab the email address which is the same as it was up here on the on the filter array right i'm going to take this items thing and essentially put it in this bit here items and then do each and then really hope i got this field name correct from the select email okay so that's just gonna grab the whole email address right but to get everything before the at or to to segregate that out i'm gonna do a split of that thing at the at sign okay so what split does is it takes that string and you define the delimiter which can be a character or a couple of letters or you know i don't know a semicolon whatever and then it turns that lit that string into what's called an array or a list so this email address would then turn into a list of joe and then company.com but i'm going to save an extra step and just grab the joe part by putting this little array element identifier which says i want the first bit if i wanted the second part the you know company.com i'd put a one there and that's just how power automate and i think powerapps does the same thing where it it starts the array numbering at zero right so keep that in mind so it's going to give me report for the first name and then in the body of that email i'm just going to put the output of the table any cool way to capitalize the first letter of what you split out so not a cool way you're talking like if i had what would be great and one of these days i'm gonna um you know write something like a blog article that shows a cool way to do this but you're essentially talking about like the proper function in excel right um where it capitalizes the first letter i don't know a way chuck do you know way is there a way so he's he's being quiet so i'm guessing there's not but like the the only way that i could think of doing it as messy which is basically you grab the first character and do an upper on that and then add the rest of everything out which to me is less than ideal right okay so fingers crossed everybody cross your fingers think good thoughts so let's see what happens green check marks all the way so far and i got an email look at that okay so we'll go back over here so report for bob report for joe report for lisa i am so tickled that this actually worked now you can see that in these emails in that html table it's given me all of let me see if i can zoom in all of the sharepoint nonsense right oh it didn't really zoom the table but it's giving me like all the little ids and all that stuff that's not anything i need here here's another way i can do this we'll go right here so here's the actual table for one of the things and you can see it's sending me all of this extra stuff to clean that up after the filter array what i would do is just do another select or i can identify the columns that i want actually in the html table um rather than doing that okay but that's i mean that's a quick easy way to do it and what did i do that in like five minutes are you kidding 10 minutes that's awesome i love that that's awesome yeah so next week or next month right so what i'm gonna do is um i'll show you a little bit more like once you've got that list how do you apply like some css formatting to it um dress it up a little bit things like that as far as the resources that i promised um so here's the blog article my my blog is flyingpolymath.com and then you'll just look for the group by lookup um article there's another one that where it actually counts or doesn't count i do have count the records that's going to be on the collab 365 thing tomorrow where i do a count of the records but i've got another article where you group by um you know whatever item and then it does a sum of however many things there are so i've got one field that says like apple and then like an inventory thing so that way you can sum up your inventory using power automate um so that's the blog this is the event tomorrow morning at six is my show and then uh here's my linkedin which is slash in slash ed gonzales uh with an s at the end if you want to reach out to me there and then you can also follow me on twitter at powered by edgy any questions can you drop those in the chat yeah uh i will drop all of those links in the chat and then also i'm gonna plug my uh newest podcast which is insert home where we talk with folks about their journey into tech so i'm gonna drop all of those in the chat for everyone's enjoyment good smash that subscribe button people smash it smash it i've got it on like six different platforms there's no reason to not be listening to it i keep the episodes 15 to 20 minutes so it's super quick you can listen to it while you eat a pint of ice cream or whatever i've been doing a lot of that ed how'd you know oh man i love ice cream just moved to oregon tillamook is my favorite brand by the way their mint and chip is awesome okay so i'm done sharing um i'm looking uh be available on all tenants okay so uh looking at jamie's question so i'm presuming jamie that you've already enabled the the the secret sauce right the the um experimental features probably yeah i probably forgot i did it but yeah that's why i've been seeing it in some of my places and not others so it's only experimental right now yeah so that's where it's at let me let me reshare my screen real quick just to address that guy super quick so some of you may have noticed that my expression editor was a little bit different than most people actually that's a terrible example um let's go down in the compose that's i mean that's it's experimental right so that's why it looks one way here and then i come down here and it looks a totally different way i like this way because i can do multi-lines and things like that and the way you turn that on is uh you click on the little gear icon in the top right you go to view all power automate settings and then you turn on experimental features to answer jamie's question i don't know when that's going to be available as ga in all tenants um you know if anybody else does know you're happy i'd be happy if you put that in there um but let me make sure are there any other ones let's block everyone's convenience okay we'll do that um yeah about bastion pretty much an inner join and that's that's kind of exactly what we did um okay and i don't see any other questions if there's more i'll be here for another 10 or 15 minutes

Channel: Skypoint

Views: 11,240

Keywords: power platform, power automate

Id: 1AJ7Ul4I6uc

Length: 17min 44sec (1064 seconds)

Published: Mon Mar 08 2021