Power Automate Desktop Tutorial for Beginners | Zero to Hero

thank you for watching you've chosen the most complete power automate desktop tutorial on the internet my name is Thomas Janssen and you're watching Tom stack Academy this tutorial consists of seven chapters and in every chapter you will build a real robot with power ultimate desktop let's not wait any longer let's start right away so let's start by making sure that we've installed power automate if you win this 11 installed on your computer you've already installed power automate and you can check that by clicking on the Windows button type power automate I will then see here power ultimate if you're still running Windows 10 and you can check that by clicking on this PC properties and then you see here that I'm running Windows 11 but if you're still running Windows 10 you will see Windows 10 here if you're still running Windows 10 and you have to navigate to this website and there is a link to this website in the description of this video scroll below and then you click here on download the power automate installer you just download this installer you click on the executable to activate the installation process and you walk to the installation process now in both scenarios where you already had power ultimate installed on your system or you just installed it going to navigate to the Windows button search for Power automate I will see Power automate popping up here clicking power automate and you will see your next challenge as you can see my power automate desktop is currently in Dutch and we are going to change that to English because otherwise all the activities are going to be in your native language and this tutorial just like any other tutorial is using the English activities and in that case it's going to be really confusing for you so what we're going to do is we're going to change the language of power automate desktop so just close power to my desktop I'm going to search for the language settings you see currently my computer is configured in Dutch so click here on the language settings I will see here that I have installed both touch and English if you don't have English installed just click here on add a language search for English select your favorite English mine is US English I just click on next to follow the installation process once you've installed the language package you're going to make sure that the English language pack is above your native language so just select it and then put it like this and then let's close this one in some cases power automatically will automatically pick up your new default language in some cases you have to restart your computer you see my is still in Dutch so I'm just going to restart my computer okay now we've installed power automate desktop and change the main language to English let's stop it creating our first flow in order to do that I'm going to navigate to my flows and on this page you will find all the flows that you've already created basically for every process that you create and for every dummy project that you create just create a new flow that's the easiest way to distinguish them so let's click on new flow and then I'm going to type power automate desktop tutorial plums Tech Academy just do the same and then click on create now you will land in the power ultimate desktop designer and this is where you could actually build flows in the center of this page this is where we're going to build your robot on the left side you see all the activities and we're basically going to build this robot by dragging those activities to the center here you see the variables those are variables that you use in the flow don't worry if you don't know what they are you will learn about them later for now let's start with a really simple flow and in order to do that I'm just going to type display and we're typing in something here it will filter on the activities that contain this word I'm going to take the display message and I'm going to drag it to the center and just leave it it will drop it here and now you can configure the properties of this specific activity so you will see we can provide a title for the message box let's call it my message box we can also give a message to display so let's set the message to display to hello world exclamation mark here you can change more properties I'm not going to discuss all of them right now and just click on Save when you're ready so this is how you can build a bot we've now built a first bot already in order to run our Bots you can click here on run you'll see that the message books will pop up see how my message books that's the title and you see hello world that's the message good to know if you use message boxes and I use them on a lot in my flows especially to debug sometimes they get behind one of the other screens of power automate and then then you just have to click here to find them okay but for now it's on the front so click OK I will now see that the flow stops running and we can edit the flow again it's important to know that you cannot edit the flow once you're running it so you have to make sure that the flow has finalized before you can edit it one thing that's also good to know just go back to the main page go to home navigate to settings if you want to stop a flow that's running you can click Ctrl shift alt and T and the flow will automatically stop if you want you can also change this shortcut but for now I'm just going to leave it like this and let's continue with creating a real bot so I'm just going to remove this display message you can just click on the delete button or you can use the right Mouse button and then click here on delete and let's now start building a real flow in order to do that I'm going to open my browser Chrome and I'm going to navigate to a website that's called powerautomatetorials.com This is the website I can find a link to this website in the description once you have landed on this page click here on power automate desktop beginners course that's of course you're now following and then this is the page that we're going to use throughout this course you'll see that this course has been divided into multiple chapters and for every chapter you can find to the material of that specific chapter here I'm going to click on chapter one your first process click here on the button and you will land on the page and this is our first dummy application that I designed just to give you a really simple application to automate to build your first process how does the process work you can order a product so you can either order a laptop a PlayStation or a graphic card then you have to provide a quantity that you want to order in this case I'm going to order two PlayStations and you can choose whether you want to receive a free cap or not and I think you should always go for the free cap so I'm just going to enable it you click submit and then you will get an order number this order number will also differ every time that you run it so let's go back make sure that you on this page and then I'm going to refresh it just to make sure that the form is empty and let's now go back to Power automate to see how we can automate this flow so make sure that you open this power ultimate screen not the main screen because we're not going to use that remove the filter here and to start with I'm gonna search for Chrome I will see that there is an activity that's called launch new Chrome if you prefer Edge or Firefox or any other browser you can of course also go for those ones but in this tutorial I will mainly use Chrome and that browser works pretty well with power automates a large new Chrome just drag that to the center and then you can choose whether you want to launch a new instance or attach to a running instance I'm going to launch a new instance and you can also provide a URL URL is this one so I just copy it from here now let's go back to Power automate and then you can use space here to paste it you can also choose if you want to maximize the window let's do that just to try it out and then you can also here provide some other options if you're interested in those for now I'm just going to click on save one thing that I also want to mention is that this activity will also produce a variable which is called browser and this variable you'll also see it back here this very variable is really important because if you use multiple browser instances let's now let's just add another launch new Chrome now you will see that the variable produced is called browser 2 and if you use any other activities you can choose if you want to use browser or browser 2 so that's a way to distinguish between which instance of the browser you would like to leverage um let's remove it again so once I've initiated the browser I'm going to continue with the next step and as I am automating in the browser that's called browser automation if you're automating in Windows that's called UI automation so I'm going to open browser automation and then you will see all the activities that you can use in the browser if you go back to this website you will see that the first activity is a select item right here we can select one of these options and the second one that's where we want to type and then we get basically what we call a checkbox and a button okay so let's go back to Power automate I'm going to expand web form failing because that's basically what you do if you provide inputs to this website and then I'm going to go for set drop down list value you will see that browser is referring to this variable here so if we add multiple browser instances we could also change this supervisor to a browser tree um in general you you mainly will use one browser um let's pick the UI element so click on ADD UI elements and then you will see that this UI element picker will pop up I'm just going to drag it to here and then you can basically hover over the page and you can select the item where you want um Power automate to change the value so I'm just gonna hover over all these things what's important to know sometimes you have multiple options so for example you can select a larger field and a smaller field as a general rule of thumb it makes sense to always select the smallest one that you can select and if you've found to do right element just press Ctrl and then click together and it will see the power automate will object clone this element now we have configured the browser we have also configured where we want a power automate to do the changes and now we still have to change the operation by default operation is to clear all options that's not what we want we want to select options and we want to do that by the name because the index that's like say one two three four five Etc and the name in this case is the most intuitive so the name that we want to select is PlayStation so I'm just going to fill that in just make sure that you don't make any dipole otherwise this flow won't work and then we're going to click on Save if we go back to this page you will see that the next element this is basically a box where we can type a number like this and in general I really like the intuitive names of power automate so here you will see a populate text field on web page and that's the next next activity that we're going to need and I'm going to drag that to the middle you'll see the power automatic automatically selects the browser and then we have to provide the UI element I don't want it to use this one we could use this one as well but for now I just want to create a new UI element and then we're going to select this one so press Ctrl click and the object learning has been made and then click on Save before we click on Save we're still going to provide the value that we want to type for now let's order 10 PlayStations so just type 10 and then click on Save then the next thing that we want to do is we we of course we want to receive the free cap so I'm going to go back to Power automate and then you see here set box State on web form edits UI element is a new one so I'm going to edit and then make sure that you hover over this one you press Ctrl and you click again okay and bow automate wants to know the state that this element has to be in after this activity run so it can be either unchecked or checked and I'm going to set it unchecked because by default it's not checked and I want to receive the cap and then click on Save okay now there is one more thing that we still have to do we have to click on that next button it's probably going to be a link and for that we we need this one press button on web page so I'm going to add that here and of course this one only needs a UI element because you cannot type anything in this button you can only click on it I'm gonna put this one here press Ctrl click on the button and then click on Save okay this is really important because once you click on submit you will navigate to another web page and we also have to navigate to that next web page just to make sure that we can do the object cloning and that you can tell power automate which element it needs to fetch so you have to click on the button just like me it will end up on this page and then we're going to make sure that we fetch this number it's at 1041 but every time the script runs it will add one to this order so I'm going to go for another activity I'm going to search for web data extraction and then the activity that we need is get details of element on web page so take it and let's show Power automate the elements and as you can see here it's a bit the same right you can find this entire one but if you fetch this entire text you also have to still cut it like yeah you have to cut it in half to say it like this but you can also just fetch this number and that's what I'm going to do so press Ctrl click on Save I will see that by default the value will be stored in attribute value and if you click here you can change that let's change it to order number something like that and then click on Save now there is one thing that I still want to do I want to add a message so display message and then I'm going to say my order number is colon and then if you want to use a variable because the previous activity has stored this information in a variable I'm going to click here on the X and then all the variables will be shown I don't want to browse a variable it's not a pro it's not typically a variable that you can use you can only use it to show to which instance of the browser you are referring but I want to click on order number if you expand them you can see more information for example you can see the length of the order number like how many digits is it is if it is empty these kind of things for now just click on order number because we are interested in the order number itself click on select I can also in front of this variable just make sure that you leave the percentage signs you can say the order number is colon space and then this is what power ultimate uses to refer to this variable click on Save I'm just going to close the browser power automate will open it again and then let's run a flow and that's what I talked about earlier just make sure that you click on power automate and you will see that this display message is hiding behind this web page you will see that the order number is 1042 congratulations on building your first robot I hope you have the same result as me because that means that the flow run as expected if you face any challenges please leave them in the comments and I will try to help you as good as possible click on OK and let's continue with our next chapter about Excel automation okay let's go back to Power automate and you can do two things you can either remove the activities or just create a new project and if you want to create a project that's something you can do over here I'm just going to remove the activities and I do this by clicking on the last one then holding the shift key clicking on the first one and you select them all and then you can click on delete okay let's navigate to our second project and that's step two and in this chapter we're going to focus on Excel so open course material and the first thing you can see here is that you can download an Excel file and this Excel file you're going to need for this assignment so I'm going to download it I'm gonna show it in the folder I'm going to copy it and I'm going to paste it on my desktop and if you open this Excel file and you see that we have employees in this Excel file we have their name their email address their birth dates and um whether they need to be immediately onboarded or not and if you onboard those employees in the application this application if you let the name the email the birth date click on submit they will also get the employee number and the ID of this exercise is that you put this employee number in this column and then of course save this file so let's navigate to power automate desktop and let's start with the first activity and the first thing I'm going to do is connecting with this Excel file so just search for Excel and then you see here Lounge Excel make sure that you insert this Lounge Excel activity by default um Power automate will launch a blank document but we're going to change this to an already existing document and open the following documents and then you can hear select the document that you would like to use and for me this document is saved on my desktop gonna pick it and I'm gonna click on open what's also important is you you don't need Excel to be visible because power automate can communicate with Excel without having it visually opened on the screen and if you don't open it this will actually improve the performance of your robot so disable make instance visible there are some other settings that you can use as well you can for example as well provide a password that Power Admin needs to use for now I'm just going to click on Save and if you open the Excel you also need to close it and by the way this goes for every activity and what's very important is of course that you have to refer here to the same Excel instance this Excel instance it works exactly the same as with chrome or Firefox or any other instance if you open a second Excel file you can refer to excel instance number two for example or number three for now I'm just going to refer to this Excel instance and what is very important is that you change do not save to save document or save document as where you then have to provide the name of the new file click on Save Okay so we've now launched the Excel we've also closed it and the next thing we need to do is we want to read from this Excel worksheet and this is the activity that you need read from Excel worksheet just put it here and we don't want to read from a single value we want to read all available values from the worksheet like this and here you can as well this is very important so make sure that you enable first line of range contains column names because that way you can indicate to power automate that it doesn't have to process the first line the data basically considers the first line as the description of the columns and if you open the Excel this is what is meant with this configuration option you see that the name of the first person is not named the email is not emailed those things are describing below column right so if that's the case you have to make sure that this one is enabled and click on Save and then we want to do a specific action for every row so for every row we want to insert of course the text we're not going to program this activity standtimes but we want to describe the process and then power outage should basically perform the activities for every row and we can do that with a four each this one so just take the four each and put it here below the width from Excel worksheet because you want to iterate through cell data and that variable is has only been generated in the previous activity store into current item and current item will basically contain the value of the current line in the for each so click on Save and I'm going to show you exactly how that works just to show that I'm going to add a display message like this and here I'm just going to say name you don't have to do this I'm just going to show you how this for each works then I'm going to refer to current item select and before the last percentage sign I'm going to add square brackets single quotation marks but if you prefer you can use double ones as well and I'm going to refer to name and this reference is referring to this header so if this doesn't work for you make sure that you um that you enable that setting where you read the first line as a header like this and let's save it and then let's just see if this works so we get Clara Raymond Phyllis Etc okay you now see that we get empty names and I don't know this is some kind of a bug in the communication between Excel and power automate where it reads empty lines and just to prevent this I'm gonna do something I'm gonna add an if activity so you can use if activities for a conditional logic gonna put that here I'm gonna say if current item name with capital n is empty then I want to break from the loop I'm just going to open Loops I want to exit the loop right and this will prevent power automate from processing empty lines so let's remove the display message and let's also add the Chrome activity because we are communicating with the browser so I'm just gonna copy this URL copy it let's go back to Power automates launch new Chrome and what's very important is that you launch Chrome before the four each because if you do it in the for each Chrome will open and close every time so that's not what we want so I'm just going to open it here and I want to launch a new instance this is the URL I just copied it make sure that you refer to chapter 2 and the windows state I'm going to maximize it let's see if there is anything else that's important nope we're all good here so let's save it and we didn't do this in the previous activity but we actually have to do this we also have to close the browser if we open the browser we also have to close it so close the web browser and let's put this one as well below the for each and we are referring again to the browser okay um so what are we gonna do we're gonna write to name email and birth date so let's start with that so let's open browser automation web data form filling sorry and then we want to popularize the text field in a web page so I'm gonna add this one below the if statement and let's show which UI element we want to use Hui elements and I want to refer to the name so press Ctrl again and click here so I'm gonna use current item and I'm going to use square brackets and name with the capital n and single quotation marks click save and let's add another one add UI elements this one is for the email and I want to type current item email with a capital e save and then there is one more and that's the birth dates and that should be current item birth date exactly like this okay then after the text boxes we get this checkbox import immediately and for that one we have to use another if statement because as you could see and some rows immediate onboarding is yes and an orders is no and if it's yes only then we have to activate this checkbox so let's go back to browse automation up form filling and then you should see here a set checkbox state I'm gonna add that already um I'm also going to show the UI element but we still have to add to this if statement so make sure to click here but immediately click save and then let's search for the if statement just if if current item I'm just going to copy this value because otherwise I'm going to make a mistake here so just copy it here would it in power automates if current item immediate onboarding is equal to yes then I'm going to check this value so that I'm going to do this and just like if you can also add an else activity so in any other scenario we're going to copy this one so you can just press on the activity then press Ctrl C Ctrl V I will see that the activity will copy itself but here so if on board immediately is not equal to yes then there's probably going to be no then I want this checkbox to be unchecked um so make sure that you select unchecked and click on Save and the selector can be the same we don't have to change this one click save and then of course we have to click on this button so bus automation a form filling press button and web page and make sure to put this one below the else because otherwise it will only submit the form in a negative scenario so make sure to select the elements in the browser and press save and then if we click this button or either if the bot click this button you will seem to get to this page so we have to do two things we have to fetch this one and put in the Excel and then we also have to click here on atom or employee just to make sure that we navigate back to the previous screen otherwise if we don't do this it will try to type the next employee here and of course it cannot type and then you will get a big fat error so that's why we why this activity is so important so make sure that we add a web data extraction because we are now extracting get details of elements so I'm just going to put this here make sure you put it below the button because we we had to click on the button to go to the screen your element at your elements and then I'm going to click here on control I will see that power item is fetching it here from a span I will tell a bit more about what you see here later World produced is attribute value I don't think that's really descriptive so I'm going to call this one employee number I guess and I'm going to click on Save then we still have to click on that link and if you want to click on a hype link click link on web page this is the activity that you're going to need so I'm going to put this one here I'm going to select the Y elements and is the one you need and a left click that's what it should be right it doesn't have to be a right click double click or anything else just a left click and then click on Save okay now let's make sure that we also write the employee number to Excel so I'm just going to search for write to excel worksheets and you have to put this below the get details of element on web page otherwise it's not going to work because we need employee number of course okay the value to write that's easy that's employee number that's the value we just fetched write mode is on specified cell we want to write to a cell we don't want to write to the active cell the column so if you count this is one two three four and this is five so that's column number five let's put it here and the row I'm just going to put one here just because I have to fill in something you see otherwise I get an error so for now I'm just going to fill in one but I'm going to change this and here I'm going to use an activity that's called set variable and I'm gonna set this variable before the four each so here and I'm going to create a variable that's called counter because the the row where we have to write is increasing after every um after every employee that has been added so counter is equal to two because the first line I want to write to this is number one and this is number two right so that's why it has to be two save and then I'm gonna search here for increase variable this one so in the for each every time this four each runs I'm going to say here that I want to increase counter and I want to increase it by one save it and if you open this one right to Excel worksheet now I'm going to change row because we don't want to write to one we want to write to the current row and that's equal now to counter okay so this one should work we are referred to excel instance employee number that's what we are writing we are writing to specific cell column is always five and the row starts with two and we are increasing with one every time save so we should be good to run this flow there is one one thing that I think is still important and that's when you're writing to this application the way power ultimate normally writes is by basically doing this it's just typing like a human and you can basically increase the performance and also make you put way more stable by clicking on popular text field on web page going to Advanced and then you're gonna disable populate text using physical keystrokes like this one and then you will get another option emulate typing and did the first one basically you can either send physical keystrokes or the bot can can connect with the web app via the background and if you want to go for the background that's more stable and this also increases the speed you have to disable this one emulate typing basically means that it will pretend to be typing so it will send the keystrokes one by one so this one you can disable as well I'm also going to do that for the orders and if you want to build Bots that perform very fast and that are really stable just make sure that you disable the first option and also preferably also the second one like this I don't think you can do it for a checkbox no you cannot you can also I'm not sure if you can do this for a button no you cannot but you can do it for a hyperlink so for a click link on web page you can also go to Advanced and you can hear you see send physical click and you can disable this so that the bot doesn't send physical clicks but um it it clicks via the background of this application um just for you to be aware this doesn't work for every application so in case this doesn't work you still have to enable it okay I think we have everything so let's close the browser make sure that you close the Excel and just one more reminder make sure that in close Excel you have saved this document otherwise you're not going to be able to see the result in Excel and if you have done all that let's run about okay let's take a look at the result so let's open the Excel file and see that the employee numbers have been wet into this Excel file so if you have the same result well done and congratulations for building your second robot I really hope you already learned a lot about using power automate desktop but if you want to become a professional developer you're also going to need some best practices fill in your name and email address at the bottom of this page and you will receive my best practices for power ultimate desktop for free okay I hope you're ready for our next chapter because the next chapter is one of the most exciting ones in this chapter we're going to use power automate desktop to scrape data from our website so let's navigate back to our website and I'm gonna use chapter 3 web scraping and if you click on open course material you will see that this website looks different because in this exercise we're going to use an external website and on this website you can find all different kind of web pages which are basically designed to to web scrape them so the first one is this one so this is the first website and this is as well the easiest website to scrape you can see you can navigate to phones you can also narrow down on phones with a touch screen like this and you can as well go to computers and there you can choose between tablets and laptops so you can see here all the laptops but for me personally this website is a bit too easy and the reason for that is that this website doesn't have any navigation it doesn't span multiple pages so the website that we are going to scrape if we go back so this is the easiest website and then this one is already a bit more difficult because if you go to this website and you will see that here you have imagination so you you have to navigate to the different pages and this requires already a bit more technique and I'm going to teach you how to do this in this chapter so let's go to the main page so let's start with the creation of a new Excel file I'm going to create an Excel file here and let's call this one to scrape and here I'm going to define the categories that I would like to scrape so I'm going to open it I'm going to rename the sheet to sheet 1. let's go to this website okay let's see what we have so we have phones but I'm only going to focus on the phones with touch screen here because this category has multiple Pages it says it has to so let's copy this one let's go to the Excel file and here I'm going to create two columns I'm going to create one with the name and I'm also going to create one with the URL so those are the headers let's make them bold and then the first name I'm just going to call this one phones and this is the URL and then let's do two more categories so let's also navigate to computers and then we're going to go for tablets so I'm going to copy this one it's called tablets and let's also paste this one here and now let's also do laptops right so let's copy this one sorry laptops and pay zero okay so we're gonna scrape those three pages and for these three pages we're going to then scrape every page so we're going to go to this entire navigation I see this website has quite a lot of laptops so I think that's really exciting because here we can also try rather about has the has the right performance um let's go back to the Excel file we have all of this we have name and we have URL that's important and then let's open power automate desktop the first thing I'm going to do is I'm going to launch Excel I'm not going to launch a blank document I'm going to open the following document and that's the document that you've just seen on the desktop here um the Excel file doesn't need to be visible as we've discussed earlier it is decreasing the performance of the bot and let's save this one and if we open Excel we're going to close it as well so go for close Excel and we are going to close Excel instance this is important because in this exercise we're also going to work with multiple Excel sheets so multiple Excel instances this one I don't need to save because I'm not making any changes in this Excel file with what okay and then I'm going to read the Excel range read from Excel worksheets and put this one below the lounge Excel I want to extract all available values from this worksheet and of course my Excel file has headers you've already seen it so we have to enable this one click on Save and I see that this this entire data sheet will be stored in Excel data and I'm going to Loop through it with the four each so put that one here and the value to iterate that's Excel data and it will be put in current item okay so the only thing we are now doing is we're just making sure that the bot is looping through this Excel sheet and it's basically going to phones tablets and laptops and inside this one we're going to need another loop and that's uh that's the second Loop and that that's a loop that's going to control the pagination and if you can't follow anything of this don't worry I will explain this all in the next minutes okay so in this four each let's also open the browser you know I like Chrome but feel free to use any browser and chrome and I'm just gonna launch this website so it just copy from static after the second slash this one and let's put this one here I want to launch a new instance and I also like this page to be maximized because that way is always the same size it's always the maximum screen size which is always the same click on Save and if we launch Chrome we're also going to close the browser right close web browse don't be confused because there are multiple activities to open every web browser Chrome Firefox Etc but there was only one activity to close them all and I'm going to do that here closing the browser okay and in this for each we're then going to do the actual scraping so let's see which activities we have for that so navigate to browser automation web data extraction extract data from web page this is the one you need and I'm going to put that in the for each browser that's the correct timeout is 60 Seconds store data mode that's a variable that's that's correct and you see that the variable that's produced is called Data from web page so click save and you will see that you get an error and that's because we haven't defined which data to scrape and if I'm going to open this one again and if you now navigate to this website you will see that the data extraction wizard will pop up and this extraction wizard is a bit different than what we have seen earlier so you can hover over all the items you want to scrape and I definitely want to scrape the title I know make sure to watch carefully because this is different don't click on the left Mouse button but click on the right one the one that you normally click on to see more options and as you can see here extract element value now you can extract the text but you can also for example extract the URL and that's also quite important especially if you want to scrape that web page as well for now I'm going to go for the text and you will see that now only gives you one green border and that's not what I want so we have to show Power automate desktop that there is a pattern here so I'm also going to click on this one just make sure that you click on the same element on multiple instances right click extract element value and then dig as well the text and I will see that power out to my desktop notices that there is a pattern and it will basically show a dotted green line around all of the titles and from now on it will do that automatically if you add another element so let's add the specs as well right Mouse click extract element value text and now you will see that it you will get this green dotted rectangle automatically around all of them I also want the price so click here and we want the text and there's one more thing that I want and that's the URL and the URL is hidden behind this link so I'm going to click here right Mouse click extract element value and now you will see that you need hrf so click here I will now see that we have um we have captured those four values from every item so I'm going to click on finish and because all the pages are the same we only have to do this once I'm going to click on Save because this page is basically the same as tablets and the structure is the same it's just the content that differs let's see what we're going to need so I already told you in the beginning that we're going to use multiple Excel instances and I'm gonna open another Excel so launch Excel but I'm going to do that in the for each like this and I want to start with a blank document because there is no data yet the instance doesn't have to be visible and if you open it I'm also going to close it at the end of the four each um that here I need to save it right because it wasn't there there yet and the Excel instance you can you see now that you can choose between the two of them and I want to go for in Excel instance 2 because that's the second one that has been initiated here so click on Save and I want to save the document as and I want to save it so you see we have the four each here which is looping to the Excel data of the first Excel and I'm going to save it as the name so phones tablets or laptops Dot xlsx so let's go for a variable here make sure to choose that you select save document s document format is default and then select a variable this one here the X and then I'm going to go for current item and here I'm going to use again the square brackets name and then after the second percentage sign so behind this one I'm going to type Dot xlsx so it's going to save it as phones.xlsxlaptops.xlsx Etc click on Save here and I want to make sure to save this data in this Excel file I don't want to overwrite any data so I want to write on the first row that is still empty in Excel there is an activity for that first I want to get the first three row for a specific column so take this one and let's put it here at Excel instance you know that is Excel instance 2. the column and we're just going to go for column one because if this column is populated the other Columns of this same row are also populated I see the variable produces first three row on column so I'm going to click on Save and then I want to write data to excel right to Excel worksheet like this Excel instance is Excel instance2 and the value that you would like to write that's what I've just taken from the web page so data from web page select and the right mode is on specific cell the column is just going to be column one because I'm going to start at the First Column with writing this this table and the row is going to be the first empty row so that's going to be first free row on column make sure to scroll here they will see it and I click on select click on Save now okay so that was one exercise but now we will only take one page from every category we're going to navigate to the correct URL so let's go to to browser automation and then go to web page and make sure to put that in the forage to URL and the URL that we want to go through that's going to be current item and then URL between square brackets and that's actually why we have this first Excel file right okay and now before we run this robot there is one thing that we still need to change navigate to launch Excel and make sure that you make this instance visible and for some reason if you don't do this you cannot see this data in this Excel file you can try it yourself so make it visible and then let's run a robot okay you probably already saw the robot writing this data so I'm just going to open laptops because that one had the most data I will see that the robot has written one two three four five six rows and if I navigate to the laptops page computers laptops then you will see that this one this first page also has six rows but of course you want to scrape all the pages right you want to get all the data even the data on page 20 and how to do that I'm going to show you right now so let's close this one let's also remove those files so that we make sure that we are not confusing ourselves um okay so let's take a look at the pagination so basically there are two routes that you can take you can every time click on the next button this one or you can just click on the button of the page and then plus one so if you're on page one you're going to click on two if you want to you're going to click on three Etc and I'm gonna go for the second route because I think that one is a bit more um you learn a bit more but you can also go of course for the other right so let's start with making a loop navigate to loops and then I'm going to take this one Loop and make sure to put it below the lounge Excel like here okay we're going to start from one then every time we're going to increase by one increment by one and we're going to end let's say with 1 million make sure this number is large enough and the variable produced is Loop index and that's basically the counter that will take care of counting the page that we are currently going through so we'll start with page one and then in theory we'll enter with page 1 million which you will see that we are probably going to close before so click on Save then make sure to extract data from web page get first free row and write to the Excel sheet those are all activities that we have to do at every page of course the closed Excel we only have to do that at the end of this entire range of laptops and phones Etc so take those three activities so click on the first one then click control click on the second one and click on the third one while also still holding Ctrl and then drag them here in the loop like this okay and then I want to know if this button so if the button uh Page Plus One is existing so I'm going to go back here let's go to browser automation and I'm going to use if web page contains so take this one and put it here below the right to excel and if web page contains the following elements I'm going to create a UI element here and then here I'm going to click on two so make sure that you you click control and then you click with your left Mouse button and they will see here the selector that is going to be created or the UI element so click on Save but I'm going to change this UI element so I'm going to click here this is where you can find all the UI elements all the selectors and I'm going to click here on enter 2. and here you you will see all the items that this selector is using to navigate so you see that the selector is using um the text to which is part of a an a element in HTML is a stands for link so it's looking for a link that has the text to and I'm going to change so I'm going to click on this one click on the two here and I'm going to change this one with the X to a variable and that variable is going to be Loop index but before I close I don't want to click on page one if I'm on page one and on page two if I'm on page two I want to click on the next one so I'm going to say plus one like this and if you have this I will show it one more time if you have this on the screen then it should work for you as well so click on save so now we're only checking whether this element exists because it's also there is also a link in it and if I'm on page 20 there is no there is no button anymore which says page 21 with link writes so in that case the if web page contains will return like it does it doesn't exist and in that case we also know that then I'm also going to add an else activity so just search for else take this one else I just put it in the if before the ends like this okay so if this page exists then of course I'm going to click on it so I'm going to search here in browser automation and I'm going to use your click link on the web page this one so if it exists I'm also going to click on it so the UI element is the same UI element because I've seen that it exists so now I want to click on the same elements right so you don't always have to create a new UI element you can do that but that way you will just create redundant items so I'm just going to select this one and click on select and I'm going to click on Save and if the next page does not exist then I'm just going to close this Loop so I'm going to go to Loops again and then here I'm going to go for the exit Loop activity right and then I'm only exiting this Loop not this one okay let's navigate here let's close this web page make sure that you have removed all the files and I also promised you that I was going to show you how to save files in another directory so for that I'm going to open launch the first Lounge Excel this one I'm gonna copy this one then I'm gonna go to the save of the second close Excel so this one make sure that you have the close Excel from Excel instance 2. click on it and then for document pads I'm going to just um press Ctrl V here to paste and I'm going to remove this last part so now you should have C users and of course your own name desktop current item name Dot xlsx and that way your Excel files will be saved on the desktop which is next to this file okay um I think we're ready um so let's see if this was going to work okay you probably have seen that my board was way faster than yours that's because I'm speeding up this video because otherwise it's really boring to watch so don't worry if your Bot is taking more time there are quite some pages to scrape so the bot will take some time I hope you learned a lot in this chapter not only about rap scraping but also about loops and about checking whether an element exists on the page and I hope to see you back in the next chapter about variables I will see you there whenever you want to take data from one part of the flow to another part of the flow you're gonna need variables so let's take a look at variables so let's launch a new Chrome instance Arch new Chrome and the URL that I'm going to use is this one and this is the assignment that we're going to do at the end of this chapter so I'm going to take this URL I'm gonna copy it to power at my desktop Windows State I want to be maximized and save it and if we open a browser we want to close the browser as well so use a close browser then I'm going to navigate to browser automation update extraction and get details of element on web page so let's take this one and UI element that I want to fetch that for now that's this one weight 15 kilograms so click on control and let's click on Save so you will see that automatically get details of element on web page will save to this variable attribute value so let's call this one wait oh suitcase and in the assignment of this chapter you will see why I'm calling the variable like this click on Save let's display this element on the screen weight of suitcase all right I will see that now we get back 10 kilogram and normally you want to use 10 so in that case you have to modify this string and you have to remove kilogram let's see how we can do that before we get into this you see that all these local variables are called flow variables and you can find them here and after running a script you see as well the values they contain so after running the script you see that here the value of the suitcase is 10 kilogram and that's by the way a text variable you cannot do any calculations because the kg element is still here um if you want to use input or output variables you can add them here and input or output variables are used when you want to connect power ultimate desktop to power automate or any other Microsoft service I'm not going to tell too much about them in this tutorial but they are important if you want to use the broader Microsoft environment for now let's let's look at variables and let's start with text variables so you have seen that currently weight of suitcase contains 10 kilogram so let's see what we can do with this variable so the first thing that we can do is change text case so I'm going to put this one before the display message text to convert that's weight of suitcase and I want Power automate to save the outcome of this activity in the same variable so here you can just change this one you can hear as well put weight of suitcase and that way it will just overwrite the value that Tesla was already there and you can convert to uppercase that doesn't make a lot of sense as it's already an uppercase so let's go for title case title case basically means that the first character is a capital character and then all the other letters are just smaller letters not capitalized and title is a very a very good one if you want to for example put names in the in the right case with the first letter being um a capital letters so click on Save let's run the script by the way the values of this website they will change um randomly so we'll see now it's 15 kilogram you see it's 15 kilogram and kg is both written with small um small jars so that means if the one was for example um an a letter A then this letter would be in capital so you can see that with this change text case you can change the case of all these characters um let's try another activity before that so you can as well save the outcome of this operation in another variable just type any variable name here just let's say just um title case and then power ultimate test will automatically create this variable and you will see it right here title case if you don't want to save the variable again as well switch this toggle and the variable will not be created and if you don't need the variable then you can save some space here in your variable panel if you want to filter on variables you can as well here click here and you can filter on text values for example or web browser instance variables or just show them all so that's very useful if you are using a lot of variables okay let's remove the change text case and let's try get subtex so let's say I want to get rid of the letters kg then I can use get subtext here I'm just going to put them right here the original text that's weight of suitcase and then you have to start give a starting index a starting character you also have to provide the length of what you want to remove so so if you never get back to this website then you see 15 kg so we only want the first two characters so let's try that so let's start with character position let's start with zero and then let's say the length is number of chars and the number of jars that we want to extract is two variable variable produced is automatically sip text so let's change that to weight of suitcase like this we still have the display message so let's see what we get I see that we get 10 and and if you just have the number 10 we can do calculations with this number because we don't have kg so it's not a string anymore it's now converted to a number I'm going to tell a bit more about that in the assignment but before that I'm going to show you some other variable types okay so we have now seen the text variable you see that there is get subtext if you search on the text you'll see that a lot of other activities that you can apply on text variables let's also look at numeric variables so let's create another variable we can do that with set variable and let's say that we have a variable number of runs and the value is going to be 10. and then we have another variable let's say that this one is counter and the value is now zero and then we can search for a loop so Loop condition and here we can say as long as counter is smaller than or smaller than or equal to number of runs and we're going to do something and you just have to make sure that you add another operation and that's this one increase variable because in the counter you want to increase the counter every time it runs by one and let's use again a display message just to see how this condition works value of counter the value of counter is equal to counter okay so we have the variable a number of runs which is 10 then we have the counter which is zero and then we will run this condition as long as counter is less than 10 so in total it will run 10 times let's run it zero okay so the number zero till nine so that was in total 10 times at this Loop condition this while is very useful especially if you want to perform a specific task a number of times and the number of times you can you can set this as a variable here so you can even change it and you can use the increase or decrease variable every time you want to alter the value of a variable okay let's remove this and let's take a look at another variable and that's the list variable so I'm going to start with creating a new list here create list and I'm going to call this list fruits so and the list is basically a collection of items that share a common value so now you see for example fruit here and if you want to add items to the list you can use this activity add item to list now the item that I want to add is an apple and I want to add that to the list fruit of course you can have multiple lists and one flow and let's also add another fruit so let's now as well add a banana into the list fruits can as well copy this one if you want just using Ctrl C and Ctrl V but let's change this one to an orange and with this list you can do a number of things so for example you can reverse it you can Shuffle the list so you can randomly change the order of this list so let's do that with fruits so here it's just apple banana orange where Apple Acero banana is one and oranges two after we've shuffled this list the values have been distributed randomly so let's use a forage again for each if I use iterate that's going to be fruits I will see that the power of Modesto will store the current item in travel current item which you can only use in this four each so let's use another display message and let's show current item so we now get the banana first then we get the Apple and then we get the orange and if we run the flow again you will see that the order will change or it's probably banana Apple Orange okay for some reason it's the same now we start with the Apple Orange and banana okay so this is how you can use it for each we've already seen it you can use it for each over an Excel file but you can also use it over a list and you can Shuffle the value of a list and lists are very useful if you want to store like two or three items and you want to perform an action over these two or three items or maybe have even more items and if you have a bit of programming experience you will definitely recognize how useful these things are still one more variable that I want to show you so let's use another set variable activity like this one and then let's call this variable password let's take a very good password like one two three four five six seven eight let's save it and what you can do so you see this variable here you don't see the content of it right now normally you can see the content after the first run but if you click on this icon you see here Mark a sensitive and that means that if you run this flow you will not see the content of this variable here so you see here now only sends it to variable and if you set a normal variable like let's call this one order for this value then you will see its content here and the same goes for any logs if you mark this variable as sensitive but what the Modesto will not store its content in any of its logs so this type of variable is very useful especially if you want to store passwords or other sensitive data okay let's now get used to using variables so let's remove those and let's open the assignment of this chapter so you see the assignment here basically this is about a plane cargo so you have two suitcases we have suitcase one we have suitcase tool and then we have the baggage restrictions so you see the First suitcase is 15 kilogram the second one is 20 kilogram so in total that's 35 kilogram and the Max total wide rate here is 40 kilogram now every time you will refresh this page those numbers will differ randomly in this case you have 32 plus 19 so and the max restriction is 50. so in this case you have to match a baggage and that's not allowed the Bots that we are going to build is going to compare if the total weight because the the Restriction is on the total weight not in individual suitcases is more or less than the sum of the suitcases and based on that we will then um allow or not allow this baggage to be on board let's open power out to my desktop and if you really want to become a Power ultimate desktop expert I really advise you to make this assignment together with me so let's start with the launch new Chrome activity the URL that we're going to use is this one so I'm just going to put it in here the windows state is going to be maximized and I'm as well going to close the web browser like this let's save the activity then let's search for browser automation update extraction and then get details of elements so we need to get details of three elements so let's create some new UI elements just use control and just take this value don't take the entire one just this one so click control and let's save this one in the variable suitcase one okay then I'm going to add the same activity again UI elements so click control suitcase number two and then the third activity create a new UI elements and then let's save this one in Max weight okay so there are a few things that we have to do with these variables so we've already noticed that once we extract this data we get 32 kilograms so we need to get rid of the kilogram because we cannot do any calculations with this and we can use the get sub X activity for that but if we then get a text that has more than two characters for example um 100 kilogram then I will not work anymore so I'm going to use another activity and the activity that we're going to use to get rid of kg a scrub decks so I'm going to put that one here the original text it's going to be suitcase one get text before the specified flag and the and flag is kg and it will then save this text let's change this variable let's call this one suitcase one corrupt and then it also with the second one original text is going to be suitcase 2. and flags kg and variable produced is suitcase to crops and let's also do that for the max weights so the original text is Max weights the flag skg and this one is Max weight okay let's just make sure that we have done this correct so let's add a display message put it here and the variables that I want to see are markzware cropped I'm gonna just put a space here suitcase one cropped and suitcase two crops so click on Save let's run it we get 50 as indeed we started with the max weight 32 and 19 and that's entirely correct and without the kg okay so let's do one more thing because in most cases this works but in some cases still doesn't work and what you can do then is convert text to number so this is the one you need and let's just you put this one here and let's say that we now want to convert suitcase one crops to suitcase one number I'm gonna do the same with the other variables suitcase to cropped and let's save it in suitcase to number one more max weight corrupts and variable is Max weights number now as I already mentioned sometimes your board still works if you don't convert text to a number or number to text but sometimes it doesn't work so just to be sure it makes sense to add these convert text to number activities by the way it's the same with date variables I'm gonna take a deeper look at that in the next chapter so I'm going to remove the display message then let's create another variable with set for set Fireball I'm just going to put that here and here I'm going to say total suitcase weight is equal to suitcase one number and now you can put a plus sign before the percentage signs and then add a suitcase to underscore number sorry I just forgot the two so like this and let's click on Save let's add an effectivity if total suitcase weights is less than or equal to because if you if you've bought 40 kilo and your baggage is still 40 kilo then that's still Oak you can still onboard the suitcases but if it's more it's not okay and then the second uprant that's of course the max weight number I see here as well that those are all text values those are text values and there's a numerical value if you want to use fraction numbers like 10.5 10.6 you can use a DOT you cannot use a comma so just make sure that if you are you're using your PIN values with a comma that you replace this comma bio dot before converting this text to a number and you can of course use the replay Stacks activity for that then click save then let's also add an else activity just like this this play message I'm just going to use a message box just for the sake of simplicity okay so if the total suitcase weight is less or equal to the max weight your goods you didn't X see it the max baggage and it's a another display message stop it took too much baggage sorry um let's save it okay let's close this one and let's run a flow the good you didn't exceed the max package I'm just gonna remove the close web browse because otherwise I cannot see the values and then let's round about again okay so 15 20 that's 35 max weight is 40. let's see what the pop-up says you're good you didn't exceed the max package that's one one more time just also want to see the order scenario so we have 10 28 and the max weight is 40 okay that's still less than 40 kilograms so we're still good you're good you didn't exceed the max baggage okay so this assignment should be good always make sure that you test the positive and the negative scenarios and let's continue with the next chapter which is about eight time variables I will see you there okay let's take a look at date and time variables and if you search for date they will find the number of actions that you can use to calculate dates and the first one that I would like to show is how to calculate today's date get current date and time so I'm going to drag this one to the center you want to retrieve the current date and time you can also retrieve only the date of today and then you can select a specific time zone or you can just go with the system time zone which in my case is M stem and they will see here that the variable that is currently produced is current date time so let's save this one let's use a message box and I will see here the current date which is the 2nd of August 2023 and you see as well the diamond Am Pm format and if you're not living in the US then probably you want another date format and that's possible so search for convert an action that you need is convert date time to text so I'm going to take this one and draw it right in the middle and the daytime that I want to convert this current today's time format to use so you can say standard here and I can choose for a short date or long date Etc and the cool thing is that you see here an example of what you're producing so if you're changing this one to a long date AC as well the day of the week Etc so for now I'm going to change standard to custom and with this option we can create a custom date there is a web page created by Microsoft and that webpage contains all the options that you can use to generate the formats that you like so you see here for example that one small cap T is the day of the month to these that's the date of the month but then zero failed so you see here zero one instead of one Etc so any format that you want I can also generate custom things so for example you can generate the full name of the month and there is way more so just explore here what's possible I'm going to go back to Power automate since I want to generate a European date so I'm going to type D Dash capital M YY and if you also want the the time you can go for age of hour M from minute and seconds and you see here that the m is in small caps and this The Mont m is capitalized so that's how power admit recognizes the difference so we have now generated a full date I'm just going to remove the time part so we just keep the date let's save it you see the outcome is formatted date time so if I go for formatted daytime so that's how you generate a European date and there is one more thing that I would like to show you because if you gener if you remove from what it's date time and you select the real date variables so the date variable that's current date time and you expand it you can as well get the year the month today so for example if you just want to get the year select this one I see then that you get the variable name current date time dot year if I run the script now you will see 2023 okay so this is quite convenient so you see here that the same way we have converted text variables to numbers and numbers to text we can also convert the date to text and we can also convert text to date what's important here is that you can only format a date when you convert it to a text because a date itself doesn't have a format because it's just a date I'm going to remove display message I'm also going to remove this one I'm going to keep the current date and time so let's also see how we can convert a text to A Date instead of a date to a text so let's create a variable set variable and let's call this one date and the best and let's say the date in the past is 13 May 2023 so let's convert it let's convert text to daytime text to convert that's a date in the past and this date is represented in in a custom format because it's a European date and that format is d m YY you see variable produced is text as daytime so that's a date time variable so now we have two date time variable we have current date time and we have text as date time and that means that we can do a kind of calculations or comparisons so I'm going to search here for dates and I see your subtract dates and this is an activity that we can use to do date comparisons so to see whether a date isn't passed or in the future so from date that's current date time just make sure here that you select the variable that is of date to time format and not the text variable so current date time and I want to subtract from that date text as date time which is basically the date time variable of the date in the past and I want to get the difference in this you can see here that you can also get the minutes hours seconds Etc but for now we're going to go for days and the variable produced is time difference so let's put that variable in a message box Place between two dates and that stand time difference and that's because this is not a date but this is a difference between two dates which has been converted to number of days right so that's just a number let's save it let's run it I see that there are 81 days in between we also get a lot of numbers behind the dots and that is something that I don't want so I'm gonna round this number and I'm gonna do that with truncate number so let's add truncate number number to truncate that's time difference and I want to get a round number I just want zero decimals I see that the variable produces truncated value I'm going to change that because I just want to save it in the same variable time difference I don't want to create more variables than needed here and then let's make sure that you put display message below the trunket number and we then select it's still time difference right so let's run the script I see that there are 82 days between those two numbers and if we now change um the occurrence of the two dates so let's go to substrike dates so let's take this one let's copy it current date time and this should be text as daytime so I'm just swapping them right now so like this so I'm now subtracting the current date which is 2nd of August from the 13th of May so probably the amount of days is going to be negative I see indeed that we're going to get a negative number which means that we are subtracting from a date which is earlier than the date that we are subtracting let's do one more thing so I'm going to remove all those so we still have current date time I'm also going to remove this one and then let's search for date again and let's add to date time so the daytime that's current daytime and I want to add let's say 10 days and you see that the variable produced is resulted date and then we've added 10 days but we still have a date variable so let's um convert it so convert date time to text the daytime that I would like to convert that can be resulted date and the format is going to be custom I don't want it in European formats I see the outcome is formatted date time and let's put that one is in a display message so today plus 10 days and that's then going to be formatted daytime and let's run it and I see that in 10 days it's going to be the 12th of August 2023 currently is the second one okay let's remove all of this and let's continue with our assignments and as you know it works really well if you make this assignment together with me um let's go to the material of chapter 5 open course material click here to download the Excel file see that I just downloaded the Excel file I'm gonna put it on my desktop and if you open the Excel file then you will see here that we have a number of clients and they all have a contract renewal date and I want to email the clients but only the clients where the contract renewal date is in the past so you see this one is in the future this one is in future this one is in the future and the last three are in the past so in real assignment I would send an email but because I know that not everyone has connected to Outlook I'm just going to put here an email true or false just to see whether I want to send this email yes or no and if you want to make the entire real assignment then there is another video on my channel that explains how to connect power automate desktop with Microsoft Outlook so let's go to Power automates and let's open Excel so launch Excel I want to announce with a blank document I want to open the following document and that document is client contracts I'm going to save it like this I'm also going to close the Excel it needs to be saved so make sure that you save it at the end and then I'm also going to read the Excel read from Excel worksheet and I want to read all available values from the worksheet um just make sure that you enable first line of range contains column names because this Excel sheet has headers and then let's click on Save then I'm going to add for each the value to iterate that's going to be Excel data and you see that current item will be generated and for every line I'm going to do the following I'm going to convert the text to a daytime with this one and the text to convert that's going to be current item I see for me the days are in the American format but if you have them in European format of course then I also have to provide the format so current item I'm going to add the square brackets contract renewal date and date is represented in custom format this one is not necessary as the dates are in American format and you see that the variable produced is text as date time click save and then we also need to calculate today's date so let's search for dates and we get today's Avid cap current date and time so I'm going to put that here in the beginning current date and time system time zone so that's correct save and then I'm gonna make a calculation so let's go for subtract dates put it here from date so that's current date time and the date that you want to subtract that Stacks as date time and that's the the date and the contract the contract renewal date you want to get the difference in days and now we want to get an if statement so the first operands is time difference if time difference is less than zero so that means that it is negative in that case I want to send the email and I'm also going to add an else like this so the variable so the variable that I set in case the time difference is negative let's send email is true because we only want to send emails where the contract renewal date is in the past I'm just going to copy paste this one so just click on it press Ctrl C Ctrl V and then drag this one here and if the time the difference is not negative that means that it's either today so it's zero or it is it's more than zero and I'm going to put send email on false and then we're going to write to excel right to Excel worksheet like this and the value to write that's going to be send email and you want to write it to column one two three four so column four and the row let's keep this one empty for now and let's again add the counter so set variable that one is two here counter value base 2. and you want to increase it but you want to do it at the end of the for each so here you want to increase it so you want to increase counter and you want to increase it by one and then I'm going to go back here I'm going to say that I want to write to row counter let's save it and that should be it so I'm going to close this Excel file and let's run this bot so let's open this Excel file and there is something wrong with my statement because you see that we are now sending emails for clients with a renewal date in the future but not for clients with a renewal date in the past so I'm going to check my logic if time difference is less than zero and I'm subtracting text as date time from current date time the difference in dates so that's correct and I ought to see what the problem is because we are sending emails when the time difference is negative but when the time difference is negative that means that the date we are subtracting is larger than the current date so in that case we should not send emails because then the date is still in the future and in this case in case number is positive then we should send emails so let's clean this column let's save it and let's run about again let's open the Excel so now you see we're not sending emails in case the date of the contract renewal is in the future and we're only sending emails for dates which are in the best okay let's start by creating a new Excel file so I'm going to create one here on the desktop just like this and you can just keep the default name then let's go back to Power automate desktop and if we Now launch a new Excel instance with this activity open the following documents and the document that we want to open is this one so open it and click on Save okay if you go back you will see that we are referring to C users Thomas desktop and then the name of the Excel file and of course this works on my computer but as soon as I transfer this power to my desktop flow to your computer is not going to work anymore because for you there is a different name here so if you want to avoid this then let's navigate to folder they see here an activity that's called get special folder and I'm going to put this one before the launch Excel and with the get special folder you can get um the the pads of any folder any special folder within windows so for example you can get the desktop folder you can get the the cookies folder the history folder Etc you can also use the documents folder but for now let's stick to the desktop folder and then you will get an example you so you will see in my case this is the parts to this folder but in your case it will be different then the variable produces is going to be called special folder pad let's call this one desktop pad click save and here I'm going to remove everything but the Slash and the name of the Excel file and I'm going to refer to desktopat so now we have desktopat slash and then the name of the Excel file click on Save and even now run this flow you will see the power ultimate desktop will open the Excel file and we have now created the flow that will work on any computer and not just online you'll see here that the Excel file has been opened okay so another I'm just going to remove the large Excel I'm going to keep the get special folder because I want to show another activity that's very helpful and that's gets get files in folder so just put it here the folder that I want to use is desktop desktop pad and then I'm going to navigate to the website uh just make sure to navigate to chapter six open course material and here you can download the example files and if you have downloaded them you will see that you will get a zip file extract the zip file and just make sure that you go in the employee files and that you copy this folder so copy it and make sure to put it here on the desktop okay and this folder consists of a lot of files and you will see that in the files you also see many times the name of the person who created this file or who owns this file like Jonathan Emma Jake and James and then you have some sub pullers like bookkeeping and contracts so let's use these files as an example so we are referring to a desktopath and then we also have to add the name of this folder so just use a slash and then add the name of the folder I'm going to keep the filter on the star we just want to go through every file and then you can choose whether you want to also include subfolders or only show the files directly into this folder so if you don't include subfolders then those files will not be listed okay so let's go back let's let's include the subfolders and Save and then we can use a for each to a loop to the file so you will see that get files in folder will create a variable which is called files if we now use a for each and value to iterate is files I will see that this for each again creates current item so use display message I just want to see all the files in this folder and let's then refer to current item let's save it okay let's run it we're going to get some message boxes I'm gonna disable this part so just make sure that you hold Ctrl select these activities and then make sure that you disable the action because I'm gonna use them again later for now I just want to show a few more things if you navigate to file you also see here write text to file this is a very helpful activity so you can choose a file I'm gonna again use desktopads a slash and then let's say that it wants to write to text file Dot txt write is hello world oh sorry and then you can choose here where you want to override the existing content or append the content like this I'm going to go for append content and then I'm going to click on Save and if you run this flow so if the text file doesn't exist already it will be created you see that this text file has been created here and if you run the flow again because I have enabled append the text will be appended at the end of the document so let's run it again and now we should have hello world two times okay this looks good then let's also try one more thing remove the right text to file and I want to try one more thing if file exists and with this if statements you can check whether a specific file exists the file that I want to check is this one and then text file.txt just going to copy this one like this and if it exists I'm going to delete it because that way you're never going to get an error once you delete files so the file that I want to delete is again desktop ads slash and then the file name let's run it again I will see that the file has just been deleted okay so if you delete files always make sure that you check whether the file exists unless you are using a loop because then you noted the file access because you're just looping through the files and using a loop that's something that I did here so make sure that you enable this these activities again enable also enable this one we just go to Arrow because um get files were still disabled so there was no variable to Loop through okay um and then the assignment of this chapter is going to be really really easy get files in folder this one still needs to be the same but this time what we want to do just open the folder we want to remove all the files that belong to Emma so you will see here Emma accounting then there is also in bookkeeping there is salary amount and in contracts there is also a file for Emma so we just want to remove all the files for another because she's leaving and we have to destroy them so let's navigate back to Power automate desktop let's remove the display message and get files in folder so in the file filter I'm going to say star which is a wild card Emma star like this and the start means that there can be something before or after Emma and if you want to filter on a specific extension then you only have to do this dot star dot txt for example because dot txt is the end of the expression right is it it's the extension so for now I'm just going to filter on Emma make sure to also put a star behind the amount you also want to include the subfolders and that this way will make sure that we only Loop through Ms files because we are filtering so let's add one more time to display message and put current item on the screen let's run it and let's see if we only get files from Emma M accounting salary Emma and Emma dot takes it as X okay so this works I'm going to remove the display message and then I'm going to add the delete activity delete files this one with the garbage bin just put it here and the file that we want to delete is of course current item current item is the entire name of the file so you don't have to use desktop pads let's save it and let's run it and if you now go back to the folder you'll see that all the files that contains the name Emma have been deleted so they work pretty well just a gentle reminder if you haven't received my power out my desktop best practices yet navigate to the bottom of this page fill in your name and email and you will receive my power to my desktop best practices for free okay let's continue with chapter 7 so I'm going to click here on open course material I will see here that for chapter 7 we have two links the first one is the contoso application that we're gonna download so click here on the link we will navigate to GitHub and then click here on contoso invoice app this is a desktop application that Microsoft developed just to learn power automate so just click here on contoso inversing setup dot zip and then click here on the download symbol and the application has been downloaded I'm just gonna extract the zip file and we're going to start the installation process by clicking on the dot MSI so this one click on it you see I can repair it because my contoser application has already been installed but just navigate to the steps by clicking on next next Etc and then click on finish okay now we've installed the contoso application let's navigate back to the web application and let's also download The Source we're going to use a CSV file because we've already worked with an Excel file so for this chapter I have chosen for a comma separated values file a CSV I'm just going to copy it to the desktop now if you open this file you can for example open it with Notepad you will see that in this file we have data records we have a date we have an account to contact an amount an invoiced yes or no with true or false and you will see here that we have the dates and then we have the company contact person and the amount to invoice and if you open the contoso invoicing application I'm just going to maximize it you will see that we have a desktop application this is just a sample application that Microsoft has developed so no real actual invoice will be created once you create an invoice here and within this application we can add invoices we can add accounts cases records etc for now I'm just gonna work with invoices so click on invoices and if you want to create a new invoice you can just click here on the new um on the new document icon for example you can then create an invoice let's keep the data the same currently as a first of September Tom's attack Academy I'm the contact person and let's create an invoice for thousand Euro and then I'm going to change the status from an invoiced to invoiced and then you can click here on the save icon and then the record will be saved and now you can add another one by clicking on the new records icon okay so what we're gonna do in this case is we're going to use the contoso invoicing application and for the records do we have any CSV file we're going to create new invoices so let's open power automate desktop make sure that you have a blank canvas in front of you and the first thing that we have to do just search here for application is we have to run the application and if you navigate back to the contoso invoicing applications should now be on your desktop you click on it which will write Mouse button and then you go to properties and you will see that this desktop icon is linking to this folder just go I'm going to copy this thing entirely then let's open it press enter and then you will see here the invoicing app so this is the actual executable if you just see the file name and not DOT access just click here on the three dots then navigate to options navigate to view and then you see here height extensions for known file types so for you in this case this one is enabled make sure to disable it click apply click OK and now you will see here dot access okay so in power automate desktop the application that I want to launch so I'm just going to copy the pads that they've copied earlier see program files contoso contose the invoicing it can look a bit different for you and then I'm going to add as well the name of the application so I'm going to select rename I'm going to select everything and then I'm going to copy this entire thing and I'm going to paste it here okay so now you should have the full path to the executable here click on Save now let's close the application and there is one thing that I want to do if we open this application with power automate I want it to be maximized okay let's click on Save let's run it just to see if the application launches I will see once we run the flow the application is launched Okay then if we open the application we also want to close it so I'm going to choose close window here just put it here find window modes I'm going to do this by the title Android class and the window title of the application that I want to close is contoso invoicing so just make sure that you add those are invoicing power out automate will automatically provide the name but only if the application is open so let's close it and then click on save so if you run your flow now and power automates are both open and as well close the application okay that was really quick but you saw that it worked okay let's now do the rest so as I mentioned earlier we're going to work with a CSV we're going to wrap separated file and what we're going to need here is wheat from CSV file you can as well write to a CSV file but that's for another video so once I have um open the application I'm going to read from the CSV file and make sure that the CSV file that we've downloaded earlier is stored on your desktop the file pad is I'm going to navigate to this one invoices.csv if you want you can as well work with the get a special folder activity to get the desktop folder for now I'm just gonna leave it like this so we're going to read from the CSV file and then there is one thing that is very important encoding is utf-8 that's correct and just open advanced and make sure that first line contains column name just like with with the 3D Excel because the first line indeed this is not data this is just the name of the columns so click on Save and if you have CSV files with different setup you can as well change here to comma separator to custom and then for example you can use a semicolon or whatever symbol is being used for now I'm just going to leave it on predefined because we are using the system default okay so we have read from the CSV file we have open the application we've closed the application let's open the application one more time let's maximize it okay so the first thing that we want to do is we want to click on invoices and then we want to create a new invoice so let's search for a click and one that you need we are not browser automating anymore we are now using UI automation that's what you use when you automate in the windows environment so I'm just going to take this one click your element and window and I'm going to drop it here and the UI elements let's create a new one let's make sure that this one is not here and now let's click here on invoices so just press Ctrl and then click on invoices because that's where we want to add invoices and once you open the application it will always start with accounts highlighter so every time you open it you have to click on invoices okay so I'm going to click on invoices now and then let's show Power automate The Next Step so everything we want to do now we want to do within for each we've already learned about the four each it's the same as we've used it for Excel but now we use it within a CSV so and we did it below the click UI element so after we've clicked on invoices the value to iterate that's um the data table that has been generated by the CSV table and I'm just going to show you one more thing if you run this flow actually let you close the app you can check whether the CSV file has correctly been read I can do that by clicking here on CSV table and you see here four rows five columns so if you click on it you'll see the entire table here and you will see that the first row has been read like a header so just the way it should be and then you get all the rows so you see that this has been processed entirely correct click on close then let's add the for each again the value to iterate that's the CSV table and the variable that is being generated is current item X so click on Save okay now the next actions that we're going to perform we're going to perform for every item so make sure that you add them within the four each and the first thing that you want to do is just make sure that you navigate again to invoices you want to click here on this new icon the last thing you want to do is click on the save icon so let's add those two actions let's search for click click UI element and window add this one here and let's and let's show Power automate which element to click and we want to click on this one here so make sure that you're not using this one this one with this one always take the smallest one click on control and click and click on Save and I'm going to add another one to save so click on UI elements and that's this one so after after we've provided all the values we're going to click here on the the save icon click here and save okay and between those two elements we're going to add all our Magic and I'm just going to check in UI automation so open UI automation form filling and you will see that you can populate text field in window so add this one and those are the fields that we're going to fill so the first one that I want to fill is the date field it's already populated but I'm just going to override it so click here add your elements and then this one and the text that we want to write so that's a current item make sure that you're not using CSV table use current item from the loop use square brackets rotation marks and then the dates save populate text field in window let's show the second text fields and that's this one is the account so press Ctrl and click and I'm adding again current item accounts next one as a contact person and that's current item contacts and those names are just corresponding directly with it with those fields right so we are now going to this one the next one is amount and the next one is invoice so let's continue make sure that you're on the contoso app you can use alt step that's easiest so amount control click current item amounts okay the next field is a bit different because here we're going to use an if statement so the one that you need is this one if and just make sure that you put it between the last populate text field and before the click UI element if current item invoiced is equal to true I'm also going to add an else here we have a select field this one invoice an invoice or page so and if you open the txt file you see that in this data we only have we only have invoice with true or false so it's either invoiced if it's not invoiced and it's an invoice but you can also Imagine a scenario where you have more than two states and where you have for example three states so for now I'm gonna open your automation form filling and the one we need is this one set drop down this value so put that one in the if the drop down list I'm going to add and that's this one press Ctrl click and what you want to do here is you want to click select element by name and then the name is invoiced in case this value is true I'm just going to copy this one so just use Ctrl C Ctrl V put it here as well but if invoice is not true then we want to select uninvoiced so and then click on Save and this is our entire robot so I'm just quickly saving it let's close this one let's close the contoso application close the browser and last one about congratulations if you were able to build this boat together with me thank you so much for watching my complete power automate desktop tutorial I really hope you learned a lot don't forget to subscribe to my channel because there is way more to learn about power ultimate desktop I hope to see you back in one of my next videos ciao

Channel: Thomas Janssen | Tom's Tech Academy

Views: 6,715

Keywords:

Id: Y35ZJs16APQ

Length: 117min 55sec (7075 seconds)

Published: Fri Sep 15 2023