Power Automate Desktop Tutorial for Beginners [2024]

Microsoft power automate desktop that you automate websites Excel Outlook files and everything else on your computer the best part is that it's done with drag and drop actions and low code here's how to learn power automate desktop the best way as a beginner first we will install power automate desktop so please open up a browser and even though you already have installed it either through the Microsoft store or it's pre-installed in your windows 11 please install it again with me then we will make sure that it's set up the right way first you navigate to google.com then you want to do a search for Power automate desktop download hit enter pick the link from Microsoft learn most likely the first one it's called install power automate then we scroll a little bit down and take the download the power automate installer that will download the executable up here in the right corner once that is done we click open file that will start the installation process for Powermate desktop then you click next you can agree to the terms of use and once I click install here my screen will go black for a couple of seconds and that's because I need to click yes this installation process will take anywhere between 5 to 120 seconds so if yours is a little bit slow just lean back and make it finish then we need to install extensions so in install extensions for the for the browsers you have installed on your computer either Chrome Edge or Firefox or multiple of these if you prefer like one of them just pick that one and install the extension I will install it for Chrome and Edge but it's equally as good to use Firefox here I'll just say add to Chrome ADD extension that has been installed in Chrome I can now automate Chrome I'll also use it in Edge so I'll click here and I'll say get ADD extension we have installed it in it as well similarly for Firefox if if I prefer that browser you can follow this guide no matter what browser you use so we click launch after installing the extensions this is the power automate desktop homepage in the application if this is not in English you need to install the English language you can do that by following the video up here in the right corner and then come back to this video if it's in English all good let's get started so let me open up this here in my flows I have the previously build flows we don't have any don't worry we will build everything today I'll click new flow up here we going to create your first flow call this one recorder example then hit create now we have created our first flow it's blank we have the co- pilot out here to the right we will use cop pilot later in this video it's very cool feature bit more advanced so I close it for now in the middle here where you see you don't have any actions here yet here we drag and drop actions in and these actions once we click run performed sequentially so one by one that could be open up a browser click an element download some data save that data to excel email that Excel uh with in an email in Outlook a very simple process the beauty of this is that we can drag and drop these actions in and these actions you can find them here to the left in categories for example I can open up the browser automation category and to open up a browser I can either choose Firefox Chrome or Edge never use Internet Explorer that button is not secure I prefer Chrome at the moment so I drag in Chrome here I need to fill out a few parameters I can choose to launch a new instance that will open up a new new browser window or I can attach it to a running instance say that I already have opened up a browser window either manually or early in the flow I'll pick the launch new instance in case you want to know more you can either click more info here that will open up the documentation for the action um let me close here or you can also hover your mouse over this information over here then it says specify whether to launch a new instance of Chrome or attach to an existing one that has a little bit guide here then we need a URL and let me first open up Chrome and with chrome or whatever browser you have try to navigate to anon.org if you see this popup simply just make a selection first we'll do this manually that is just to avoid this popup but you can easily automate this as well so now I am here in the site I'm opening up the site I want to click courses and extract some data so let us do that and the first thing is that the URL is an asens or that was the web page that we wanted to automate the windows dat is normal this is the size that we previously closed the browser and we can also choose to maximize the browser or minimize normal is fine here it's important to say that we are automating to addresses also known as selectors at least per default that means that it doesn't matter how big or small the window is we are not using coordinates and we're not using images per default so you can just pick either normal or maximized then this browser instance is saved in a variable called browser and if I click in here I can see that a variable starts with a percentage sign and ends with the percentage sign so when we refer to a variable we need to have a percentage sign in the start and in the end this is clever because this variable called browser store for all the information about this browser window that we're just about to open that means that we can come back to this window later on in the Flow by referring to this name here I click save so let me try to click run we are opening up the website now I want to click courses and I also want to get some information out and here let's say it's the first time that we use power automate desktop it can be hard to find find out which one of these actions over here to the left so what you can do is to use the recorder click recorder up here the recorder captures user actions such as click with your mouse or keyboard typings interactions with other applications or websites then it translate these actions into Power automate desktop actions and that these actions can be executed so we don't need to know any I just need to record my own steps so what you will do here is to press record now it's recording everything that is also mean that if I you can see I can choose elements in the browser that also means that if I move this window a little bit down you can see I have a move window here I don't want to automate that I move the window a bit so I click a spin here to delete that recording what I want to automate is this courses so once I'm over courses and I can see see this red one I click the courses and here you can see that we have an launch web browser and a click element in case you have some slightly other actions that's okay sometimes Pate desktop translate to different actions they do the same but they are uh they are different don't don't worry I also want to extract all of this data so I pick the outer diff you can see div up here in the left corner but I go red around it now it disappeared so once I have clicked here I need to not clicked I hared the mouse then I can right click with the mouse I repeat right click say extract element value and just click anywhere in here on all this text then we are extracting all the elements in here yes they will be a little bit messy we will do data gymnastics later in the course so once that is done I click done and now you can see we had this laun lach new Chrome we did that ourself and then we have a comment here saying start of autogenerated actions using the recorder these blue ones here and two and seven these are comments we can use these comments to comment our code and that makes it easily understandable when we or our colleague come back to it later I can also open it so try click the number two here you can say start of autogen generated actions using the recorder I can edit this so for example I can just do this just to show you that when I click save it update the comment I don't need these comments these are not really informable when I come back I don't care if I use the recorder or not so I can click on this action then I can hold control in with on my keyboard click the other one then I have marked both these are the same ones the control that you use in other programs then hit delete on your keyboard they disappear you can also right click and click delete here we see that the recorder also said well um you you're La attaching you're launching a new Chrome and this time you can see here I attached to Chrome instead of launching that's because it didn't know that we already have it opened so I can just say delete to this then we clicked on the link courses and then we navigate to anon.org courses we could of course we could just navigate to this URL in the beginning instead of clicking but because I want to demonstrate how we click here let's just delete this go to web page if you have it then finally we have this get details of element on web page where we extract the data so shouldn't we go test the automation so here I click run so here you can see I click courses and I also get the details of element on web page if I do this then what I want to see here is that in the lower left corner we have a green check mark that means that our automation ran successfully if you see an error message down below you have done something wrong please repeat the step for now it's good so I go over here to these variables in case you don't see this variable it might looks like this simply just click this x here variables and you will find the variables used in this flow we have the browser that one got created up here and in this action get details of element on web page we created a variable called inner text and if I double click here on Advanced Ai and these ones here I have all the text extracted I couldn't write this out to a text file or an Excel file I know it's a little bit a big mess but it's a start we have done our first web extraction so far so good now we're going to get more advanced let us first save this flow there's no auto save in power automate desktop that also mean that you need to remember to save your flows because let's imagine that we work on this we minimize it for a couple of hours and then we just shut down Windows forcefully then these flows will disappear so remember to save if I try to exit it there'll also be this popup where it promps me to save I can just click save here then it's saved and it exited we will build a new flow now but congratulations you build your first flow you're about to become an RPA developer and we can find that flow here in my flows and when I want to come back I can hit this edit and if I run this flow I don't need to open it I can also press this play button that will also run the flow we want to create a new flow so I go up here in the left corner so even though I'm here in my flows I can also find it at home that one here in uh that new flow here or here or here so I click new flow and here we will call it portfolio let me just portfolio case and I'll click create I prepared a case for you today that involves that we're going to read an Excel sheet we're going to iterate the values in this Excel sheet we're going to do browser automation browser extraction we're going to create some real Advanced calculations and visualization we will also make Power toate desktop draw charts in Excel we're going to email these things we're going to work with files and everything I prepared it step by step so we take one step at the time very slowly it will teach you everything about power automate desktop so it's important that you follow these steps and you build it then you learn much faster instead of just passively watching anyway we have this flow and let me close the co-pilot again I don't have to but in it's a more clean look let's navigate to the course materials I can close these ones down as well just for now at least the course materials you should download those those are in a zip format so once you have downloaded either to your desktop or to your downloads folder right click on them say extract all and here let us just download it to our desktop in a folder called Project Feel free to place it somewhere else for example on another drive another Drive where you will save it I'll click extract and we have uh unied the project here I can double click it we have a case a and a case b let's work with case a as a start so I double click this and in here you can see I have an input folder double click to open that and I double click to portfolio this is our Excel sheet that we will automate as a start what I want to do here is to read all the data and here we can see that this is our position we are somewhat Rich so and this is of course sample data it's not my person wealth I wish but here I have a name a tiger a buying price curent price quantity total cost market value and profit lost we also have some assets down here from Apple to Paramount we have their tickets that is the unique code we can use that for search we have what we gave for them in buying price and how many we own then we for each one of these zos I want to navigate to a web page I can choose the Google fin and I will web scrape the Q price and add it to excel right back to excel once that is done I want to do the calculations of all this data so I want to calculate the cost the cost is the buying price times quantity the market value will be the current price times quantity and then the profit loss that will be market value minus total cost then we want to do a visualization I want to make a pie chart and I'm using co-pilot for that but one step at a time time we will set everything up so for now I will just close this one go back to um this folder here called case a so if I go back here to my project where I have the case a and caseb if you're on Windows 11 just right click on case a if you're on Windows 10 hold shift on your keyboard and click right click the folder then you'll find this copy as path go click it we have the path in our clipboard then we go back to Power toate desktop to our nlow so we are going to store this path in a variable a variable is a container for a value a variable has a name which we can refer to and then we can retrieve a value so if I go up here and say set variable I can initialize a variable drag it in here we give it a name never call a variable new VAR that's default it's very bad practice to do not name your variables well this means that it will also get easier to come back to the flow if we name our variables well and something that says something about the data and this is since this is our project path we'll name it that you can also see that I name it to the Pascal naming convention that means that I start each word with a capital letter that's also the way Microsoft names its variable here in power toate desktop so please follow that standard of course feel free to change it if you have a good reason to then we go down here and value and here I say contrl V to paste in path we don't need the quotation marks in power automate desktop so I delete those we will use this path later on in the flow so here I click save now we're going to launch an Excel instance that means that we will open up the Excel book so go search for a launch and when I do this and not find them in the categories this is just because you will get used to these names so fast here can also just say launch I can search for Excel and then find the launch Excel this is the way you will work after this guide trust me you have learned these actions one once this guide is done let's drag in the launch Excel so now I can open up a blank document that is fine if I want to start a blank Excel sheet and start filling in things but we want to opening up the following just document here it's important when you automate Excel files in power automate desktop that you have closed the Excel file so if you have it open down here below please close it otherwise it will fail and it will probably lock your Excel book until you have restarted or closed it forcefully then we want to say what document path should I use I just created a variable at least for the first part of the path so let's use as variable I could say percentage sign then I can a project path and a percent sign this will be perfectly fine to refer at least to the first part here um of the path but a better way is that once you have an input text box is that anytime you see this x click it and double click the variable that you're going to use that's because of course we're lazy we are automation developers but it's also to avoid errors so we don't have any spelling mistakes or anything then I also need to navigate to the input subfolder so I'll have it backwards slash and I'll say input and then I need to specify the file name of the Excel book that one was called portfolio. xlsx again it's important to spell right it's programming so if you don't have spelled the extension right this xlsx then your automation will fail we can choose to make the instance visible that might be fine if you want to see what's going on I don't need it right now so I disable it I can also choose to open it as read only that is fine if we're not going to save data back into the document we are that so we cannot take that one at least we do that later on scroll a little bit down and here you can see this variable is produced is called Excel instance we can refer to this Excel instance when we want to refer to this Excel book that also mean that we can give it another name and then we can open up another Excel book give that a second name and we can have two Excel Books open at the same time we sometimes need that when we want to compare it or move data from one Excel to another anyway we click save now we can read the data we just open up the book here now we can read a single sheet so to do so you will find a read from Excel worksheet and drag it in here you can see we work in the Excel instance and and because we only have one Excel instance power toate desktop just auto fill it in here if we had more we can find the other instances in this dropdown and we can pick between them we can choose to read a single cell range or in our case all available values from the worksheet we will just read all data what you need to do now is to click Advanced and we need to indicate that we have headers in our data set and we do that in first first line of range contains column names hit that and yes it's a little bit annoying that it's hidden so well I think it should be default up but and that's because I always have headers on my data at least like 99% of the times here we produce a variable called Excel data now we can maybe add a little bit to it I will have a position and this is because position this is because this is my position this will be a table a d data table so what we will do here is to read our Excel data into a data table and that's because it's better it's easier and faster to work with it than just opening up an Excel book work with it in the UI and do clicks and all this so we read it into a data table and a data table also got rows and columns and almost um almost um appear as the same as an Excel book and that's convenient first you click save here we also need to close the instance so every time we have launched an Excel instance we also need to close it so I go up here say close and then have a close Excel for now we will not save any data of course we will save data back because we're going to web scrape data to it but one step at a time so here we just close it like this then we can run it and what I want to see here once I click run is that we don't get any the errors that we launch the Excel we read it and we also close the Excel that's it so that's the first success I also want to see 10 rows and eight columns over here so if I double click here here we have our data and I told you that a data table behaved a lot like an Excel book can see we have rows and columns the only thing that differs that is that we have this header row here in Excel this is Row one but in a data table this is not a row per se this is just a header Row the first data row is a index zero and that's because it's zero index and this also mean that it behaves a little bit different than the Excel seed let me open up the Excel sheet let me resize it so we have it here and here you can see that in a data table the First Data row is index zero in Excel it's the second row so it's two ahead in the Excel so once we read it into a data table there's no link between the Excel and data table of course it's easy to see that we just need to that row zero here equals to row two here we will fix that later on once we have data we want to write back to excel so now I can close but and let me also open up the Excel book once more let me go to another sheet called anenson orc try to do this and this is just data about me for example feel free to connect with me on LinkedIn um I post a lot about automation chat DBT automate desktop try to click save now here close the Excel book and then try to run the automation once more now we are launching Excel reading it from it and closing it and right now what I want to see here is that over to the left and here you can also see that my Excel is a little little bit slow that's just sometimes how it is here in the editor I want to show you that there's nothing wrong with your automation it happens once in a while it's only here in the editor and that's just because the Excel is a little bit slow so let me fast forward uh till it stops that's it mine took like 1 minute and what I see over here to the right is 31 rows and three columns I read the incorrect sheet and that's because it was the last active but imagine that I wanted to automate this in production then it's not convenient that we cannot refer to the correct sheet let's imagine that a colleague opens up another sheet saves it and then automation crashes or even worse calculates on the wrong numbers so what I can do here is to click close then we want to say I want to read the position sheet and again I'll create a variable for this so I'll find a set variable and have it up here all variables where uh things can change or if I use it multiple times I put those one in the beginning of my flow that means that they're easy to come back to and change so we will give it a name we will call this one active sheet and call this position then we will say once we have opened up the Excel book we will say set active Excel worksheet and drag it in here we are working in the Excel instance and we're activating the worksheet with the name yes we could have just say position here but because this worksheet name could change then we prefer to use a variable for it so now I can just refer to the active sheet which has the value position so it's literally the same we're just using a variable for now now um we have uh this going for us this means that we will activate the sheet called position and one once we have read it you can see again over here we have 10 rows and eight columns I will again fast forward till the end of this automation that's it so what I see over here is that we have if I double click here we have read the correct data now I want to do browser work because let me again open this data table now I want to iterate each one of these assets I can probably use the tiger look that up in for example Google Finance scrape the data and write it back to the correct place so let's do the browser work first let's open up a browser and here I want you to navigate to google.com SL finance and hit enter this is the page that we want to automate here I can do the searches for example I can search for msft that will be Microsoft then I can hit enter and we have the Microsoft stock price which is 409.1 want to extract that data and write it back to excel again one step at a time can see it also it's live at the moment that's fine we will build an automation that gets the data right in the moment even though it shifts so what I want to do first is to grab this URL up here like this so I mark it and say contrl C then I want to open up the browser I want to do the searches and then we'll do the web scraping afterwards so again one step at a time I will use this URL um it can change I will use it at least two times and once I want to use a URL at least two times in a flow it's often a great idea to make a variable so again I'll find a set variable it's not necessary but it's best practice so again I can drag it in here to my starting variables I will call this one URL control V paste in the URL say save now we're going to launch a new browser so go find launch and pick your favorite browser this is the only action where we use different actions if you choose Firefox or Edge and the rest of the actions will be exactly the same I choose Chrome I put it in right here before launch Excel we're going to launch a new instance and then the URL we created a variable for this so here I double click the URL we can refer to we can refer to this and that will get you this value the windows state will again be normal fine you can also choose maximize if you want to see it maximized then the variable produced is called browser so we can refer to this variable when we want to automate in this browser instance so then we click save now what I want to do if I have this Google Finance open then I want to automate this one here I want to do some searches in case I don't have it open then it can sometimes be it takes a long time if I need to either do it manually or run the entire automation I can set what we call a break point so if I click this launch Excel that means that when I click run now it will open it will only perform the first four actions and then once it reaches here it will pause before it performs it that means that I now have it open I can go back to the flow click stop I can click here to remove the breakpoint again so this is great way to just run a a partion a portion of your flow but what we're going to do is to iterate the Excel data this Excel data was the table here so that is position Excel data and then we want to iterate each row and do something similar to each row so we want to repeat the process here go find a reach and drag it in below here the value to iterate that will be the data table that we just mentioned the prision Excel data and now you can see let me click this x now it's convenient that we have named our variabl as well then it's easy to figure out which one is which then there's store into that is because we do the iteration for each one of these rows so then we can give it a reference name we can pick the names ourself it doesn't have to be a certain name I will just call this one C and stock as it is stuck in each one of these rows you can also say q and row q and item also works but again C stuck describes the data that's in that row I'll say save so now we iterate to it what do I want to do well I want to na move to this position Excel data then each row it will be stored in this Q stock and then I want to refer to the tiger column that's where we have this msft and a I think apple and so forth we can do the searches to do when we want to type on a web page we find a populate text field on web page and here we're working in the Brows instance that is fine then we will create a UI element a UI element is just something you see on the screen either on a web page or in an application so if I click here I'll add a UI element and then when I'm in the browser you can see that these there are red borders around elements that I can interact with since we're going to interact with this input text I choose this one so once this is red I press control on my keyboard I left click with the mouse and we have created a UI element fine then I also want to say what do I want to type here yes I could of course say msft but what would happen here is that it will type MST msft for all 10 iterations not nice no we want to go into the curent row we call that one curent stock click the sex double click the curent stock I also want to say which column where the data is I can use the Tigger or the name to find the tiger I will say a hard bracket single quotation mark then I'll say ticker single quotation mark and a hard bracket so here we are typing in this curent stock ticker and I click save let me also show you one more thing so if I go back here that means that if I type one stock in here msft and then I want to click enter we haven't made that yet that means that this search box is gone is gone but there's another one up here but it's placed differently that means that this got most likely another address so what I want to do after each one of these searches I want to move back to the front page that means that I can either go back or simply just move back to the URL once more then we on the front page so at the first of each one of these iterations I want to find a goto web page and drag it in here we will test it in 2 minutes so here I can just navigate to the UR we stored that Ur in a variable so I can just click this x scroll a little bit down if you don't see it to the URL double click it we have it here so yes we are naviga we opening a browser in this URL and then the first time we navigating to it again but after that we will just navigate to the front page for each one of these iterations then we also need to copy what we human do when we did when we this search I can either click here on this element or I can simply just send an enter click on my keyboard to move inside here but there's a trap here and that is nice to see so if I go back if I'm here and I've typed in msft I can click enter here but if I click away and try to click enter it will not perform the search in a popular text F on web page per default power toate desktop takes the focus away from this search box that that also mean that I will need to go into the poate text field on web page go in advanced and unclick this unfocus text box after filling it then I click save now we can send the enter click usually you can also click on the search button but there's no search button present here so what I want to do here is just just to find a send Keys here and drag it in after the populate text field on web page so here we're just sending into the foreground window I want to insert special key I'll find it miscellaneous here can see I can insert a space or an enter then we have the shortcut for an enter click that will be return in curly brackets we could of course have ridden it ourself but again we are lazy then I click save now I want to test it once more and this time I just set a break point here at the end then we can see for each one of these iterations that we actually navigating to the correct stock and we don't have to navigate to all 10 Stocks by the way I didn't introduce myself I'm an Yensen a Microsoft most valuable professional in power automate I use power automate each day and yes it is my favorite tool that's it so we navigated into the apple and we got that price that also mean that if I click run once more you will see here in the background that it populates and then we're in the Microsoft now for each one of these iteration we want to extract some data so I go up here and click stop and again uh I can choose to remove the break pun but let's just let it be for now so what I want to do to extract data then I want to find and extract data from web page and make sure you pick the one here on the web data extraction we want to put it after the send keys after we have made the search so again we are in the browser to activate this you need to open up this stock where we can find the price so here we have it can see here the diff element once I'm happy with the selection which is here make sure it's red then you can right click and here you can say extract element value this will not create an address based on the actual value but the elements place place in the HTML code so it will create a selector and here you can see the green border around it and we can click finish let me just do this right now it's called variables produced let's give it a better name I will call this one stock price and click save I can also choose to right now you can see here we have a couple of browsers open we want to as automation developers we also want to clean up after ourselves that mean that we want to close programs again so let's just find a close web browser and drag it in here in the end after the for this will just close the browser instance that's it so now we can run and inspect it again what I want to inspect now that is will be the stock price I already know that up here this works so I want to inspect the stock stock price variable after we have done the search so we're going to the web page we are populating the text field here we are performing the web extraction and if I click stop on the automation what I want to inspect over here in the flow variables that's the stock price if I double click here over here one rows and one columns we see that we have the results which is fine it is in a d data table though and that is just the way an extract data from web page works this also mean that if I want to refer to this this is in a table format I need to say I want the first row and again that's index zero it's programming so it's zero index is also the zero index column so the next one would have been one so I can refer to this data this value down here as the stock price and then 0 0 I also want to get rid of this dollar time sign and that's because I don't use that in my Excel sheet that's usually what happens when we get data from one system here's a web page that is it's in a different format that we don't use so let's get rid of this and what I want to do here is to have a get sub text here and have it right after the extract value so I will do this for each one of the extractions in this for each the original text that will be the stock price so I double click here and then I wanted to say 0.0 it actually might work in a one time one data table just by writing stock price but for best practice let's refer to that correct cell by saying 0.0 sometimes you will have more cells or columns in or more rows or columns in your data table then I want to say I want to start from the second position I want to get rid of the dollar sign that's how it is in all these scrapings and again it's zero index so that will be position one as the first position is position zero the length here that will be the end of text I will just scrape I will just get the text whatever how long it is if it's a really long big number I want everything then we can write it back to the stock price variable that means that it's a data table up here and then once we have getting the subtext it will return a text variable you can do this in power automate desktop the variables are not uh definitive so you can change from a data table down here to a text value I can click save I also want to save this stock price for each one of these iteration back to our Excel so I go to my folder so here in case a let's create an output folder and that's because I want to have a folder to put in my output so I right click here say new folder here I'll say output that's the folder where I want to um save to so after we have gotten this stock price we need to save it to excel in the correct cell we know that it's column let me just open it again here in the input we know that it's column D so that one me know but there's no link between the data table and the Excel so for the row number I just need to say well if I'm in data table row index zero then I'm in index two or row two here in the Excel to do so we're creating a helping variable so find a set variable and drag it in in front of the for each we will call this one row number and this is just to keep track of where we are in our iteration I know that the first iteration of the fre that will be the zero index that will be index zero in the data table that corresponds to row number two in Excel so here I will have a Tracker that tracks where I'm at relative to the Excel row numbers then you'll find an increase variable and drag it in in the end of the for each this variable name we're going to increase the row number by one so pick the row number here in the variable picker and say increase by one one now we can write back to the Excel and what we will do here is to find an a write to Excel worksheet drag it in in in front of the increase that's very important not after it so what are we going to write well we're going to write the stock price so click this little X here double click the stock price we're going to write on a specified cell we know that it was D column and we also know that the row number so for each iteration we want to use the row number and that one will change so it will be in the correct position in this Excel sheet then I click save now when you run it you could think that you have written it to the Excel worksheet but actually we haven't saved it yet so what I want to do is let's just remove this breakpoint I want to take this close Excel market then drag it all the way down to just before the close web browser and then I want to create a variable for the path that I'm going to save I'm creating a variable for this because uh I'm going to reuse it several times I'm not going to create it up here that's not necessarily I can just create it down here and that's because it will only change in the flow so I can have it here before the close Excel this let's call this sorry out put Excel p and let's talk about where we want this to be outputed to so I need I know I need to put it in the project path and then I also need to put it in the output folder we will add a couple of things to this later on but for now this is fine so and then I could actually also added right now the Excel book name instead of having it in the close Excel so I'll have the or updated portfolio. xlsx and I click save this is the part where I want to the path where I want to save it and let me just double click it again so I combine it with the project path then I move inside the output and then I pick the Excel book name then open the close Excel right below say save document s and here we use the newly created variable called output Excel path now you can click save and let's just save the floor for all time sakes just so if the computer crashes hopefully it won't it's really rarely my computer crashes then we will click done now we will do 10 iterations where we will do searches I will fast forward to the end here so let me come back now we are almost done and please if this video helps you please give the video a thumbs up that will really help me and my channel a lot I have a dream about making a living out of this YouTube so your like will help me a lot thank you now we are at the last iteration we saw that it closed and let us go back here we're not in the input so let us go back to the output here we can see we have the updated portfolio and now Bingo we have the q& price nicely written in on the correct places then you might see that um when you try to open up this Excel book that it's gray out so everything here is gray if that's the case that's just a minor Windows bu to solve this if it's gray really and it's not giving you an error message that's important then you can double click this Excel and choose to make instance visible then you unfortunately got a h an unlucky computer window Windows instance so you need to do this each time you automate with power toate desktop I don't have it so I can just leave this unchecked in case this one is completely locked that means that it says an error message that you can only open it as read only or something is wrong or with your input then the way to solve it is to go to start search for and this is a general run if your power toate desktop locks the Excel instance then I can go to the powers shell I can right click when I click run as administrator my screen goes a little bit black until I click yes and I'm in Powershell and please bookmark this because this will be important for you when you want to force close your Excel when power toate desktop sometimes accidentally locks it you can also restart your computer or sometimes use your task manager but this is the bulletproof solution I'll write task kill which means that we will kill the task either by an image or a PIN number here I'll say I'll do it by force so even if the program doesn't respond I will just completely shut it down this also mean that it will shut down all your Excel instances so if you have anything open please save it I'll say a forward SL and I want to say I want to close an image I want to close the image called excel. EXO right now I don't have any Excel instances open but imagine I had it could look like this and once I click enter here let me just come back to here now it disappeared um let me just write it again so uh that's with these live codings I hope you enjoy them but and sometimes they just uh like this then I click enter you can see that my Excel book in the background closed that's nice so that's the way to solve when an Excel book locked usually when I automate excel at least when I develop I just have this minimized because then I can come back I can just use the up Arrow to find the command again hit enter again right now it cannot find any excel. exit that makes sense so minimize it and have it right here in your taskbar so if I ran this automation once more that means that it would overwrite this updated portfolio here it will so we want some types of type of a prefix so we just have unique outputs each time so it doesn't um overwrite these things we can use a day time stamp so I can use the year uh month month days hour minute seconds in front of this updated portfolio so what I want to do here is in front of this set variable I want a get current date and time and drag it in this will take the Q and data and time once your power automate desktop flows reaches this point so here I can click save and then I want to convert a date time to text so here say con convert date time to text and drag it in that is because it is a date time variable and I want to use this date time variable in a text format that will be my file path I need to convert it and also need to convert it to a standard that I can use um file path doesn't accept colons or slashes so the date time to convert that will be the curent date time here in the format choose custom that is because we can custom the format just as we want here I want four y's 2 M's 2DS a hyen let me move the mouse two Pig a that's hours in 24hour format that's nice when we want to sort then I want minutes and seconds this is by the way the net custom daytime format string so and if I want to know more about this and how I write these things out then you can search for net custom date time format then you choose this one here custom dat date and time format. net scroll a little bit down and this is how you use these letters here it's the same in Excel for example two big Sims that's the month from 1 to 12 I can choose to also have for example three FS small FS that's uh milliseconds if I wanted that for now it's good now you know where you can find more information about this this variable is getting saved in a variable called format datetime I click save here now I want to use this format dat time in my output Excel path so I double click to open it I want it as a prefix to my file name so here in the updated portfolio I click this x double click the format date time and let's also place an underscore outside the percentage sign so now I have a timestamps here I click save that also means that if I run this automation once more we will not have this overwritten we will create a unique Excel book um at least a unique name and that's convenient so we never overwrite a lock or anything else and it's also nice that um we have a Tim stamp so let's say that we build a lock then it will also be unique so never delete a lock or at least have a rule for that in case you want more help with all of this then I created a Discord Community called I love automation here we are 11,000 RPA developers networking around our careers helping each other with Solutions it's 100% free to join and the link is right below here in the description so we are uh soon done I always I think it's so satisfying to see my automation run even I mean year five or six as an RPA developer and also use Auto hotkey before that but this is so satisfying to see the mouse or the typings and all these things and also see the Excel books that's getting created here so now we are we closed if I go to the folder I can now see that I have this time stamp here this is very nice of course the data is the same or at least the qent price might have went a little bit up or down um but so mission accomplished now I want to do excel calculations so this means that in here for each one of these curent stocks I wanted to calculate let me show you what I want to calculate the total cost that is buying price times quantity market value that will be curent price times quantity and again profit loss that will be market value minus total cost I want to make these calculations for each one of these rows it's very important to know how to calculate stuff in in Excel with power tomate desktop or just calculate in general with power toate desktop we have 22 actions already now we can see that we have a lot of actions and imagine that we create more now then it can be a little bit hard to see what's going on here we can structure them a little bit better one of the Rays that we can do it is by a region let me move this region in here we will move it after this right to Excel worksheet we will call our region Excel Cal cations and then click save this region um just stores some actions the region itself like the two yellow ones they will get ignored at runtime so this is just to structure your flow better let me show you how smart it is because now we can drag actions in here and yes they will be performed just like in other actions so it's only two um to make it a little bit more clean looking and that's will also uh mean that it's easier to maintain when we come back to the flow let's first um we need to convert these elements to numbers so the elements that we have here in the curent stock even though that this buying price and the quantity they look like numbers they are text in um how toate desktop meaning that's because they come from a data Row in the data table so we need to convert them to rows before we can calculate on them the same with the stock price so I'll have a convert text to number like this the first thing I want to convert to a number is the buying price the buying price lies in the data row called Cur stock so I go up here click the X click curent stock go inside here and then I want the header in hard brackets and a single quotation mark so here I'll say buying price in here so this will be the buying price I will also say variables produce produced that will be buying price you cannot have spaces in variables for several reasons um so you need to have it nonpaced so this is the buying price click save and again we are lazy we need to calculate two more H we need to have two more convert text to numbers so just click here say crl + Ctrl + V two times yes we have repeating it three times so now let me move inside the second one well now we want the stock price yeah that could be uh so we only saved a little bit of time I delete all this then I just click the X stay stock price down here in buying price find the stock price once more so we're just converting stock price into a number and saving it to the same variable and again if you click here you can also see the percentage signs I can click save then in the third one we want the quantity one comes from the data row so here I'll say [Music] Quantity make sure I spelled right otherwise it won't work so here I have the quantity quantity like this now we can do our three calculations and write them back to excel let's take the first one so I'll have a right to Excel worksheet and drag it in underneath here we're working in the Excel instance so the value to write the first one and let us just repeat what we wanted the first one that was in column F then we have the GH and here we want total cost market value and profit lost so let's wait a bit with the value to write we know that we were in column F we also know that the row number number that one is stored here in the row number so I can also fill that one here and then I just need the value to write to find the cost I need to times buying price with quantity I created two variables for those here so I just click the X and say buying price to make a calculation in power toate desktop I need to move inside two percentage signs that also mean all the variables inside a code block surrounded by percentage signs we don't need to have more percentage signs around these so I can say times with an aisk click this X and then I also wanted the quantity double click here and then as I said we need to remove these two percentage signs so this is the calculation I can click save then we are repeating this one two times so almost so mark it again so it turns gray contrl C on your keyboard control V two times move inside here now I'm in G and the calculation now that will be the market value so instead of buying price that will be the stock price times quantity here and here and click save then for the last one that will be colum H and now I want to say buying price um stock price time quantity minus buying price times quantity so I already have something here so what I can say here is to find the stock price again I'll remove these percentage sign then I will have space when I have spaces in these calculations they will just get ignored by runtime so let me just say stock price I meant times quantity and again remove these and then I want a minus here oh sorry I think I have the insert one uh um accidentally collect here so now I have the stock price times quantity minus buying price times quantity I could set a parentheses around stock price and buying price and only have quantity once but for now this is fine if you get an error here that means that you have just misspelled something or you have a percentage signs too much go back and fix it after this again let us save it let's try to run the automation once more we will wait a few seconds and then we can click run when we come back here then of course we will inspect that the calculations have finished well but I also want to show you how to use the co-pilot that means that when we don't have enough enough actions in pomate desktop then we might use want to use code that could be VB script Visual Basic script python or Powell but we might not know any code this is a low code tool but with co-pilot I can just use my own words I don't need to know any code to automate this so I will show you I will just fast forward till the end of this iteration that's it that was the last part here I will close the web browser let's just first inspect the result and here you can say that um it um it had created a new Excel book so if I do this here we have it we have the total cost we have the market value and a profit loss nicely calculated now I want to create a chart I cannot do this natively with power automate desktop action so here I want to use a script let's say that I don't know anything about scripting or coding I do but yes it takes a long time for me as well so we can automate this with co-pilot I want a pie chart of the market value column with the labels over here and I want it to be placed here so it doesn't um it doesn't shade for any of the data so I want it to be placed in the I column let's go create that and let's do it with the co- pilot so when we do this after this close Excel we will run the script on the data so that means we need to have the data first and then we can run the script so go over here find a run VB sorry that's my spelling run VB script Visual Basic script move it down below the close Excel here I could of course put in the script but click this generate script with co-pilot here with our own words we can describe what we want to have a script of so here I say read all data in a specific sheet in an Excel file this understands a wall of text but I sometimes just like to do this to structure it for myself then I want to say create oh sorry create a pi chart of column G data use column A for labels then I also want to have a titled market value that will be the pie chart and here I can say start chart from column I and I also want to say uh save close and quit Excel um just to have Excel closed conveniently then I say generate then it will take like 5 seconds and we will have a script ready ready and or maybe 10 seconds here we go if I just move this a little bit up do this here we have a script and here we can see something that we use a path we also have a sheet that we're working in and we have a pie chart here let's say that and it also have uh some comments here it might be that I want to add something that I'm not satisfied with this code let's say that I have some knowledge I can click regenerate I know I don't have changed something but it will still make a new prediction so here you will see that it is someone changed it still has has these these two placeholders but let's say um I can click regenerate a lot of times it will make predictions that's how a large language model Works which this is based on let's say I'm happy I can say use this script and now you can see I just need to fill out this placeholder for the Excel path and the sheet name I can do this by either hardcoding in but since we have created variables this one here that will be the output Excel path that one we just saved here to this part and by the way the Daye time is fixed so it's not changing through each one of these actions we took that one up here if you're thinking about that so here I say output Excel path make sure you have the quotation marks the sheet Mark that go up and find the active sheet that's all we need need to change sometimes we will have to work a little bit with these scripts but usually it's not um something I need to worry about I can just click save here and let's go test it and run the automation I will again fast forward uh so we don't need to see 10 iterations I just fast forward to the VB script running here we have the second last that will be Johnson and Johnson and now we will have the Paramount yes we know the stocks uh I think it's the fifth iteration now in this project so here we're running the VB script and then we're closing the web browser if I go back to my last Excel book here we have it in column I we have an nice pie chart of our data with the labels on so far so good now I I want to make sure that this uh nice sheet is sent directly to my Outlook inbox so let me just don't save it so now I want to save this one here each time this robot runs I want to send an Outlook email here I want to include some nice uh datetime and I don't want to use this format day time here for the file so let's do another conversion so here I want to find a convert date time to text this is just to get it in another format so I can use it in my email so it looks nice I'm still using the current date time I'm taking the custom and then I want to uh think about how I want to format this that could be for example months oh sorry days years then hours in a 12h hour format months and minutes and seconds sorry and I want to save it in form a day time email when I click save here so now I have some time stamps here and this could be a great time to introduce a comment again comments were to explain different difficult Parts in our flow they will again get ignored in runtime sometimes when you search for something it doesn't show up it will be in this category so you can find it here and I will go down here what this does is that we can say generate two timestamps file naming and if like this and then we say first file naming and second for email then I can click save one other thing that I see here is that now I have a formatt date time for email I named that one well and then we have a formatted date time I want to call this format date time for um for file and this is by the way an act an error we see sometimes in power to my desktop just close it so now I want to rename this but then I need to rename it in several actions the best way to do this is to go over here to find the formatted datetime right click on it say rename then you want to say for file that means that when I click enter here it will get updated everywhere in the flow so that is a more bulletproof way I want to launch an Outlook so let's go up here in the initialization phase and we can have an Outlook up here so before the edge I will have a launch outlook here and drag it in here so launch a new Chrome we're launching an Outlook instance we're clicking save so I want to have all my initialization all the opening programs in the beginning then it's a more natural flow then after this close Excel you will find a send send email message through Outlook make sure you pick the right one oh sorry after this VB script we can attach uh we can send the email and attach the file so here it's the Outlook instance and again here I'll say account that will be your data account most likely email or otherwise you can check an outlook here I want to send it to someone I just pick myself usually that will be some someone else if I use this twice I also said that if I use a value twice in the flow I need to create a variable so let's go do that just to make sure that we always practice these I know it's annoying sometimes and um just do it because uh if you force yourself to it your flows will be much more stable this one we will call email address and here I will call this one Anders anenson org you should of course use another email so you don't bombard me with an email if you like this video you're more than welcome to send me an email and tell me so here I can use this email address down here in the send email message to Outlook now just do this one here we'll have uh this this one here then we have a subject you can choose whatever you want you can say your portfolio bot RAM and then we can have a body then we can say the port Bodo bot ran on and then we have a timestamp and again I can format this exactly like I want this what was for the file this one was for the email let see now say attached is your updated Port Folio and then I need to in attachments I need to attach a file we already have that we have that here in the output Excel path so I just clicked SX here I'll say output Excel path and click save now we're also sending an email so let's go save it before we test it and uh soon we have a really cool flow I will start the flow and then I will come back to the end when we reach Paramount so we don't uh see this for the sixth time see you I'm back we're searching for Paramount and what we want to see is that we don't get an error in the flow and we are sending the email message so the first thing is accomplished let me move to my other screen and here I have the email um here it says that the P portfolio but ran we have a timestamp and here we have an attachment which is um the updated curent prices and the calculations along with a piie chart so far so good let us move on to the second part of this let me also close this of this exercise that is to work with files now if I go here then if I go to Project I also have a case b and here in the input I have portfolio one that is like the data that you just saw I also have a portfolio two with just five stocks in it but it's it follows the same structure with the same um column names I can take all files in this one here and then I can say I want to uh do the same so for the portfolio one and portfolio 2 I want to do all of this so what is it that I want to do well i' only want to launch Outlook once I only want to launch Chrome once but here I want to launch Excel I want to launch an Excel book called portfolio one portfolio 2 also want to if we had a portfolio tree someday also make want to make it work there so first thing go up here and change the case a to case b and click save now I want want to get all the files in that input folder so I go up here and then I say get files in folder it's here I want to um have it right after so right after the launch new Chrome but before the launch new Excel the folder where I want to grab the files that will be the project path and then in the input folder here I could also say I want to have a file filter that wants probably sometimes I just want to just have the Excel files that will make sense here but let just let it be we only have excel's files in that folder so it will work for now but you can make filters then we can include sub folders if we had any this will get stored into a list called files so it's a list of file names and when I click save here we can try test that it actually works so if I set a breakpoint here and launch again these break points are excellent so we don't need to run the entire Automation and when I click run here you will see that we open Chrome and then we get the files in folder I can close Chrome I can stop this and what I want to inspect is this files variable so click the files over here and there you go we have portfolio one and we have portfolio 2 now we can iterate to this list and just repeat all the actions over and over so we can make an outter for each let me close this and let me move this so I want to have it for each starting here before the launch Excel so find a for each and again you can find it up here I have it here and the value to iterate that will be the files double click here and then I want the call it curent file this is again a reference name that uh reference to the file that we're currently iterating I'll click save now I want all these actions inside this for each but I could of course drag them in the easiest way is to click the end go all the way down uh just before the close web browser I want to have it um here so then uh what I then want to do is to um talk a little bit about what file we're opening now we're not opening the portfolio now we're opening the current file that will be the file name so I will delete all of this and just have the curent file I actually press cap lock there Q file and click save I also want to say well now I don't want to call it updated portfolio now I want to have whatever is the curent file whatever that file name is without the extension that will be portfolio one for the first time and portfolio 2 then I want to call it that so let me go in here so in instead of we're using a variable for this this is convenient since we're using it in the close Excel run VB script and send email to Outlook so I just need to change it here so instead of this updated portfolio delete this then I can click this x scroll a little bit up to Q file expand it and here you can find name without a curent file name without extension here we have the file name without an ending so now we're combining a lot of stuff but this will get it right I'll click save so now we do the iteration and we also giving it the right name I also want to whenever I iterate it through a file that will mean the outer for each then I want to move that file into another folder that is just to not have to process the same uh Excel file twice imagine that these files are automatically getting placed there by colleague or another automation process so let's create a processed folder like this and does also mean that in the end of the out of for each that will be here we will move the curent file into this processed folder so here we are saying move files here so now we're working with the files have it here and then the the files to move click this little X pick the current file so that one will move the curent file where do I want to move it well I'm want to move it into the project path I'll have it backwards slash and then I will say processed if the file exists I can choose to overwrite it most likely will because we have a time stamp we're not having a time stamp but it most likely will if we're just moving the these files over and over the varable is produced called move files I'm not going to use that so untick it best practice is to untick the variables that you're not going to use now I can click save let's go test it so let me save this and let us try run it what I want to see here that it starts well that we can uh do the searches for the first file that we can actually uh here we have the first one that this will be the same one as before so fast forward till we come to the next Excel book and also see this works and by the way if you haven't liked this video yet please do so it will help me so much thank you let's fast forward now we are at the Paramount so it will be exciting to see if we can actually move this file and now we're running the VB script for this we're moving the file and here we go we're opening up the portfolio too now we should search for some different stocks here we have the Walmart it looks like this works so we have created an automation that now can take multiple files and move each uh each time a file is processed then we move it to a processed folder we still need to check for this we only have five stocks in here so we will stay and now we have the Intel C I think this is iteration 30 or4 coca colola KO that's shortening here we have it and we will check a couple of things so it closed down with errors first uh thing that's nice then we move to the folder now I want to see that I have portfolio one and two here in the process so far so good here and what I also want to see is that um and now you can actually see here we're moving the curent file into these process that is fine and we also have it in the output and then I also want to see that I'm getting sent two emails I have them here so now I have two emails each sent with so this one is the first one I can check it there we already saw this work this works and this works Isn't it nice you have completed a complete guide on power toate desktop now you want to learn best practices in this advanced guys guide you and I will build a complete flow that will teach you all the best practices see you there

Channel: Anders Jensen

Views: 37,799

Keywords: Power Automate Desktop 2024, beginner tutorial, Microsoft Power Automate, automate Excel, Outlook automation, web browser automation, web scraping tutorial, file management automation, Excel charts with Copilot, automate tasks, workflow automation, drag-and-drop automation, low-code solutions, data entry automation, email task automation, data extraction, document organization, learning Power Automate, automation skills, tutorial for beginners, Power Automate guide

Id: B0cHOdBv7Jg

Length: 81min 3sec (4863 seconds)

Published: Sat Mar 16 2024