5 Fancy Functions in Power Apps

today let's talk about five fancy power apps  functions all right there's nothing really   fancy about them but the idea is these are five  functions I think you all need to know but you   don't tend to watch things if I'm like Hey we're  going to do a video about beep you're like but   so what we're going to do is I'm going to go over  these five functions that I love so much I think   you need to know I'm going to show you quickly  how to use each one give you some examples of   where they might integrate into your stuff s fun  let's just switch over to my desktop and take a   look all right so first off let's start with one  that I think almost none of you ever use and that   is one called Coalesce right so what Coalesce  does let's insert ourselves a label real quick   is it Returns the first nonblank value okay so  what that means like if I do this I say Coalesce   and then I did doom doom nothing and then I said  hi it's going to return high that first parameter   there is completely blank and so it Returns the  first one that's not blank right so to make a   little make a little more sense like if we throw a  text input over here so if you go here and change   this to be text input one. text okay so right  now it has text input in there so it shows up   and we'll change Hi to be Na and we know we'll  insert another text inputs right just so you can   kind of see this thing it's full Glory so now  I got text input one text input two and so then   we'll go right here because it can take as many  parameters as you want so we'll say text input   2. text and then na okay as long as they're all  the same type of data so right now it is showing   you the one from text input one because that is  the first one there so if we say play if we blank   that out now it's showing text input two because  that's what's down here get that out there's na   right if we go here and we just put a one you  see one we put a two that doesn't change it   because the first non-blank thing was text input  one but if we delete the one we get the two and   then of course if we delete the two we go back  to na right so Coalesce is a great way for you   to get the first non-blank value now this could  you know this is very simple this is just using   a control but you could be doing a data source  lookup or other fancy stuff right so speaking   of fancy stuff so one of the my favorite ways  to use this is let's go ahead add a data source   we're going to add my SharePoint employees list  so I'll kind of edit this out real quick hang on   okay so now we have a data source this could  be SharePoint, dataverse, SQL doesn't matter   but if we throw over here and insert ourselves a  button how often do you have code that might look   something like this so what we're doing here right  we're saying hey do a look up go look up in our   employees list to see if the first name equals  Nicola one if that is blank then do this patch   if that is not blank right so then do this patch  we're going to do this look up again right you see   the redundancy here you're doing two of the same  exact lookup and you've got a lot of code written   here okay but what you can do with something like  this once you understand Coalesce is what if we   did this copy paste the button What If instead of  all this I'm going to take the lookout out so I'm   gonna copy that copy and now we're gonna get rid  of all the whole if and we're going to get rid of   the whole second patch and we're going to say hey  patch employees and then here we're going to say Coalesce and then we're going to put in our patch  or sorry our lookup and then we're going to put   in our defaults and then we'll close our cute  little thing like that okay so now what does   it say it says Hey patch employees Coalesce so  find the first non-blank value so do this lookup   if this lookup is blank then don't patch it right  because you can't patch blank so then do default   but if it finds Nicola right so if we took off  the one here and did that then it would patch   that just fine right so this is an interesting  way to think about this right anytime you have   any type of if statement where you're checking to  see if something's blank but you're going to reuse   that blank thing a lot of times you can adapt it  and use Coalesce right so once you get comfortable   with this function this is much cleaner as long  as you understand how it works this is less typing   less chance for typos less repetition I really  like using Coalesce in scenarios like this along   with scenarios like this where you might be saying  hey you know maybe you're doing a bunch of text   inputs and if they leave any of them blank you  want to automatically set it to na but you don't   want to put na in the Box that'll confuse the  user so you just say hey if they left that text   input blank then put an a here right so all of a  sudden here now do you understand how that works   maybe in here for the title you're pulling this  out and you're saying hey if they put something   in text input one make that's the title if they  put it in text input two make that's the title   if they didn't put either one do NA so once again  giving you this ability to make decisions and kind   of just write an if statement without having to  do it right so that's my first fun fancy function   course all right for the second one let's create  ourselves another new screen here so for this one   we're actually going to do two functions at once  so first off I want to throw a gallery in here   right Gallery just wants a table doesn't care how  it gets there we're going to change this Gallery   just to be a simple title so we're going to say  hey for your items I want you to be sequence   sequence is a function that you give it a number  so in this case we're give it 10 and it creates   a table right data type table with call a column  name value and then the numbers 1 through 10 right   so if we go in here like let's kind of update this  thing real quick pull this over some and we'll   set this to say value so this this is a great  way if you're going to get something from your   user maybe they're giving you an indeterminate  number and you need to create reservation slots   based on the number they gave you or you need to  cycle through right you need to count stuff and   then show them that right sequence is awesome  because it now gives us the power to generate   a table automatically to a set number right now  with this so you can do fancier so that's just   giving us one to 10 right if we scroll you can see  there's 10 here we'll change it to five so we can   always see them okay so that's five now you could  also maybe for your scenario you want to start at   a different number right so I can say start at  zero so 5 comma 0 now it does 0 1 2 3 4 right   that's five rows the first row being zero fun you  can also set the increments so if we change this   to 10 now it's 0 10 20 30 40 this gives you a lot  of flexibility for creating Dynamic tables of data   right now once you understand how sequence works  and remember these are just numbers so I could   be looking these up from a database I could be  uh doing getting them from a text input I could   be doing math right it doesn't matter how you get  to those numbers just put the numbers there that   you want right like if we come over here and just  say insert ourselves a text input right so this is   text input three so then now we can just go here  and say you know what for this step we want to do   text input three input 3. text now wrap that in a  value function right cuz you want it to turn the   text number into a number number we're about to  give it so we'll do something like that and now if   we go over here and hit play and we type in three  or four I type in four right there they are if we   change this from four to two now we got two we do  zero we got none one Etc right now to stop them   from typing a uh some text in there right we could  then just take this and say change your uh format   to number now they can only do that and then you  could even if you want to get fancy maybe you want   to don't want them to have zero you know you could  do like Coalesce or something just say hey if they   don't give us anything make it five it's up to you  okay but the other thing that you can do now that   you understand how sequence Works we're going  to do our third fancy function and that is one   of our data shaping functions called at columns  so in this scenario what I really want to do is   I want to get the next five Fridays right and so  today's Thursday so we know that that would be uh   um you know the next five right so we'll put five  over here let's go back go hit play okay so we   need five in there now this is just numbers how  do I change this table to be dates so you can't   change the sequence per se you can't say sequence  spit out dates but what we're going to do is we're   going to say I want you to take that I want you  to add columns so add columns what that will do   is take a table sequence put it out a table we're  going to get create a column called my date and   then here we're going to say you know what I want  you to be I want you to be today and then uh plus   value which is the number that got produced out  a sequence okay so now if we go down here and   say hey edit this insert ourselves another label  in here we'll say my date perfect right kind of   pull these something I don't know it doesn't  matter but look so now because today is the   third or no today's the fourth since today  is the fourth fourth right so uh 14 plus 0   is that boom uh 114 124 boom boom boom right we  probably want to change this to sevens and then   since today's Thursday we actually want to make  this start at one so there you go so there's the   next five Fridays right 15 112 119 12 126 and  22 Tada so once you start understanding that   hey sequence can produce numbers fancy function  and then addcolumn gives a way to shape the table   that came from that you can do some really cool  stuff right and addcolumn has a bunch of friends   it has renamecolumn dropcolumn showcolumn there's  a bunch of other data shaping ones but most often   I find myself just need to add a column because I  want to pre-calculate something I want to create   something like this but little bonus tip here  like if you didn't want the uh the value column   to come through you just only want the table to  have the single column right then we could just   go here and say show columns put our addcolumn  back because the new formula bar likes to hide   stuff and then we'd say just show my dates and  look now what errors because value is not there   if we look at the table it only has the my date  column okay so but we're not doing show columns   now so I'll stop that but I wanted you to kind of  see um the fun that we could have there oh and I   can't even hit delete all the way there we go okay  now if you want a bonus tip or a bonus challenge   right so I'm not going to show you how to do it  but what would be really fun is if you can go   and figure out like so I cheated right so today's  Thursday so I knew today plus one was Friday right   how could you figure out though what day of the  week today was so if today was Monday how could   you automatically have it show me all the Fridays  right I'll give you a hint there's a function out   there called weekday but I'd like you guys to  try to solve that yourselves and just leave in   the comment the formula it should be a pretty it  would be formula very similar to this but right   here we've got the one you're going to have some  additional logic right so put it out there um you   know I will definitely check in the comments and  I will be happy to thumbs up or heart the people   that get it right so that those of you that have  no idea how to do it you can see that cool thought   be a new little little twist here okay so that's  three fancy functions two more to go now before   we jump into the next fancy function I did want  to remind you about Power Platform University   that is a six month live immersive program with me  and a personal Mentor for you from our team it's   got exams it's got Hands-On projects it's got  all these different unique classes everything   that you need to be awate Power Platform right it  is the same training that we use to put our new   Consultants through so that they are awesome  for all the Consulting projects we do and so   we make that available so go over to training.  powerapps911.com and check out Power Platform   University so you can learn really cool stuff like  this and hang out with me all right back to our   fancy function all right the next one not the most  exciting function either and I probably don't use   it the way you think too often but I want you to  know was here either way right so insert ourselves   a button and so that is the notify function right  so notify function is the ability to say Hi Mom   in your power app right so now if we take that  and we hit play when I press this button we're   going to get a little popup that says hi Mom I  know super exciting right so this is a way for   you to notify your users without creating that  big old modal or popups that we all like to do   so what you can do now is you can go down here  and so if you understand that's how it works   right there's a couple little caveats so you have  text up to 500 characters and by default it'll go   away after uh 10 seconds but we can adjust all  that so the first thing we might want to adjust   is the notification type all this is really doing  is changing the color of the notification right   so error is uh red warning is yellow success is  green information is what it defaults it to which   was that white right so if you just change this to  be you know error now they're going to be red and   if you do a comma then you can set how long you  want to go away so this is in milliseconds so like   if I wanted this to go away after you know half a  second right we just do 500 milliseconds now if we   go back over here and hit play when we press the  button not only we going to see the red but after   a half a second it goes away right so you do have  the ability to control that stuff now honestly   most of the time if I want to notify my users I  am most likely creating a modal that whole pop   up you know where we do a container um I'll put a  video link up there if you haven't seen it before   but you put a whole container over the screen  big message formatted buttons all that stuff   right where notifications work are either little  notifications like you really are just trying to   give them a little snippet of like hey success and  you don't want to write all that interface still   don't do it a lot but you could where I will find  myself sometimes using this though is I use this   a lot of times in troubleshooting right because  sometimes when I'm troubleshooting like long code   com uh long complex I don't know easy for me to  say a big chunk of code I'm trying to do that   and in the middle there I want to make sure that  like a variable is getting set or something the   loop is happening I will put notifies in there as  a troubleshooting step so I can see on the screen   what's going on right the same way I like to put  labels on the screen to show things but sometimes   because of all the moving Parts a label doesn't  work as well some scenarios for me so notify for   me is mostly a troubleshooting tool you don't have  to use it that way you can use it any way you want   but I just want to kind of throw it out there that  I think a notify is a way for Shane to get a pop   up when he's trying to figure out what he broke um  more than the users I like to build the whole big   model last but not least in our fancy functions  is going to be the one that is most dear near to   my heart it is the With function right so let's  make ourselves new screen and so for the width   function what I want to do is we're going to build  a little quick demo right so we're going to insert   ourselves date picker and then we're going to  insert ourselves another date picker and then   now I'm going to rename this I'm going to DPstart  and then DPend like so so I've got those controls   renamed and then I'm going to write a formula in  a label so hang on one sec okay I didn't think you   needed to see me write that whole formula but  there it is and so what this formula does is   it spits out the number of weeks and days between  two dates right because datediff doesn't do weeks   and days it can do dates months weeks or years  and all that but it doesn't do weeks and days   like this so I a formula for it so now if we hit  play and I change this to be like the 29th and   say okay there is 3 weeks and four days between  those two dates awesome so but if you look at my   formula here it's very repetitive so the way that  that works right is I do a datediff between the   two and then I use this mod function of not  datediff to get the seven that's kind of the   remainder subtract it out so the seven subtracts  cleanly and then I do a mod here again date diff   again so I've got three date diffs two mods very  repetitive now you're thinking Shane I can do it   more efficiently that's great this is a teaching  example don't don't over complicate this okay but   what the With function does is it allows me  to generate that stuff and reduce redundancy   right so if we go up here we're going to just  type in with and then we're going to put it in   curly brackets and then we're going to say um X  total days like so and so what we want to do here   for X total days is we're then going to grab this  datediff I don't want to rewrite it I'll screw it   up you know that we'll do that we'll paste that in  we'll close our curly and then we will do a comma   so now everywhere after this comma until we get  to the closing parentheses which we're going to   put at the end that is available so now there is  a think of X total days as a variable that only   exists between that parentheses right there and  that parentheses right there okay but now what I   can do is I can take this I could copy and I can  go in here and basically say hey everywhere that   that I had datediff now we can put put that in  right so there's that one there's that one and   finally there's that one okay so look now we  got X total days minus mod total Days Seven   blah blah blah blah blah right so we've still got  the Redundant mods but we're a little bit closer   so what's really cool with With though is you can  Nest With inside of With so watch this we're going   to copy this whole mod x total days thing copy and  then we're just going to we'll hit enter here to   make this easier for us to read we're going to do  another With inside here and we're going to say x leftovers leftover days whatever right we'll paste  that in we'll close our curly we'll do our comma   and then we'll go down here and now right it's  happy but now we got this so we can copy this   out and then we'll do here everywhere we have mod  we'll replace that with this and now look now you   can see it's really just total days minus leftover  days so that's how we got down to 21 right because   I think there's 25 total so 21 divided by 7 that  gave us the three weeks and then leftover days   is four and so four days between the two dates so  notice here this reads easier and instead of doing   five things we did two things right because we did  the one thing three times the one thing twice but   we're able to use with three repeat that and by  nesting the one width inside the other I have the   ability here to reference the output of this  first one in the second one right you keep in   mind you could in with you could have two things  like we could just be like you know X um message   high right so you could have two or three or 10  of those inside there if you really wanted to   but if you want to reference the one from the  other then what you're going to have to do is   you're going to have to um put those inside here  right because this x total days exists after this   comma through that uh close quote or this close  quote for closed parentheses right and then X   leftover days exists after this comma through that  parentheses right there so that's what you want to   be kind of thinking about right is like the way  that these guys all correlate like that so there   you go five very fancy functions that I think your  power apps would be better you'd have an easier   time writing and doing things if you understood  those so hopefully by using the word fancy I got   you all to watch this but what are your favorite  functions right if I was going to do another video   like this what functions would you like to see  leave them in the comments below happy to do   that take these on maybe there's controls maybe we  should do five fancy controls oh that could be a   good video I don't know you tell me okay and with  that I'm going to say thanks and have a great day

Channel: Shane Young

Views: 7,906

Keywords: Shane Young, powerapps911, PowerApps, Power Apps, power fx power apps, powerapps functions, powerapps coalesce, powerapps sequence, powerapps sequence function, powerapps addcolumns, powerapps notify function, powerapps notify, powerapps with function, power apps canvas, power apps beginner, power apps microsoft, powerapps tutorial, powerapps tutorial for beginners, power fx powerapps, powerapps functions cheat sheet

Id: tRIVjDASxMY

Length: 19min 54sec (1194 seconds)

Published: Mon Jan 29 2024