How to Get Started with Microsoft Power Automate Desktop

automate any application or do Advanced web scripting with Microsoft power automate desktop this course contains 10 lessons about how to use the RPA tool from Microsoft if things goes too fast just rewind or pause the video it's designed for that I expect you to have Microsoft power automate desktop installed if you haven't done that simply just follow the guide up here and come back to this video Now open up our automate desktop the first lesson is about variables here you will have the variables that we'll use the most the first one that is text that could be any kind of text from email addresses to the content of a word file so for example let me move to my desktop here I have an empty folder called project this is where my entire project later on will go in Excel files documents everything let's grab the path of this so I say shift right click then I can find this copious path here I need to press shift in while I right click with the mouse otherwise this will not get shown I click here and I go back here now I create a variable that will store this project path so I'll find a set variable like this and drag it in here these blue things those are just comments I made to help you so you can actually see how we differentiate the different kinds of variables first off we need to give the variable a name the default name is new VAR and look what happened when I press here you can see that we have percentage signs in the start and in the end that's the sign of a variable so whenever we want to refer to this variable we not only need to type in the name but we also need a percentage sign in the start and in the end so new VAR that is not really describing for what we're doing so we always want to rename our variables so it describes what we want to do with them for example this one will be project path and now I didn't delete the percentage sign and wrote a project path and when I click away they disappear but when I click back you can see them again power automate desktop automatically made these percent assigned because let me delete everything and I just say project path again go back and these will automatically be made so now I can refer to this variable by its name I can use it later on in flows so if I go down here to Value press Ctrl V delete the two quotation marks like this now I can refer to this project folder in my entire process that is very clever and is the best practice that we often use because this path can change I can move the project folder and then I just need to change the value of this variable for once imagine that I will use it like five times during the flow I can only I can just use it here and then just refer to this variable name so that is very easy to understand this is also the most common ones we'll use text a lot then we'll have a numerical those are numbers and when we want to do mathematically operations then we want to use a number a number looks like this we will initialize it with another set variable so if I drag this one in here let's say that this should symbolize the vat so if I say I can say that like this and again click back you can see we automatically get these percentage signs so you don't need to write them then we'll give it a value in Denmark it's 0.25 and then I can click save so now we have a vat let's do a calculation and here I want to introduce a an action called display message if I drag this one in here then there's a lot of parameters to fill in I will just fill in the message to display a display message action is exactly what it is it will display a message to the user and the robot will Porsche until the user has clicked okay let me show you so for example here we can say the price of the product is 122 plus that so the vat will just be a text here because I haven't used these percentage signs in the start and in the end so those one will just be letters let me show you so when I click save here I can now click run you'll see that here it says the price of the product is 122 plus that but we actually wanted to do a calculation we want to add these 25 percent to it and we can do this so when I go inside the display message I can double click to edit it and now if I want to do a calculation then I need to have percent in science in the start in the end also around the numbers so it will look like this so it says 122 plus that but when we have the vat was 0.25 so we just need to say and let me just say apple entities then I want to say 1 plus that and then the parentheses end because then this will be 1.25 and then I want to instead of the addition I will have a multiply 122 we'll find the total price including that it's worth to mention that I only need percent signs in the standard in the end when I'm calculating something with a variable I'm using the variable here and you can see we don't have percent assigns here and here but we have them here and here so only in the start and the end on the entire expression so now I can click save and we can try to run the robot so here you can see now the price of the product is 152.5 that is we now made the entire calculation I can click ok then let's move on so because we don't want it this display message to show up in during the rest of these this exercise I will right click here and then you can see disable action it's it gets grayed out you won't see it so we don't have this a little bit annoyed annoying a pop-up in front when the robot runs now let's move to a Boolean a Boolean can only hold two values it can either hold true or false that's it we use it a lot when we want to do checks let me show you again we will initialize a Boolean variable so I'll find a set variable then I drag it down here let's say that I want to find out whether or not this is a rainy day so this is a fact I will say amount of rain this could come from a website or anywhere else where we have weather forecasts so the amount of rain let's say it's five millimeters today I'm not sure if that is a lot I don't think that's a whole lot but it's still rain so now we set it and I can initialize another variable called rainy day so this one will be called rainy day and this will have the value false so this is a Boolean I'm introducing here so I'll say true or false you'll see it in a second now if I run this these will just be said and nothing will really happen but we can actually and do something about it let's say I want to change the the rainy day value down here so I move in here and let's say I want to make it dependent of the amount of rain so here I'll say if it is a rainy day that is if this one up here is above 15. so here I would say amount of rain and again I could write these things out but a great way to refer to these things up here is to click this x here and then I'll say amount of rain now it comes in automatically I don't risk of typing wrong things in so now I have the amount of rain again when we make a an expression we just need to have a quotation sorry percent assigns in the start and in the end so I'm moving here and I Can Make spaces in here that that will just be ignored it will be a little bit easier for you to look at but then I will say if the amount of rain is bigger than 15 we said and so this what happens here is this will evaluate on this value so if this amount of rain is larger than 15 then this will be true otherwise it will be false let me just do this let me run the robot again nothing really happens but we can see it over here if we want to inspect our variables so this is a nice place to be you might not see it and it will look like this you just have to click this x up here now I can inspect the variables and their values for the last run of the robot if I click in here you can see we have a Boolean value called rainy day and it's false and that makes a lot of sense because we we said that we want to evaluate if the amount of rain was larger than 15 then we wanted it to be true but now it's only five so it's definitely not true but we can try to change the value up here so I'll say 20. now run the robot again and now you'll see the rainy day Boolean variable I can double click it over here is now true so these are checks that we want to do often so booleans cannot be ignored and just remember they can only be true or false so they are quite easy to understand now let's move on a date date time in power automate for desktop comes in a standard format and that is month forward slash days forward slash years hours minutes seconds let me show you so if I find a get current date and time like this and I drag it in here so here I want to say I want to get the current date and time and I want to Output it into the Q and date and time variable so we haven't talked about variables produced but each of these flow um actions over here to the left they almost all of them produce a variable this one will just give us the Q and date and time so when I click save you can see our action down here and if I've run it you'll see over here to the right if I double click here you can see that this is the current date and time that is the 5th of October 2022 one 10 minutes past one and we can even have um some seconds on so this is the default but we can actually what what lies behind here we can also get milliseconds and everything on we can do a lot of conversions so if I just do this and we will use date and time a lot imagine that we want to create a log or we want to dynamically name files so they get a timestamp on whenever we receive them or whenever they get created we will use a date time but we might not use won't use let me again open it this format because this looks weird and we cannot use all these colons and forward slashes in when we want to do timestamps on a file so we want to convert it and what we can do here is that we can go up to actions and then find a convert date time to text and drag this in here so here you'll say date time to convert I want to choose the current date and time so I click this little X here then I take q and date and time the format to use well we can use standard and here you can pick between the other formats so for example I can pick long time this only give gives me the time and it will say save it to a variable called formatted date and time I can click save let's try to run the flow again and again the values only exist for in the memory of the robot we haven't printed anything out and that's really that's not really the point of this lesson but let me move inside first of all we have the Q and date and time this one the current time this will looks exactly like before except that it's now a little bit later and the format a date time if I double click here now you can see that this is a text value and we only have the time here similarly if we wanted other types of output if we wanted to use this time date time we can we can use in the convert date time to text we can just pick another one from for example short date we can pick a full date time that looks like the with the date in text format like Tuesday or we can actually do custom say I want to use it in a file format I'll often go with this the year first then the month the days the hours the minutes and the seconds so this will be a nice timestamp because this will be unique down to the second you can also get millisecond but that's not really the point here let me just click cancel and just stay with long time we will not use that anymore so lists and list a list is a collection of items that is for example a list of text values a list of numeric values booleans Etc let's create a list of text values that could be animals we can um we can set a list in a lot of ways we can either do it manually with a set variable or we can actually if I go up to here to variables we can see that we have a lot of um list actions here we can create a new list and then we can add items to it usually we will not go with these actions and we will just create them with a set variable that is easy easier and more flexible with this add item to list I can only add one item to the list at once but I often want to add like five items 10 items at once I don't really want to drag five of them in so it's very very easy so I'll find a set variable here and now we will make a list let's just call this list animals and then to initialize a list variable that will I'll need to have percentage signs in the start and in the end and then I just need to have a hard bracket in the start and in the end and then my list items will go inside these hard brackets and these needs to be comma separated for example I want a cat and these ones text values needs to be in single quotation marks so that will look like this if I didn't have these single quotation marks on a power automate for desktop will think that this cat would be a a variable because it is inside these percentage sign and then we'll have an arrow because we haven't defined the cad variable so remember these single quotation marks then just list let's just pick it dark comma separated bird like this so This Is How We initialize the list and you can see I have spaces in here it doesn't matter these will just be ignored but it might be easier for you to read when you watch this video if I click save here I now have a list let me just run it again we have a lot of things that goes on during run time and then not do anything else but that's the point of this lesson we want to see how we can work with variables if I go over to animals and double click this you can see that this actually got initialized as a list of text values we have three items we have cab we have dark and bird each one of them they will have an index number and in programming most of the things are zero indexed so that means that the cat is placed at index 0 and 1 and 2. you just get to to get just here to have your head used to it that the first item is not index one but index 0 and then you'll be fine you'll use it a lot so better remember it so the first item in the collection in programming is zero so let's say that we want to refer to the dark in this list that will be index one so and let's just have another display message here and drag it in so um if I just drag this this display messaging I can in here I can say animals so the first one thing I want to do is to refer to the animals and then I wanted the dark which was at index one so I just go say hard brackets and then I will have the one I can click save so now I will run it again and here you can see I got a dog out and I clicked OK similarly if I move in here and I change this one to two we will have the pert printed out like this so here we have the bird so now let's just try to iterate through each one of the values because that is often what we want to do we have a list and we want to process each item in that list we'll do that with it for each and we'll come to whatever each is so for now it's just an iteration of a collection so here I'll say what value do I need to iterate click this Alexia and say animals we will store it into q and item this is just a reference value so whenever I refer to the Q and item that is we run this Loop over and over as long as we have items in the list and wherever we are that could be we are at the cat that would be the Q a item doc then will be the Q and item and then the bird so and I often want to rename this to make it a better reference so I can say what's going on and this will actually be a single animal so the list that is animals and each of these items that is an animal so I can click save now let me drag this a display message up here and instead of animals too I'll just refer to the single animal so I double click this and here I can just delete here and then I'll refer to the animal I'll click save let me try to run it again so here you can see I have my cat I have my dog and I have my bird that's it that's how easy it is to use list variables so again I'll just right click this display message and just disable the action so we don't have this pop-up going when we move on so the next thing we want that is a data table I haven't made a description for data table here because this data table variable is very very important and I want to play codes want you to play codes attention because we will use it a lot so let's make the comment together and it will look like the other blue ones but I just want you to pay very close attention sorry I was uh terrible word to say as a non-english being so we have a data table and a data table looks and acts like an Excel sheet except it only exists during run time and when I say run time that is when the robot runs so whenever we start the robot and the data table initialized till the robot has has ran that is where a data table exists a data table has several key advantages it's very fast to work in faster than an Excel sheet so that's why we want to use it so it only exists during runtime and again it's zero index it's zero indexed and again if you want to refer to the first row in the date table that will be index 0 otherwise no difference and a dates table is actually a list of data rows so and let me just tell you what's going on here so when we say it it looks and acts like an Excel see that is we have columns and we have rows we usually just read our Excel sheet into data tables we look at exact same things except that they only exist when the robot runs but we'll do that so we can easily do gymnastics with the data so now that we have the definition in place we can create a data table so go up here we can do it in several ways and here we can also use a set variable but I actually like the create new data table action so drag it in here so here I can say how many rows and columns do I want well I want three rows and two columns we already have two columns and let us just say we want to call this so I right click here I can either delete clear or select and when I double click I can rename it so I want to call this name and this one I will want to call it h and I can just start by giving it values here so say that I want this to be Abraham and Abraham is 20 years old I now want another row so I click this little plus here and I actually want two more rows so let's just create two of them then I want Becky Becky is 41 years old and finally I have Carlo Carlo is 65 years old so this is my data table with data and of course I could have read this from Excel sheet now I just created manually so then I can click save here you can see we produce a variable called Data tail again let's giving let's give it a more describing name so I'll call this employees and then click save so now we created a data table called employees again we can refer to I to any of the elements in here and the way we refer to data tables here we have three three rows and two columns that is that we refer to the data table name then we say what row we want to to look in and then we will say what column we want to look in remember again it's zero indexed so for example if we wanted to get Carlos age out or the H that stands here then we will have to say employees and then we'll say 2 2.1 that is the coordinates of this one here let me show you how that looks so I just click save Here and Now find a display message here and just write it out so here I want to look in the employees data table I'll find it by clicking this little X here say employees and go in here so here I'll say we wanted to have the second row that is the third row but it's indexed second two and then I want the second column that is index one I'll do this so this is what I'm saying here look in the employees data table and then take the second row First Column let me get the content of that cell that was 65 if I remember correctly now if I run the robot again you'll see that we get 65 out that was our mission mission complete similarly we could also iterate through a data table that is in fact it could also be an Excel sheet that we read into a data table so again I'll find a for each let me drag it in after the create new data table so the value to iterate that will be employees and here again I have this little reference variable I will just call this employee and then click save so now I can refer to this employee let us just use this display message again so I'll drag it up here and we just need to fix the expression because now I want to refer to employee that is the first one so if I just do employee like this and I could actually delete it go find it over here so I don't misspell and then I can click save so when I run this you'll see that we are saying we get the whole data rollout and that comes back to what we said in the beginning a data table is a list of data rows so this is one data row apron 28 the next one comes here and the third one comes here so but what we want let's say we just want the their age out so I can go in here and then I can say employee and then I need to refer to the column header or index so for example if I just do hard brackets and I can say h and do like this and I can click save let's run the robot again and they will come out here so that is the first H the second one and the third one it's that easy so that was data table the final thing that is custom objects a custom object that is key value pairs that is we have a key and then we have a value to that think currencies um if we want the key that will be USD for example that would be US dollars but the key is USD and then the rate that will be in Danish Chronos that'll be 750 maybe so that would be the value and that has the advantage that we can look things up in a custom object I can say give me the USD I will say give me the the value of the key USD and then power automate for desktop will give me the value back it looks like this and it's also had the advantage that it's easy to convert to a Json if you know that that's very Advanced we will not charge it here so I'll find a set variable like this and then I'll drag it in so we're down here in custom object I'll call this qnc so then we'll give it a value so this is the name currency and again percentage sign in the start and in the end this time to create a custom object I need curly brackets so I'll do this and then I'll have it in here I'll again I'll need this to be key value Pairs and USD that will go in in single quotation marks then a colon and then its rate so 761 times 63 let's give it that that was our our first key value pair of Q and C then I can have a comma separate I can create as many key value pairs as I want let's just create like three so I'll say Euro colon and then I'll have the euro rate so the 700 and again these will not be the correct rate but something similar and then I will have the Indian rupees and actually if you're wondering what base currency it is it is Danish gross I'm from Denmark so um then I'll have this colon and again I'll have the currency rate here that will be 9.31 and like this so now I created this custom object I can click save it's down here and again let me just disable and I will actually disable this for each so we don't have the iteration and I'll do it up here as well so we only focus on the custom object now we created it when we run it you'll see that it exists during runtime we can go over here find the currency you can see we have this custom object and we have the three key value pairs but say that we want to find whatever the US dollar rate is so let's have another display message here so when I want to do a lookup in a custom object it's very simple I just go down here message to display obviously currency then I just need to give the key and that will be in hard brackets like this single quotation marks I want to find the accuracy rate of US dollar I click save and I can click run so that the one is 761.63 we can see that is right so then I can click ok so now we got that one but let's say we want to do a calculation let's finish here so um here I want to say Let me give this a message so I want to say one US dollar because this was 100 um 100 sorry 100 US dollars cost 700 100 Danish Kronos cost 600 uh 100 US dollars cost 661 uh Danish Kronos so let's find out how much one US dollar cost you can probably do it very easily but let's see how we can do a calculation and I'll just say one US dollar divided this expression by 100 I can say one US dollar is and do like this so let's just start the robot again here you go one US dollars is 7.6163 Danish krones we could of course write that as well we just move outside the expression and put in DKK and then click run that's it I forgot to introduce myself a full-time IPA teacher you're more than welcome to follow or connect with me on LinkedIn the next lesson is about the recorder in power automate desktop key to boost your power automate learnings you'll find a recorder by just clicking the recorder up here and say that we want to find out how my company anasian.org is doing based on Google reviews so what we want to do first is to open up a browser and we'll do it by clicking these three dots here launch new web browser and then I'll take Chrome that will open up a Chrome browser and you can see here that we open up a blank page so this is the first step then I can say record and start recording my actions but be aware it will record every step with your mouse and keyboard for example if I just do this you can see that it moves the window to a new position if I make some actions that don't really make sense I can either reset it that will reset the entire flow or simply click this little delete icon here that will delete the section so now I want to navigate to Google so I'll just say google.com and press enter how automate desktop automatically says well we are launching the new Chrome and then we'll navigate to this in one actions so it compressed my say three or four actions into one that was quite clever now I want to do a search and that is in this search box you can see it turns red so when I go down here and then make a search of anaciensen org you'll see that this is the recording this is the reviews page these are the data that we want to scrape over here we want to get how many stars the company has and how many reviews we only have two but it looks good so far and if you want to review the company you're very welcome so um here you can see we have we actually have two steps we have a populate text field that was the anaciensen org we can see it here and then the press form that was probably the enter click so now we need to get the data let's start with the average rating which at this time is 5.0 so what I'll do is to navigate over here to this span so only the red border should only surround the reviews then I'll right click extract element value and take the text you can see the text right now is 5.0 but it will work no matter what the average rating is so now you can see we stored this we also need to store it to to store the sample size right now it's two so it's really small but nevertheless move over here here we cannot get the two out or isolated so we need to say two Google reviews let me get it again right click extract element value the text to Google reviews and drag that one out here you can see we got it in in a variable called output data that is a data table I'll show you in a little now we have what we need and if I click done here you can see we have some order generated actions that is launching a new Chrome navigate to Google do a search for understand snork click enter that was this one and extract the data let us just close down this page and let's run the automation to see that it works so we are launching Chrome we're doing the Google search and we extract into data right now we will only extract it to a data table but let me show you how it looks so if I go over here into output data and I double click this you can see we actually have the data web script here we have a data table of two columns and one row if I wanted to get the the average rating out then I'll need to refer to this output data and then I'll say I want a 0.0 a data table is zero indexed that means that the first row you can even see it here is named Zero the First Column is also indexed 0 so that'll be 0.0 and two Google reviews if I want to to say how big is the sample size it's two here then I want to say 0.1 let's combine these auto-generated recorder steps with some manual actions so we can present the data again we will have a display message so find a display message and drag it in message to display well I want to say based on and now I'll say how many reviews do we have we have two right now but it could change when you watch the video click DX here and take the output data and again let us just repeat what we wanted to do we wanted to get how many reviews we have and that was we only have one row so that was the zero indexed row so zero point and then it was the second column so 0.1 we'll do that by saying hard brackets zero and then another hard bracket start one hard bracket end so this one will give us the um the value of the data table called output data and whatever we have in there the first row the second column is zero indexed again so then we'll say based on two three Google views whatever that is the average stars is and then we just want to say we want the 0.0 because that's where we have the Stars so shouldn't we just so we are lazy we want to automate things and not make errors so we just copy this one Ctrl V paste it in and simply just change this one to a zero so now we can present the data and let me just close the browser again we can make our robot do that but let's just stick here in this recorder lesson so this shows you the power of the recorder you will automatic you have automatically created these steps and now we're combining it with manual steps it's great for learning purposes you might not use the recorder in say two months from now but in the beginning go use it learn as much as you can from it because you'll learn these actions very well because they are also generated and here you can see we have based on two Google reviews the average stars is 5.0 that's right I can click ok the robot finished let's also say that we want to use a variable in this auto-generated flow so instead of an essentials.org I might want to change this based on some input in another system so here I'll find a set variable here and I'll drag this one in so here the variable I will give it a name so I can refer to it I will call it page and let's say I just want to search for Noma it's a restaurant here in Copenhagen I'll give it a value here I can click save now I want to use this variable inside where I do the Google search so if I double click here I delete this and I see instant org click this x here and find the page so now I do a search based on whatever this value holds imagine that we can change this based on previous actions right now it's hard coding as I say as a hard coded in as I said variable we could also have a list so we want to get the reviews of say like 10 pages then we can store it in a list and iterate through it with a for each now it's just a one so but if I click here you'll now see that I search for Numa and then in a little while I will say based on 1646 Google videos the average stars is 5 4.6 so that's it did you like the recorder now we are moving further into Power automate desktop and look at some Advanced logic so let's create a variable with a value then evaluate on that value with an if and a switch and let our flow take different paths so go up in actions and then find a set variable and drag it in we want to evaluate whether or not it is a rainy day and imagine this variable the value of it comes from another system for example a forecast service so here I'll say expected amount of rain and then let's give it the value 5. before we warned it to call it a rainy day we need to have more than 15 of expected amount of rain you can see it as millimeters so then I can click save let's create that condition with an if so it looks like this I'll find an if and if all Waze asks a question that can be answered true or false so let's evaluate on this expected amount of rain variable so I click this little X here expect an amount of rain and double click I want to say if this is greater than or equal to 15 then I want to do something so this will be the true otherwise it will be false her default everything that comes in between the if and end that will be if this expression is true a little bit later we'll make the other branch that is if this is false so if this is true that means it is a rainy day so let's find hey display message and drag it in here inside div here I will just say it's a rainy day like this I can click save before I run it can you tell me whether or not this display message will get shown I'll click here and no it will not get shown because this expression says we only want to run whatever's in here if this value expected amount of rain is larger than or equal to 15 and it's really not but we also want to tell not right now it says it's a rainy day but we also want to tell if it's not a rainy day and we can do that very easy so find an else and dragon else in below or down here so now we created these two branches in our if so if this is true everything from the if and down to the else will get performed and if it's not true everything from else and end will get performed let's just do another display message and we're lazy as automating developers so mark this one with a single Mouse click Ctrl C move your mouse down to and click here once Ctrl V that will paste in the action the correct place now I can open it and here I'll just say it's not a rainy day and I can click save so let's try to run it and here you'll say it is a it is not a rainy day that's what we wanted and we can click a okay now let's change it up here to maybe 15 because now it will be a rainy day so now when I run this it will say it is a rainy day I click OK that's it so now we created an if that asks a question and it will take two different directions so it will take the either a true Direction everything up here or false Direction down here and when these actions are performed it will jump to the end and then take the actions that will come down below right now we will not have any further actions but what if we wanted to have three outcomes with an if we can only have two either true or false then we can introduce a switch so and let me just um click this little if we up here press shift in and click the end right click and disable actions now these ones here will get ignored not the set variable so and a switch can be seen as an if on steroids and the way we introduce it here is that we'll say switch and drag it in here so the value to check that will still be the expected amount of rain like this so here you can see we have the switch and then we have the end now we need to Define so nothing will really happen right now we need to Define some cases and the case look like this so up here in actions I'll find a case and drag it inside the switch so the first case is that if this expected amount of rain that's the base that we're evaluating on if that is equal to zero then we want to do something so case Zero we also want to have a case whenever the expected amount of rain is larger than or equal to 15. we are not doing anything yet but let's just create the cases first so here we want to say if the expected amount of rain is greater than or equal to 15 then we also want to do something we also want to do something whenever the expected amount of rain is between 0 and 15 that is our third case we couldn't create that with an if but we can do it now and now an important concept comes in but let us just first create the case so here I'll just say if this is greater than 0 then we want to do something and here you might say well we look at the expected of amount of grain then if it's zero fine we'll perform the actions that will come in here we'll see that in a little while but and then we'll go to the end but let's say that it's above zero then the actions will be formed here and this will actually a switch will perform the case that comes the first that means that say um this one here will actually be larger than zero then this one will not be performed because it will take this one first X says 16 then this one will be true it performs this and then it will not perform this so what we need to do is to move it up here then let's just have some display messages and then let's test our flow so with the with this equals to zero that means it's not a rainy day like this and with 15 we want to have it a rainy day so I'll say it's a rainy day very easy and then this zero we change the order of it because the they will be performed only one of them and so if it was like 16 then these both of these two could actually be seen as true but we want to to make it here that's why we have it first before the larger than zero so then it is a slightly rainy day that is if it's between 0 and 15 like this so now we have our three conditions and let's just test it from the start let's say zero so if it's zero then it will not be a rainy day that's fine we go into the correct case and we click OK it's done now let's change this to for example 10 then it will be a slightly rainy day let's see if that actually is true as well yes and now let's change it to 16 to see that we have the order of the cases correct in the correct sequence so I'll say 16. and now you'll see it will say it's a rainy day and finish that's it that's how easy it is to work with condition if and switches do you have any questions please let me know here in the comments the next lesson is about loops first of all we have a for each a for each each rates true say a list or data table that's the most common ones so let's create a list that we can iterate through with the for each I'll find the set variable and let's give our list a name I'll just call this any Mouse and then let's create a list to create a list you will have two percent signs in the start and in the end aren't brackets done in the end and then we will have the list items inside these hard brackets in single quotation mark put in your first element that will be dark and then I will say cat and let's have a third like this so three elements in our list that we can iterate through and I can click save Here Comes our for each so I'll drag in a for each what value do I want to iterate I want to iterate to the animals up here so I'll say animals I'll store them into a current item variable that means that now we have three so and we are looping through them one by one so that means whenever we are in the first occurrence we can refer to that item as q and item here it will be dark then the next iteration takes place the Q a item will now be cat bird and so forth if we have more elements so let's storm into an animal Parable instead and here you can see so the list is called animals and then each item when we Loop true with a for each that is called animal this is just a reference name you can call it whatever you want but best practice is to use a name with some meaning to the data in that way it will get later it'll get easier later on if you want to update your flow or if your colleague wants to so then I can click save now let's just print out the names so very very simple but I'll find a display message as we often use when we want to display messages so here I can just refer to this animal here so I click this little X say animal make sure you pick the animal and not the animals but then I can click save I click run dark cat bird that's how easy it is to use a forage now we want to use a loop so and let me click this for each up here hold shift and click the end right click disable action it's just because I find it annoying when we move to the next example to show these pop-ups so but you can find have him if you want so let's have a loop and a loop is doing something x times let me show you so let's drag in a loop and here you can see I actually accidentally dragged it in before the end I'll just have it here so I want to start from one I want to end at 5 with increments of one so this Loops run five times one two three four five very easy if you use a variable called Loop index and that is an index of what Loop we add so first it will be one two three four five very straightforward then I can click save again let's try to display a message here and here I'll just write out the loop index so click this little X here take the loop index and we could say this is run number like this space and then we'll have the loop index so now when I run the loop it says this is run number one two three four five that is a straightforward Loop often we are using its cousin called Loop condition so let me again Mark all these things and disable this so now I want to um do the loop condition in a loop condition evaluates on something and whenever this is true the loop will continue we'll often use it when we want to check for elements at the web page say that we want to post the flow um and then look for an element if that element is not present then we want to pause it further and maybe pause it for like uh 10 or 20 Loop iterations of maybe 10 delayed seconds so we can use it to check for something and then make it last a while let me show you so here I'll have a loop condition and I'll drag it in actually I dragged it in before the end it will go in after but let me show you we can move it afterward so the first operand now we need to to have an operand and since we don't really have it here we can say we can say if 2 equals to save and let me just move this end up here so this is essentially an infinite Loop this is always true so if I run the robot here you'll see that I have now created an infinite Loop it will run forever because this condition is always true so and this is not really best practice as you can see this run this robot will run forever but imagine that it takes some hard system accents right now it's just running it will not take any system resources but you can actually create a very bad robot that will run favor so always do your conditions so they can change or make a handbrake I'll show you both of the things first it's just instead of here we will have something to evaluate on so I'll find a display um input dialog here I'll take I'll ask the user for some input and then I'll save it as a variable I will drag it in in the loop condition here so here I want to say the title that could be capital and here I'll make a little quiz what's the capital of Denmark and then the user will give me an input so that will be stored here in user input let us call that capital for easier reference then I can click save now I only want this Loop to run until the user has that the capital value is code making because that's the correct answer so if I go up here in Loop condition and then say the first operand let us just say Capital if that is equal to Copenhagen like this then it will run so right now we just need to change the operator to say not equal to so now it will run as long as this Capital variable is not coping let me show you so and let me just drag it in over here so what's the capital of Denmark I'll say London it will run much more let me just do one more fail so room come in here and let's just pick coping is now finished so it finished because this one was not true anymore so the capital is not Copenhagen it's not equal to Copenhagen then this will run but now it became coping so this became true and it stopped so Loop will evaluate on something when it goes in run the actions inside it evaluate again and continue but what is what if if our user was very didn't know the capital of Denmark then we created an infinite Loop that could essentially run on limited times I know that the user will probably get bored after five attempts and then just leave the computer shut the flow down but imagine that this was some unattended robot that checks for something then we wanted to to have some sort of a break in the loop condition and this is very important and for this I need some sort of account that says only run this Loop x times so I'll find a set variable here and then I'll drag it in here so here this one I will call this Loop index and it will start at one I only want to run it 10 times so I also need to each time a loop has run I also need to add one to this so pick a set variable in the end of the loop and this is actually something that you'll use a lot so please pay close attention so the value like this x here I'll add one to the loop index whenever we reach the end of the loop so that will be plus one here I don't want to make it 10 because then I need to make 10 guesses so let's just make it 5 to make it easier for ourselves but you can of course change it I'll be in the loop condition so right now we can only do you normally do one condition but we can actually make more conditions inside here we just need to make it a little bit different so what we can do up here is that we can say if the capital is not equal to and then it's coping like this and now you just need to have it in a single quotation marks because the capital is a variable Copenhagen is not that's just text so that's why and then we also need so we know we need to have an end and then we can say if this is Loop index we only want if the loop index is is um is less than or equal to 5 then we want it to run when the loop index 6 that means that the loop had ran five times we don't want to run it anymore so that's it's just um below six and we can write it like this so if the loop index is less than 6 then so we only want this to run whenever the capital is not equal to government and loop index is six so this is an expression and now we will change it to equal to true so we will only run run this Loop condition as long as this expression appears true that is if the capital is not correct guessed and the loop index is below six little C so now we can run it and I'll just show you here and I need to drag it in here so now the loop index is one you can see it over here let us just press a now the loop index is two I'll just press B not correct answers so C and that's why I didn't want to make it a d and now to be the last one because the loop index is five this is still true but next time it would be six this will not be true so we will stop our loop I don't know where when that so that will be e there you go we have now made that backdoor this is a very important concept so if you didn't know what if you didn't understand what's what went on here with the set two set variables and the condition please rewind the video and do it Excel is the most used business application across companies and here's how to automate it so I created a Excel book here on my desktop and I open it and there's only one sheet in it here I have five columns three rows and one header row so the first one that is a good Tablet TV laptop B that is revenue C that is cost for that good and then we want to calculate the profit the profit is simply just the revenue minus the cost and then we want to write a status so such as processed a very simple task but it shows you all the nice features about automating Excel in power automate for desktop so let's go do it so I close my Excel book here you will find that automating excel in power automate for desktop can cause some problems and I'll also show you how to solve them so stick to the end first I grab the path of this Excel book I press shift in right click copy as path then let's move over to power automate for desktop the first thing we want to do is to find a launch Excel drag it in this launches an Excel instance that we can use here we can choose to open a blank document or a following document which could be our Excel book on the desktop sometimes you want to create a log from scratch then you choose blank otherwise we want to open something here I can either browse to it by clicking here or I can simply just Ctrl V pasting in my copied path so if I delete these two quotation marks that's it that's the path make instance visible that's nice in the beginning when you want to see what's going on when the robot runs but honestly it's not necessarily and it hurts performance a bit so close that I'll take that so now and then we have open mess read only if we don't want to edit the data in it which we want here then we can tick open as read only that will increase performance a bit but for now we need to edit it so we'll untick that as well then click save generally speaking I don't like to hard code in values that is this path here imagine that this changes I need to go into this action change it here that might not be the problem but imagine that we have used this path four times an offload and I need to update it four times so best practice is to create a variable for path so up in actions find a set variable and drag it in above the launch Excel this one here I could call this Excel path like this and the value that will be this path again here so I just Ctrl V and delete the quotation marks now creating a variable has the advantage that I can now just refer to the Excel path and then get this value out so if I click save here and then go into launch Excel I delete this document path then I can click this little X here Excel path double click it or click and then select like this now it got chosen here so then I can click save so now we created a dynamic a variable that we can easily change up here so now we launch it usually we want to make sure that we also close it again because that can cause a lot of problems in Excel I will show you a little bit later so again stick to the end so here I take a close excel in and what I want to do is that I won't before I am closing Excel I want to save it so and here I you can argue that we want to create a new document if we wanted to create different logs so say that this robot runs every day we might need a new book for each of those runs then we just take save document as and choose a document path with the dynamic expression but now I will just say save document that will just override whatsoever in it but we will not delete any data we will just update the empty columns now we can start reading from this Excel sheet so these were just the skeleton that we built now we can put on some meat so here I want a read from Excel worksheet drag it in just in the middle of the launch and close Excel so here I need to see what do I want to read I could read a single cell I could read range of cells I can take a selection that is if I work in the UI like opening Excel physically we will not do that out we'll try to refrain from that or we can just take all available values from the worksheet that's the one we'll pick we will read everything that's in this sheet up here and we can go in advanced and here you can see first line of range contain column names we have column names Heroes so that one will take that one you can see that we're reading this data into a variable called Excel data this is a data table a data table is a very important variable type in power automate desktop as we'll often use it when we want to manipulate a specially Excel a data table looks exactly like an Excel sheet except it is zero indexed and it only exists during the runtime that is when the robot runs so when we read it we read it here then the robot stops somewhere down here then then this data table will not be accessible anymore so if we want to save something in it right now we just read it but if we want to update it we need to remember to save this data table into the Excel sheet so let's try to click run so we are launching Excel then we're reading from it and we're closing it if I go over here to the right to variables in case you don't see it it looks like this or it's empty just click this little X up here these are the variables that we have created the Excel path we created ourselves and these two got created multiple Ram open the Excel data by hovering your mouse over three rows five columns and double clicking that is our data table and it looks exactly like the Excel sheet except you can see here the first row is called 0 and 1 and 2 and actually and this first row in the Excel was column two because the headers was actually all was actually row number one so you just need to to say that in the data table we have a headers um headers row that that doesn't have a name and then the first row is zero index so that is zero just need to get your head around that and you'll be fine don't worry it will come to you after you made your first couple of robots now we want to update um these two here so we have uh the difficult one that is profit here we need to make a calculation based on whatever's in here here here here and then the status so when we processed these rows here I want to write processed in status let's go create that first that's the easy part and then let's pick the difficult part um afterwards we need to say in this data table I need to iterate through each row one by one and then I can update profit and processed one by one so here I'll find a for each and I'll drag it in right after the read from Excel worksheet the value to iterate here I want to iterate the data table so click this little X here double click the Excel data so that's the one I'm going through I'm now storing each the iteration it takes the row one by one and each time the iteration text row I can refer to that by saying current item I often want to create my variables so it reflects the data that's in it that's also best practice so here I might say good then click enter don't worry about these percentage sign that's the sign of a variable in power automate for desktop and power automate for desktop automatically created when we create when we just write here in a variable field you can see it already did it for me as well so then I can click save so now I'm iterating to it I need to do something about it and we want to write to our Excel sheet so I'll find a right to Excel worksheet here and then I drag it in so now we just need to think about what do we want to write well um we want to write Pro cessed like this we want to say I want to write on the specified cell so which column do we want to write in and here let me just click save this will give us a few errors so if I just open it up again the column that we want to write in that's column e you can see it here so let me close it again open it up go into write to Excel worksheet and take the column now we need to fill in the row and this is the row that we are in and since there's no way we can we can do it if we pick this little X up here and we can see the goods there's no thing that we can get the row number we can only get the columns count the column names so what we will do here is that you'll create a supporting variable that's an important Concept in Excel automation so just go outside it again find a set variable and this set variable it will correspond to the row numbers in Excel so here I'm saying set variable and then I will call this Excel row counter like this and we will give it the start value too and that's because we're writing back to the Excel worksheet and here the first row was in row two let me just show you that as well so we complete this year that we aligned so this is the first row we are updating it and that because the first row are here that is the headers so here I want to say I want to update this one first then I want to add 1 to that counter each time the for each runs through our data table so we know that we are aligned with this Excel sheet it looks like this so I go back here I have this variable I gave it the value to that corresponds to the column sorry to the row number then I find another set variable drag it in in the end of the for each here I will add one to this counter so click this x here say Excel row calendar go into the value Excel row count and to add 100 you need to go inside the percentage sign and say plus one like this so now we have a counter that we can use up here in the right to Excel worksheet go in here save row click the selection Excel row counter and click save shouldn't we try to actually see that this works remember to save your flows in power to make for desktop because there's no auto save here and you might restart your computer without noticing that you had a flow here then it will be lost so now we are running we can see that we are launching reading set variable it ring true to three rows and closing Excel we only had the process now but let's just see that that worked then we can easily do the conversion that's it we processed three rows here and we wrote in the status now we just need to calculate the profit The Profit here that is calculated where I want to say Revenue Minus cost so it will be 4 000 here 2 000 here and 10 000 here um it looks like numbers and even though if we formatted those as numbers and they will still when they come into power to make desktop they will still be treated as objects or text values so we need to do a conversion so we get them from here and then we convert them to a um to it to a number so we can do a calculation and here um let me just do this and go back here so in our data table I want to say for each one of the rows I want to say the revenue that should be stored into a number variable called revenue and the cost whatever is in here will be stored into a cost variable numeric value so I'll need to find two convert text to numbers like this so here convert text to numbers and we will do it before the processed so drag it in here so which text do I need to convert I will look in the Q and item which we named Goods so click this little X here take the goods now I just need to specify in which column do I want to look well the first one we'll take that was the revenue so say hard bracket start hard bracket end single quotation mark and a single quotation marks it might be a little bit difficult to see but it's it says goods and then hard brackets two single quotation marks and we'll start writing inside the two quotation single quotation marks then I just say Revenue so this will take for each one of these Goods row it will convert whatever is in the revenue column it will convert that to a number it's called text as number again we want to rename our variables that is best practice so here I'll say Revenue like this and then I can click save so now we have converted the revenue then we also needed the cost we can do two things we can either drag one more in here or I can say Ctrl C control V now I have two similar I just need to update this Revenue to a cost so I double click it like say cost instead of this and the variables produced that will be cost so now for each one of these rows when I'm entering through it I have two values now revenue and cost I can write back to my Excel worksheet so um here I'll say right to Excel worksheet and the value to write well I will just save Revenue moved over here Revenue Minus cost and to do that I'll need first I can get the revenue find it over here like this and when I want to subtract two variables um I need to only have percentage signs in the start and in the end so it will looks like this I can have spaces in it doesn't matter you don't have to have it but I think it makes it a bit more easy to look at for you and then I'll say cost I can also if I want to make sure I don't misspell I can also find that one over here but then I just need to delete these two percentage signs so this is my calculation then I want to say where do I want to write it it will be in a cell and that will be in the column d That's one before our column E before that's where our profit goes and the row that will still be in the Excel row counter make this x here cell row counter save so now we have our workflow I'll save it again and then we will try to run it so here we are launching Excel we have reading from it and each range you can see it takes a few seconds more and let's just go to our Excel sheet that's it that's how easy it is to process rows in Excel I also told you that we often get some errors when we automating Excel that's because one drive power automate for desktop and Excel doesn't really talk that well talk that well together it's not a problem you'll usually get a failed to open Excel document or a file related error let's Force the arrow and then let me show you how to solve it don't worry it's very easy so if I go up in the first this right to Excel worksheet here I'll say Revenue Minus cost and then I just say minus a text value so this one will give us an error and um the thing is now we can force the Excel error so what will happen we are launching Excel this will give us an error here the workflow will stop and then we are not closing it properly that means that it locks the Excel sheet let me show you so if I just click play here we're launching Excel and there you go we have an arrow and if I try to run it again I can show you both I can if I open it you'll see that like getting these kinds of Errors I can't really open it and when I run it again we will fail up in the launch Excel where we will get a timeout error that we can retrieve that we can open this Excel instance so let me just fast forward till it fails and you can see that it actually just keeps running this will give us an error eventually and we don't have to wait for that so I'll click stop here so now we want to say I want to fix this and this is actually very easy um so what I will do is just I have my Excel sheet here so I'll go down to the start menu here I will just start typing CMD that will give us a the command prompt so we'll open this so now we want to close Excel by force and remember if you have any Excel sheets open on your computer this will also close them so remember to save them because they it will not prompt you so what we are going to write here is task kill forward slash F that is force and then we want to say the image of excel XL so when I run this command I just press enter you can see that we now shut down two processes we also need to go into Data now this is unlocked and click close here and then we can click save so now and just for good manner press the upper arrow in here and just do this again it's not found now but sometimes it opens up again so now we can run it and we have freed so to say our Excel sheet and here you can see we still have the arrow so we should of course fix that one and we can do that by saying this and now we will get the arrow again so let's just count that by doing show here click close here we repeat it it's nice to see so then we do this now you can see we have the process has been terminated so again whenever you went in and click close into document recovery remember to do the task skill now the process will run perfectly so that's it is this video helping you then you can really help the channel and Me by giving it a thumbs up thank you now we will look at how to automate applications in a broader sense we will use a symbol application which is the calculator but the same principles apply in automating all applications so we want to automate notepad it's the same principle that goes whether you automate notepad or more advanced applications so notepad is a good sample application the first one is that we'll need to find the path of the notepad so I'll go down to start and this is a good way to find path of the applications that we want to automate I'll need to use notepad so I right click then I say open file location and let me just drag it in here here we can see this is a shortcut so again I right click open file location here it is so I'll need this path because I now I want to open up notepad so here I can shift with your keyboard right click with your mouse and then find a copious path move back to Power automate desktop so the first thing we will do is that we will open up this um notepad in here so we'll find a run application and that one so here we'll paste in the path remember to move the quotation marks this will actually have worked if we just had this this done this but for stability and because we'll use that with a lot of the applications difficult path we will just type in the full path because you might not know that with some of the apps we can only use the endings so always just use the full app this will produce a variable call app process ID I click save now when I run this application this will open up a notepad so far so good so here we want to fill something in that's just some text that could be every application that we are filling something in this is an input field just like you know input fields in sap service now or whatever you use so go up here and then find a populate text field in window take the one under from filling so I'll drag it in here here we need to have a UI element that is a text box so click that drop down say add UI element here you can see that in the app I can now choose different parts of the app that is all elements that we can fill text into I know it doesn't make a lot of sense to fill text in here but it is possible we want the input field down here so I press Ctrl on your keyboard I repeat control on your keyboard left click with your mouse we have now created a UI element now we just need to fill the text in and I want to say if this video helps you please help me and give it a thumbs up thank you so now we have created a working flow why don't we try to run it so it will take a few seconds and then we'll type in the text there you go you have now created your first Automation in applications well done we also want to make it build text and here I want to click format and then click fund and so fourth so go up in actions and this is just like clicking buttons in any applications we'll find a click UI element in window and drag it in here we have a UI element so again we click this little drop down take the add UI element let me just move it over here so I find the format that is the first button that I want to click press Ctrl on my keyboard click the format so now we're doing two things one thing I want to show you is click the stack of paper over here to the right that is UI elements here you can actually see that we have created some elements and we have different windows so windows or actually the computer is the top level then we have a window and here you can see we have a little asterisk and that is because when we write in a notepad this little asterisk comes up it means that um it's unsaved so that's why we have two different Windows here we have the document text editor and best practice is to rename your UI elements so either right click or F2 click rename so here I want to say notepad input field and down here I can say rename I can see a format button make something that do something meaningful so it's easy to maintain both for you and for future developers so not in Practical difference but for um for future use you will be happy just to make it a habit so now I can click the format button let me try to run it again here we fill in the text in a few seconds there you go and we click the button so that's it and you can see here it actually it because we have we had two notepads open it got a little bit confused and took the other one and clicked the format in the last one that's why it's always good practice to close down all open applications when you begin an Automation in that way automation will not get confused so everything works but we just need to close down notepad here at the beginning and for that I can find a terminate a process and drag it in in the in in the start sorry so we'll find it up here and here I'll have a process name so what I can do is just click this little drop down and either type it in I'm lazy so I'll do that or we could write it since we don't have any notepad open let me just click save here and open up a notepad you need to have the application open so I created this little trap and I didn't I just forgot that I should have opened the last one so I like these videos because you'll see how I work and now we can find the notepad it's probably also the same mistake as you can make and I think you can learn a lot for them so from them so then I can click notepad I click save so now we terminate the notepad process first and let me just open one more so we can see that it works so now I run the automation I terminate the process that will that close to two of them and now we only have this one open so now we are clicking the format that one worked now we just need to click the fund and get into the font options so we go up here and then we'll have a click another click you element in window again we will create the button so click the drop down add UI element and now you need to don't click control now so click the format then when you're here click Ctrl press fund go back here click save again I want you to go over to the UI elements gives us menu item format blah blah blah here I really just want to say font button you can see it makes it a little bit more easy to read so now we need another click view element because when we have clicked format find this power Below open I need to click bold and then click ok so another click view element you know the drill and this is the exact way you automate applications it's straight forward no magic here take this drop down add UI element find the Bold here press Ctrl and we have created another one then I can click save here it says list item build that might be fine it's actually a list and it's called bold then we warned the last one in here and we want to add another U element and click ok so here I place press Ctrl and we created that one click save I think I just want to use the syntax that I used before so okay but now um we can just actually have this one open because we're terminating it up here so let us try to run it so we're terminated opening up a new instance and again the benefit of doing this is that we can always make sure that we start up a fresh instance where we have our rules in and there you go we have automated Notepad so since this one will stay let's just make it back to regular when we want to close it we could of course have this file exit and then choose if we wanted to save and so forth you can do that that is just more click UI elements that is a good approach if we want to save it here we don't have anything that we want to use so let's just copy this one here go down to the button and paste it in we'll term in it again that we would like to see that it actually works so I find a weight bracket in and let's just wait for four seconds click save I only do this so you can see what's going on and not not for any practical use so I just started now um we are filling in the text again we are formatting into text so now you actually learned how to automate applications and we wait four seconds to terminate it what do you think about power automate desktop and discourse please let me know in the comments below now on to automate browsers and do a login I have a web page here which you can find by navigating to the internet dot herokuapp.com forward slash login here I need to do a login with power to make desktop that's it fill a username fill the password and a login and hint hint the username is actually up here since this is only a test page a password is here I'll be that one in here click login and now our task is to take a screenshot of this area wait three seconds click logger I hope you understood it because we will create it together now I just refresh it to get rid of this green bar to open up a browser and to navigate to a URL we of course need the URL so I go up here and copy in the UI back to Power automate desktop I'll find a launch new and here you can see I can pick Internet Explorer Firefox Chrome or Edge as long as you don't choose Internet Explorer as that one is outdated and insecure I'm fine with whatever browser you choose and each one of them will be very easy to automate so I'll launch a new instance the initial UL press Ctrl V that is my UL from before here you can see we produce one variable that is a browser variable so this is a browser instance that we created and when we want to automate in this window we just refer to this variable I click save try to click run and see that you can actually open up the browser and navigate to the page this one works for us now we need to fill in the username and let's just copy the username from up here we will store the username in a variable so I'll find a set variable and drag it in above the launch new Chrome in variable here I will call this username and the value we'll give it is Ctrl V that will be Tom Smith now I can refer to the username variable and whenever I do that I'll get the value Tom Smith back I click save now let's try to use it so I'll find a populate text field on web page on the web form filling drag it in after launch new Chrome here you can see that we are referring to the browser instance that we just created we talked about the UI element that is anywhere on a web page we have UI elements and if I click this drop down I can click add UI element and here you can see we can add different UI elements from this web page and actually this is what the web developers made and I will choose the input text here now press control on your keyboard left click with your mouse and we have created our first UI element which is the input username field in here in text we could write Tom Smith like this that one will fill in Tom Smith but we created a variable for that a variable needs to have percentage sign in the standard in the end we don't have to write it just click this little legs here and here you can see our username variable either click it and click select or simply just double click which I prefer and we can click save so now we have our first flow Let's test that it actually works so we open up a browser fill in a username it's that easy to automate web and let me go back to the brow to the power to my desktop if I go over here to the right click UI elements you can see that we have created a UI element here called input text username that is the input field first I like to rename my variables so I can either click right click and click rename or press f2 I'll call this username input field is to have a little bit more describing a name you can see that it automatically gets updated the name over here down here is a picture and if I double click it here you can see our selector the selector is built up from these elements we will not go true in this session but it's worth to mention that we this selector is the CSS selector it says look in the input element find the attribute ID with the value username let me show you where this is from because this is valuable this is a little bit more advanced but I'm sure you will like it in a browser you can press F12 that will open up the developer tools I want to inspect the code for the username field so if I click this little arrow when I'm in the elements like it here and there you go the input that was the input element if you move a little bit in into that element you can see the attribute ID equals username so this is actually just the code that we used we could have tapped it in ourselves if we wanted to do do but power to make desktop do it automatically so that's how easy it is let me close it again click cancel here now you know where these selectors originates from we also need to fill in the password so let's go copy that let's find a set variable like this and drag it in here this one I'll call password Ctrl V the value in super secret password I can click save similarly we will populate text field on web page so I'll find a public text film web page take the one on the web form filling and not the one on the form filling so I'll drag it in here the UI element that is the password field if I click add UI element and find the password field just as we did before now click control on your keyboard and left click with your mouse we have created another UI element and again the same syntax clicked as the legs here in the text double click the password now we can fill the password in I click save go over to the UI domains again in case you don't seem it looks like this you can just click him go down here and now try to click F2 that will also rename it this one is called password input field like this now again let's try to run the robot to see everything works we usually do that we do it step by step so we can see that we have typed in the password and it's very likely that it's the password that we set up here now we need to click a button so up in actions again then you will find a click link on web page and that one is here drag it in after the two populates because now we have filled in username password we need to click again we end the browser instance and we want to add another UI element so click the drop down add UI element go found find the button here you can see we have both a text and a button both will probably work I prefer the button but I think both will work so just take that one press Ctrl left click with your mouse we've created another UI element again I click save and I'll always go over to the UI elements make it the best practice to always rename these UI elements that will make it easier to maintain both for you and your colleagues so in the button radius right click rename and here I will say login button I click enter now we can try to run the robot again to see that we can now fill in the username password and click the login that's it we are now logged in this one up here is just my password manager so don't worry now we need to take a screenshot wait three seconds and click log out so we will do the take screenshot first so let's find a take screenshot off web page we can also do a view elements but for now we will do the entire web page drag it in here so here I can say do I want to take the entire web page or specific element I'll just take the entire page save it to a clipboard or a file here I need to have a file I want to save it to my desktop a good little project let me let me click save that will give me an arrow so I go to my desktop I right click then I say new and a new text argument this will easily get me the path of my desktop instead of I have to write it and make errors so I shift right click copy as path go back to power to make desktop now I open up the text screenshot of web page again I could also click this little file selector but I prefer the other way so here the image file now say Ctrl V I know this is a text document we'll fix it so I'll do this delete the two quotation marks and then let's talk about what file format we want to use we want for example a PNG that is the most often used so I'll take that one then we'll also need to have a PNG ending up here so instead of txt we'll have a PNG now let's give it another name so I'll say maybe new screenshot nothing fancy but this will work so now we take a screenshot of the web page place it on the desktop we will wait three seconds and then we will click the lock out button which you'll find here so find a weight here practice waiting here you just need to specify how many seconds you want to wait for I'll wait for three the reason I do this this is just the process description but it's actually so we can see that we are clicking logout so that's the only one for this and you'll learn to make these delays we'll often also make delays in web development in power to my desktop when we want to load things probably so now we want another click link on web page here drag it in let me just drag it in in the end so we need to create another UI element for that so add UI element take the log out here press Ctrl take it and click save again you know the drill go over to UI elements and here you can see we are on a different page so these ones got created up here from the login page this is the secure area so we created it down here right click rename and then you say log out button like this click enter so now we have created our entire flow shouldn't we try test it so here I'll click run login do a screenshot wait three seconds and then do the logout that worked we just need to check for the screenshot that is it's that easy to automate browsers as power automate desktop developers we work a lot with files and here's how to do it I have created a folder with six files in it and the mission now is to sort each file type and then create a new folder and cut them into there so that means that each text file will go into a directory called txt and so forth I also build in a trap that you'll see a lot in the real world that is the extension here is in capital letters and this one is non-capital letters but of course each text file must go into the same folder my name is Anna Jensen let's learn some Microsoft power automate desktop if you want these sample files you can go to the link in the description below mark them and then click download I downloaded mine to my desktop and we'll see him here that's the directory I want to grab the path of this folder so what I do here is that I press shift and then right click with my mouse that will open up this extended menu go find the copious path and we'll go to Power automate desktop and create the solution first we will set a variable for the folder path so choose a set variable and drag it in we will name our variable file path then in the value control V to paste it in so I'll delete the quotation marks and then I'll click save now we can use this variable name whenever we want to call the path this has the benefit that if these files get moved we can just change it here in the set variable now I want to get the files that is in this folder so I'll find a get files in folder and drag it in so here the folder that is the folder path that we just created up here called file path so I'll click this little X here and choose the file path we will not choose to include subfolders but we can do so if we want it developers produced that is called a files and that will be a list of files but then I can click save let's try to run the automation to see that it works that we can add the files to a list if you go over here on the right and click this the legs if you don't see the variables in files double click it now we have a list of files each item has its its place on the list we have three items and the first one is index 0 then 1 and 2 that is because this is zero indexed if I click the first one I can click more here you can see and let me just expand it we have some properties of this file for example we have the extension and that is called dot extension and here you can see the extension of this last this first one here that is called dot XL X and um this is the Excel file and if I just go back here you can see all six ones here I can again I can just do this to show all the elements now we will work with that extension to determine what type of file it is and then we won't do the Sorting so I'll click close here the first one we'll do is to iterate to each file so I'll find a for each a for each Loop iterates a collection from start to finish and the value to iterate that will be my list of files you'll find it up here so I click this x and double click the files I will then store the current item into a variable called q and item but since we each item is a file let's just rename it to file it doesn't mean anything but this is just a reference variable that says when we work with the first one we can call file that will give us the first one the second one we can reference to file again and grab that one so just for referencing it makes your flows a lot easier if you rename your variables to names that is describing for what they're doing so for now let's just try to write out the extension so find a display message and we drag that one in so in the for each down in message to display click this little X here click the arrow to the left of the file and we will find the extension which is here and double click it now our expression is file.extension inside percentage sign let's see that it works so I'll run it here you'll see Dot xlsx dot xlsx in capital letters and so forth let us just stop it now we know that it works we need to do a little bit of text manipulation first we will remove the dart and the dot is always in its first place so we'll just remove the first character for from this value so what I'll do here is that I'll find a get subtext here and drag it in in the start of the for each the original text well that is the file.extension so here again I'll just do the file I always prefer to find them here in the menus I could write them I could just write this but we will reduce the risk of Errors when we just choose them in automatically I want to start at a position and in programming it's zero index so the first position that is position zero then the second position is position one that means that we will start at position one end in length that will just be to the end of text and the variables produced that is a subtext but here I'll say extension value this is just the name of the variable and I click save now we can write this extension value out in our display message so simply just delete it click this little X here and double click the extension value then I can click save so now we have removed the dot and we can see it when we run it here we have removed the DOT that's the first part of our mission let's stop it because we also want to make every extension to lower so we can compare them better because it has no difference for us whether or not it's capital or not but else there will be and we will click this little X here and then say change text case and drag it in right after this gets up text the text to convert well that will be the extension value so I'll find it up here I will convert them to lower case so pick that one here and the variables produced let's just save the value to the extension value again click this X choose the extension value and click save so now we have to remove the DOT and we have converted to lowercase Excel is X plus X docx and so forth there's no difference in the capital or non-capital now we can start comparing them and moving to new folders we will first count them because we also need to create a nice log and let me introduce you to a custom object a custom object can be seen as a dictionary where you add key value pairs in so I'll find a set variable we will initialize it in the beginning I'll drag in the set variable Lobby and then we'll give it a proper name I'll say five count like this and then we'll give it a value so the syntax is we want percentage signs in starting in the end then we want curly bracket in the start in the end and then we can add the key value pairs for example we can say test a in single quotation marks then a colon and we can say this one has a value of result a like this and so this is a key value pair the name is test a and the value is result a then it's comma separated so it looks like this we can add a space that doesn't matter it would be treated the same now let me just add one more item so I'll say test B in colon that equals two result B this is just to show you how this nice custom object works that will be useful to you let me have a display message it's just to write out something so if I want to say I want to look up test a or test B I can go into the X here if I go into the file count that is this custom object up here and then after the T hard brackets then single quotation marks and inside that I can say test B so I'll look up a name and then I'll get a value back and click save now let me try to run it here you can see we look up the test B and then we get the value result B back that's how it works we will use it for the file counting that means that we will have each extension in this list that means that xlsx will be one item and then we will have the count of the files the dxcx will be another item and then we'll have the count we will only add it once that means that we should check if this extension exists in this file count it will not for the first item so then we will just add it to it and we'll give it a value 1 because now we know we have one file type if it exists we will just add one to it it looks like this so and I really want you to do this because this is so nice to be able to do it will help you in a lot of your workflows I'll stop let's also delete this display message so I'll delete it again I go in here because this will be an empty list we will of course not have test a test B that was just to show you so I delete everything and to make an empty one we will have two curly brackets in the start and in the end like this I'll click save it looks like this so now we want to say does our file extension that is our file type does that exist in this file count if yes we will just add one to it if no we will create it so when we ask questions we'll use an if search for nif drag it down below the change text case and then you will ask and I'll go here so we want to ask is if does the file count contains click this x file count and then we want to say does that one contains and we'll say does it contains the extension value here and that means that it contains XLS X at dxcx so fourth and we will click save we will not use this ignore case we could have used it and then got rid of where we change the text case but since we're also going to use these extension in a lock we have created the solution with this one here so then I can click save so now we have an if and if this is true we do something we also want to add a branch to whenever this expression is not true anymore that means that the item is not created up here so I'll find an else and drag it in right now we have nothing in it but if it doesn't exist we want to create a value up here the key up here and give it the value 1. so let me find a set variable and drag it in inside the else so right here I will say um I want to set the file count go here I want to say give me the file count but the key I'll put in inside of hard brackets if I just wanted to do a test B test C I'll have it in single quotation marks but because our value is right here in the extension value I'll use that in here delete this text C and I'll just say extension value so because we are inside this percentage sign that means that any text here without single quotation marks will get treated treated as a variable so we're going into the file count we add this key the extension value now we just need to give it a value and since it doesn't exist before we're creating this we're down here in the else we will just give it the value one it's the first time we see this file type then I can click save so I can run and expect inspect we don't really do nothing and right now we could have deleted this display message because now I'm sitting here and clicking this one six times it's okay I'll just delete it afterwards that's not really the big issue but now go over to your file account and click here so now you can see we have one xlsx one dxcx and one txt that is because we're only creating the item the first time we see it we don't do nothing if it's exist but now this part of the solution actually works so I click closed let's delete the display message down here and we solve for the then Branch we'll just do the same as here so we can either drag in another set variable or simply just copy this I recommend copying it because we are lazy as automation Developers so our control C go up to the else Ctrl V we need to modify this a bit so if I go in here I'll say it's still the file count extension value but now I want to add one because I know it's there oh check for that ndf so I'll add one to it and that means that I will wrap this expression remember the sentence sign so I copy this one here and put it in here to add one to it just move inside the present sign and say plus one this will add one to it now I can click save try to run the automation again now we have created a solution that creates a nice lock we still need to create folders and move the files but if you go over here that's it and this solution is so great because it works with any file type you can try other file types this will work so then I can click the sex so now we know we we need to create a folder if we haven't created one that means that the first time we see the file type we'll create a folder and again we're down here in the else so like this little lecture and then find a create folder it is here put it in the else Branch it doesn't really matter if you put it on top or in the end of the else so we create a new folder into that it will be the file path that is the directory of our files the new folder name that will just be the extension value that could be xlsx dxcx and so forth so refer to the extension value like this then we can click save so now we have created the folder and we know the folder exists if we up here because then otherwise we would have created previously so now we can start to move the files so we will go after the if down here and then we'll find a move file s here and drag that one in here so the files to move and now that's just time to do a referencing we are referring to the file up here so click the sex and this is the file that is because that is where we iterate each one of our files where do I want to move it well I want to move it into the file path by the path that one is up here so that is our main phone but I also want to create a subfolder otherwise it will just move it to the same folder so I'll have it backwards last and then I will move it to the extension value like this so now um we have created a solution if the file exists well it would only exist if these folders were already present and then we would have to create a dynamic solution here I recommend using a date time but this is not the scope of this exercise if you want to know you're more than welcome to ask down here in the description I can link you to my solution then we can click save so now we are moving the files let's also create a log when we are done here I'll just write out this file count it's not the most fancy lock but again it's a lot and we can in later [Music] um lessons we can see how to create even more nice logs so here it will just be a display message but it could also be to a text argument go down here and then just write out the file count like this then we click save so now again let me repeat what we actually have accomplished here because it's quite huge you're entering to each files then we'll count them we're also saying that we treat these ones here in non-capital and capital endings equal and then we will start creating a folder and moving them let's see how it goes and I can open this so you can see the solution and you can see the folders are getting created and the files are getting moved and this is our lock again we could format it but it is a lock it is a working lock I can click ok yeah the word file is in here the text file is here the Excel files here automating emails is as easy as everything else in this video and here's how to do it I want to send Dynamic emails that is specific content to a specific recipient so here I have four students it could be an Excel sheet with 100 students but for now this is a fun example I have four columns name email homework and deadline their name is here I want to have that filled in they have separate email addresses because I don't want to spam anyone or myself I just use this 10 minute email you can fill in your email or your 10 minute email here in this column this Excel sheet can be downloaded in the video description so you can change it here homework you can see Anna needs to learn power automate baking needs power to make desktop cardi needs power automate that here we have space and door on each UI path but don't worry too much about this is just to show that we can fill in Dynamic content in these emails I also have a separate deadline so I want them to be filled in probably let's show how it works and this 10 minute email we can just refresh the page and then get 10 more minutes or click 10 more minutes here and that is the one that we use here so in power automate desktop let's first read this Excel sheet so let me close it I downloaded mine to my desktop you can do the same so shift right click like this copy as path we will use that path the first we'll do is to set a variable with that path in we'll drag it in so here I'll call my variable Excel path like this and just Ctrl V paste it in delete the quotation marks click save so now I want to launch Excel so here I'll say launch and I'll drag in an Excel I will not open up a blank document but I'll open the following document now I could type in the path or find it by clicking here or we have created a variable for it this is actually best practice because we want to make sure that it's this pass path changes we can update it quickly and imagine that we use this path as that in our flow this will be convenient I will not have the Excel instance visible and I will click save like that so now I also want to read the data from the Excel sheet so I'll find an a read from Excel worksheet and drag it in I want to retrieve everything that is in the worksheet and in the advanced I'll say first line of range contains column names I'll take that one in the variable is produced that will be Excel Theta so then I can click save you'll also need a close Excel so I'll close my Excel that one is here that will just close the Excel instance and do not save it it's fine so we created we read our data now we can start to send our emails but let's just see that this works that we can launch Excel read the data close it again and this is just to show that our data is over here there you go so name email homework deadline if we wanted to make it a little bit more stable say that we have two sheets in this Excel sheet I know this is not an Excel lesson but best practices to always activate the correct sheet and here let me open it again imagine that we have this one as last active then we will start reading here we could not use these data so we want to make sure we are unconscious right click rename Ctrl C close it down again then in power automate desktop now let's just find a quick activate worksheet so active weight set active Excel worksheet you can see there's a lot of actions to fill in so set active Excel worksheet and then right before the read I'll just paste in the worksheet name and click save I will not show you that this works but trust me or you can just try to run the robot pause the video and do it so now we need to launch the Outlook I'll find a launch Outlook instance and this is necessary to have an Outlook account on your computer so yeah we'll launch the Outlook instance like that and now I want to say I want to iterate to each one of the rows in our data this data table over here the Excel data got read into this data table I want to take them one by one and then process them so what I want to do is first take the four each like this and drag it in here the value to iterate that is the Excel data click this X double click the Excel data here you can see that we refer to the current item that is the row that we have as q and item I want to refer to it as student so I click save so now we're iterating through each one of the rows one by one since we need to send separate emails that's that's why we do it like this so in Outlook find a send email message through Outlook and drag it in the account that is the account data file that you use in Outlook minus called this who do we want to send it to and now it gets not tricky but we just need to know what we do since we refer to each row as student I want to say two take this x here and then go find a student and double click it now I just need to say what column do I want to fill in this is the email column in case you forgot you can always open the Excel sheet and inspect so here I'll have a hard bracket single quotation marks and then I'll say in a student but email single quotation mark and hard bracket more I also want to have a subject and here I'll just say remember to do your homework and then I can fill in the body I can also add attachments and everything more but for now this simple exercise will do so here I'll say hey and then I want the student's name click this actually again you know the drill say student inside here hard bracket single quotation mark and then I'll just say name single quotation mark hard bracket end so and then I'll say remember to do your and again um I need to say what kind of homework do this student want to do so I click here take the student go in here and then pick the column in the Excel sheet that I want to use so I just make two hard brackets two single quotation marks at once and I'll say homework so this is saying go to the current row homework column and use what's there so I'll say make your this kind of homework homework no later then we also want to get the deadline that is the deadline column and again I'll just find a student go in here do this see hard bracket single quotation marks and then say deadline and again this is the Excel column names so this is not standardized um references this is just what I chose to call it in um in Excel now I can add a signature let's just copy the one from my email where I say kind regards my answer and then my social media links so now I can click save we have everything we need and we even have our 10 minute email here let me just get 10 more minutes just for uh for Simplicity so now we have 10 we have an MTN box let's see if it works again if you want to send it to another place then 10 minute email feel free to change the emails in the Excel sheet now let's just inspect that we can actually send Dynamic content so we're launching Outlook iterating through it and let's go to our 10 minute email we have four emails you can see them down here let's pick the first one hey Anna remember to do your power automate homework no later than and then we have one 30 20 23 at 12 am since we didn't specify um a time this is how it looks we can easily work with date and times in power automate for desktop you can click the video up in here to make sure that you'll understand everything about daytime but since this is not the lesson let's just see that we here we have a Becky and with the power to make desktop homework we have Carly and we have Dora that's it that's how easy it is to automate sending emails in power domain desktop when I teach power automate desktop the most asked question is about web scraping it's straight forward and here's how to do it we need a data set and here I have some Google searches for my own companies I want to get the result that is each one of the titles the descriptions and the URL behind it so back in power automate for desktop I need to find a launch and here I can pick my favorite browser I choose Chrome and drag it in I can either launch a new browser or simply just attach it to a running instance I'll choose this one and then we'll make the elegant solution a little bit later in this lesson click the drop down here and then find your data this will produce a variable called browser and I'll click save now we can data scrape so if I go up to actions I'll find and extract data from web page take the one on the web data extraction this one will open here so if I open my data set a new Wizard will come up here that is my scraping wizard first I need to pick something from the first element that is what I want to scrape that could be the title but it could literally be anything here I take the title so I right click here extract element value and pick the text now you can see I got the first title over here in the Wizard similarly go down to the second item pick the title here right click extract element value there you go we have now extracted all the titles we can take this description as well so go to the first item right click extract element value and pick the text that's it that's how easy it is to do no code web scripting with power automate desktop the URL is a little bit more difficult to get so we'll just click finish here here I can see do I want to store it as a variable which I can use if I want to make a more complicated flow or do I simply just want to save it to excel I will save it to Excel and the variables produced is called Excel instance I click save here now I can run it will take a few seconds and we will open up an Excel sheet with our data that's how easy it is to whip scrape now let me show you how to get the URL because that one is not easily accessible so what we will do here let me just close this Excel sheet again to get the URL I need to show you a very cool trick so add your data press F12 that will open up the developer tools this is the HTML code behind this Google site and if I just click this little arrow pointed to a title just click it and here you can see if I move down here this is the title it's called something with H3 that was by the way the same name as we got before with the wizard and if I move a little bit down we can see that the text is called an agency org all about automation so power automate desktop will create an address for us called H3 and tell to get the text here if we want to find a URL linked to this title we can move one element back to the a element you can see here I have the H3 and up here is the a this is H ref anaciensen org so what I can do here is that I can simply just say cut a little bit away from the address that we used before and use this a element it goes like this so in power to my desktop I'll open the extract data from web page again and if I open up my data this will open the wizard we'll move it over here so what I want to do is to click advanced settings and here we have the two CSS selectors that we created for each one of the elements this one appears for the title and this one was from the description since we want to move to do a little bit of edit with the title we go up here press end on your keyboard or simply just scroll away there you go you can now see that this was the a element and this was the H3 element we know that and let me just find it again so just to show you we know that we want to go up in the a element and not in the H3 anymore so what I can do is simply just copy everything from the a without the arrow and the H3 then go down to specify additional CSS selector and paste it in here and here we need the attribute H ref so I'll say href then I'll click OK Bingo now we have the URLs that's how easy it is but then I click finish I click save we can try to run it again and we are attaching to the browser there you go we have now extracted it and now with a URL here so we can customize a our solution a bit more still with no code and let's start from an end we also want to save the data because this just opened and sells it and often you want to save the data so if I go up here and the easiest thing will just be to find a close Excel and drag it in so here we are taking the Excel instance that we created up here I can say before closing Excel do not save document save document or save document as we will do that we will need to create a document path and the easiest thing to do will be let me click save here this one will produce an error fine minimize and go to your desktop let me just minimize this as well here I'm just creating an Excel file like this I'll grab the path of it and the way to do that is to press shift right click copious path so now I get a path for this Excel sheet and this will be where I will store my data I will simply just delete this one again it will be created in power to make desktop now in close Excel go down to document path and Ctrl V paste it in now I don't have to write everything here we could give it a new name so instead of new Microsoft Excel worksheet we could call it Google searches if I could just hit so Google search is liked it this remember to have the dot xlsx on then I can click save so now when I run it we will also save a closed Excel and save it to our desktop here we have the Excel and we have saved it so if I go down here there you go we now have our Google searches on our desktop one thing I want to change more is that I want to give it a unique file name because we really if we run the robot again this will get overwritten over and over that's not really nice so a simple trick is to use the current date and time when the robot runs to make a file name so I go up here and here I'll find a yet cune date and time drag it in up here this will just produce available at a time variable called q and date and time save now we can convert this date time variable to a text so we can use it in a file name we'll find a convert date time to text did it take time to convert that is the variable that we produced up here called q and date and time pick that one the form and to use choose custom and here we will use the net custom date time format it's similar to the one that you know from Excel so this is the format I want my date to be in as text so for wise that is years two big M's that is month 2DS that it stays now I want hours in 24 hour format minutes and seconds this will be a unique name because the robot will not run every second so I can use this one here in my file name and now we have it as formatted date time so open up the close Excel and use it down here and the result that will be just before the Google searches click this x here and then choose the formatted date time and maybe we could have an underscore between the format date time the Google searches so let's see how that works I'll just click run we're launching Chrome extracting the data and now we save it to a unique Excel book look we now have a unique book I could run this over and over and it will produce a new file pretty clever we still have to make the elegant solution I promised you in the beginning because right now we need to have this page open instead let me just grab the URL of the page if you're scraping another page take the URL of that page now it can close so in the launch new crew choose launch new instance and paste in the URL I can click save so now I have closed each browser window I can just click run the robot will open up a web page scrape the result and save the data it's that easy there you go and again we have created a new result final thing this one is dynamic so I can go in here and simply just say Microsoft click save run the robot and this will save the Microsoft results instead very convenient and we can easily expand our Solution that's it that's how easy it is to do no code web extraction with power automate desktop I put together a list of the best Microsoft power automate desktop resources in the video description at the official Microsoft power automate desktop documentation you'll find excellently written articles do you want to network and solve power automate desktop problems with more than 5000 RPA developers then you're invited to the ilove automation Discord here you can get help with the most advanced power automate desktop problems and learn to handle job interviews since power automate desktop and RPA are fast moving Technologies books are quickly outdated however there's one book I will recommend you to buy and that is clean code it's not an RPA book per se but it's a book about how to write clean code that is how to apply all the best practices in software development and since RPA is a soft subset of that you will want to learn this so the link is also in the description for this if you want a Microsoft power automate desktop job you want to take the official PL 500 certification from Microsoft I created a guide to pass that you will find the complete list of all the resources in the video description below now you want to apply all the things that you learned here's a two hour case that I recommend you to build it will assemble all the techniques that we learned and you can just click the video here to get started

Channel: Anders Jensen

Views: 7,735

Keywords: power automate desktop, power automate desktop tutorial, power automate desktop web automation, power automate desktop examples, anders jensen, getting started with power automate desktop, how to use power automate desktop, how to get started with power automate desktop, power automate desktop beginner, power automate desktop tutorial for beginners, microsoft power automate desktop for beginners, start with power automate desktop, learn power automate desktop

Id: _7YkqephDxg

Length: 139min 10sec (8350 seconds)

Published: Sun Nov 20 2022