Variables Actions in Power Automate Desktop

hey everybody dave here have you tried power automate desktop yet if you haven't i would definitely encourage you to do it because it's free and if you have tried it and you're a little bit confused where to start or where to go with it here's what i'd suggest i think the best place to start with any rpa tool granted maybe a little bit of training is a good idea but if you just got the tool for yourself and you just want to try it out the best thing to do is just to use every single action or command or whatever it's called in that particular rpa tool just want to use every single one of them and make sure that you understand how each individual action works so that's what i'm going to do in power automate desktop over the next few months i plan to make a video about every group of actions starting with today which is going to be about variables most of the actions are super easy to use but i found actually that even the very simple ones there are some unintuitive things about them i wouldn't say it's anybody's fault that it's designed this way it's just not necessarily apparent about how you should use them i also figure that there are some actions that might be obvious to me for how to use them but maybe you're struggling with it and you're just trying to find out how to use that action that's what i plan to do over the next few months we'll see how that goes but today it's going to be about variables we're going to go through some of the actions like truncate number add item to list merge lists set variable things like that be sure to use the chapters feature in the video or go in the description and click on a time stamp to go straight to the action that you want to use one thing i want to mention though is i do plan to make the whole flow as like a full runnable flow using all of the actions in that single flow and that means that if you jump to a later point in the video it may be hard for you to replicate what i'm doing if a previous action is kind of required to run first just recognize that i think you can still jump around in the video but i just want to be clear about it each section of video is not going to be totally isolated from the rest i think it'll be pretty clear once you see it's a full runable flow when we test this out let's go ahead and jump into the variables group of actions in power automate desktop on the left you'll see all of the actions that we're going to be working with today luckily i'm not in the way on the video so you can actually see all of them on the screen if anything goes behind me i apologize sometimes i'm just not paying attention but the only setup that i've done is to put a bunch of comments over right here so if you type comment into the actions search box right here you could search for a comment you could drag it over and you could type into it this is non-functional you can't put breakpoints on this these don't do anything they just are comments there's labels sort of for sections of this so what i'm going to do is drop actions underneath each of these comments for each of the actions we're going to work with before we actually start with set variable we have to get a little bit of data it's not required to do this for you to practice these actions but i did find it useful for some of them so i'm going to go ahead and do that as a setup and i'll show you what i did on screen right here in case you want to do something similar and you haven't worked with excel already the first thing we're going to do is build a subflow to get data out of excel this is not 100 required for testing these actions but i think that it's helpful so i'll show you on screen what i've done and i'll actually just build it right now so let's uh go to my folder where i have an excel spreadsheet called challenge.xlsx i got this from rpachallenge.com you can use any spreadsheet that you need to but this is what i've done i'll show you what's inside here so we've just got some random columns and some rows of data that's all we need so this is the location of it c colon backslash test and then i'll put another backslash and then challenge.xlsx so let me go ahead and do that i'm gonna copy this backslash and then i'm just gonna copy this value right here let's create a new subflow and new subflow we'll call this get excel data and then inside of here we need some excel actions so let's see we need launch excel do we want a blank document nope we want to open the following document here's we'll paste in that path see colon backslash test backslashchallenge.xlsx do we want to make the instance visible no open is read only sure and save then i'm going to put close excel it's going to automatically put in excel instance which matches the output of the launch excel before closing excel do not save document save and then let's go to get first free row on column that's not what i want i want get first free column slash row from excel worksheet it's automatically putting in excel instance as the excel instance variable which this is going to be correct all the time as long as you're only working one excel at a time and then the variables it'll br it'll produce are first free column and first free row the next thing you want to do is just read what is this even called read from excel worksheet excel instance and then retrieve we're going to want a a range of cells the start column will be a start row will be one the end column here's where we use our variables we want the first free oops first free column minus one because the first free column is a blank column we don't want to retrieve that and then the first free row minus one because again that's the first free row is a blank row so if you wanted to write into the first free row then this would be the way to go and then you just don't subtract one under advanced we're going to select true on both of these i don't know that this really matters get cell content says text but i do i'm used to working with the the values as text when bringing them into an rpa tool and then first line of range contains column names this is true because in our spreadsheet the first row is the column names and then the variables that will be produced is called excel data save that and then let's save something that's pretty awesome is it now once you save the flow by either doing hitting the save or doing control s it no longer pops up a window that you need to click ok on to confirm that you've saved the changes my goodness that is a quality of life improvement i can't believe somebody thought it was a good idea to have a pop-up in the first place so let's go ahead and add a subflow reference to get excel data on the main flow so i need to type in subflow run subflow choose the subflow get excel data save i'm going to hit control s to save and then let's just um run this it's gonna run over the subflow and you'll see it run over all of these i'm gonna change the run delay to one millisecond so that it runs as fast as possible so it launches the excel gets the first free column in row subtracts one from each of those gets the range and then closes excel and it probably ran over the rest of this really fast so i'm going to open up the excel data and you can see it looks the same these bolded ones are the column names that we can reference and then here's our 10 rows of data right zero index based last one is nine let's look at the action set variable so i'm going to drag set variable over on to the flow and there's two things we can do we can change the name of the variable so i'm going to set this to count and put a 1 inside this okay and then i'm just gonna run this you can see it'll run our excel data subflow and then it's gonna set the value of the count variable to one so it's a number value sounds good now we can do is also for the same variable just so that we understand this how it works a little bit more you can change this to be text as well some text here and we can run this it'll set the variable to account of one i should probably have put break points let's do that i'll run this again it's going to go through this it should stop before setting the value to some text here so the value of count is one it's a numeric value and then if i run the rest of it and it sets some text into the count you'll see it changes the data type of the variable to text and in previous versions of power automate desktop what you had to do in some situations if you had like an input to a flow and you were calling it as a desktop flow you had to you know you can only do string as the data type for inputs what you could do is change that string to a number by multiplying the string by the number one and that would effectively make it a number um if that was a valid thing inside of power automate desktop at the time so i tested it a couple minutes ago and it doesn't seem like that's necessary anymore so i'm not going to actually show that on the screen you can basically just set this to number or to text and if if what you put into the variable is just a number it's going to call it a number variable now is that going to have unintended results i don't know let's test something what happens if i try to make this like a phone number but let's say the let's say like an account number that begins with zeros maybe that'd be better all right so this has leading zeroes let's see if it will put this to be a number because if it does that means it'll probably delete those three leading zeros and i don't want that let's run this okay right now we have some text here it's a text variable let's run the rest of it okay so it's still a text variable that's good let's try it like this though after we have set the variable to be a number data type with a number one but then we put something in it that has leading zeroes right after that does it stay as a number or does it stay as text i do not know the answer to this let's find out okay right now count is a numeric value to run the next action it put zeros okay my guess is that microsoft must have just designed power automate desktop to evaluate the value and determine whether or not it's probably a text value and one of their rules is if it's it's got leading zeroes um don't know so i would say suggest if you were going to use this a single variable for changing the data type back and forth or you know you have a value like this that's questionably a number or a text data type you probably just want to do some testing around that but it looks like microsoft has handled it pretty well here and then i can run the rest of it still text data type all right so that's how you use set variable it's pretty simple the next action is increase variable now i actually have no idea why this even exists it seems completely unnecessary but we're going to use it anyways because it exists what increase variable does is it takes a number and increases it by one or by whatever you choose to give it so here's the variable name input we'll choose count like we've already created above and then we're just going to increase it by one save and then let's let's run this we'll put a break point oh this actually won't work i just realized that so we have some text here and we're trying to increase that count variable by one yeah so it says argument value must be a numeric value so this has to be a number by the time it gets to here so let's copy set variable and paste it below right here okay so right so it'll do some text stuff but then it's going to set it back to 1 before we need to increment it and then it should work at this point so let's try running it one more time when it gets here the count is one it's a numeric value so now if i run the rest of it it will increase the count to two pretty simple right there's lots of times when you're designing a flow that you want to increase or decrease a variable by some you know number but what i don't understand is like why not just use set variable and then you know set the counts to like counts plus one that would do the same thing right let's run this whenever we get to our first break point the value of count will be one because it set it here we're gonna increase the count by one goes to two and then we just set the variable count equals count which is two plus one so it'll equal three now it's three so it seems completely unnecessary to me but here we are the next action is decrease variable let's drag over decrease variable this is going to be the same as increased variable except the opposite so let's grab our count let's decrease it by one click save and then in the same vein as how we did this before where we can use set variable equals count plus one we can also decrease the variable the same way and you accomplish the same thing so um decreasing the count by one and setting the count equals count minus one is the same thing so let's put the break points down here run this down which what we should get at the end what we should get by the first break point is three because we have set to one we added one then we added another one now let's decrease it by one goes down to two we'll decrease by another one goes down to one so by the end of this when we run it the count is going to be equal to one and again there's no benefit to using increase and decrease variable with the exception that maybe it's a little bit more readable to see increase variable rather than needing to read it like count equal or count to count plus one so maybe that's why they did it just to make it more readable our next action is going to be truncate number before we actually use it though let's first create a variable that we can use to truncate because our count up to this point has just been a single digit number and we need a decimal point to work with so let's use set variable underneath this truncate number section i'm going to call this number 2 truncate let's set the value to 95.386 let's drag truncate number over into the flow and the first thing that we got to do is choose the number to truncate i've actually named it number two truncate so that should be easy to find number to truncate then you'll see that there is an operation input that way you can choose how this action works the first thing we'll do is get the integer part of the number so if it's 95 point something we should expect to get 95 out of that i'm just going to name this trunk integer and then let's go ahead and copy and paste this i'm going to go into this one the second one that i just pasted i'm going to change the operation to get decimal part and then this time we're going to produce a variable called trunk decimal then i'll copy and paste again choose the second one and then i'm going to change the operation to round number and then i can choose how many decimal places i want to round it to let's round it to two decimal places because our input's gonna have three decimal places this will change it to two and when it rounds because our last number is a six we'd expect that when it rounds it should go up so we should see 95.39 the only thing left is to choose the output so i don't want to use the same variable because i want to look at all the variables together at the end so we'll call this trunk round and then we can just uh let's save this i always like to save just in case i actually haven't had power automate desktop crash on me and like uh since like when i first started using it it runs pretty well now but i just can't help but save constantly so let's run this it's gonna run all the way through and then we can look at the outputs of each of those so we set number to truncate to 95.386 so right here you see it says 95.386 then when we got the integer from that by doing truncate number and we chose the integer operation here's the integer 95 and then when we used get decimal part for that operation it went into trunk decimal so 95.386 became 0.386 and then truncate number we use the operation round number it became 95.39 and that that makes sense right because 95.386 that six should round up because we were rounding it to two decimal places and now it's 95.39 so if you need to do math operations uh this is what you do there are some things you can do as expressions inside of you know like say set variable there are a few things you can do but i don't as far as i know i don't think there's any like expressions or commands that can do things like truncate numbers so you have to use these individual actions to accomplish that the next action will be generate random number so let's pull that over put it into our section the minimum value i like that it gives you some defaults to work with so we're actually just going to use that for now the minimum value for the random number it will be zero the maximum value will be 100 we're going to leave generate multiple numbers off for a second and we'll come back to this so minimum value 0 maximum value 100 and that's going to go into a variable called random number i also like that they could have just called this new var but they're trying to go for a good user experience and if you just need a random number they go ahead and have the variable named for you in a way that's more readable so i like that let's save this and we'll go ahead and test this one so i do like this you can right click on the action and hit run from here so we can skip everything above later on in testing when we work with lists i won't be able to do that because i need to get data from excel first but here this should work run from here it'll only run this one and so random number is 15 let's run it one more time and just make sure it gives us a different number right so now it's 30 and so that's between 0 and 100. let's copy this and paste it in the second one we'll change the the operation that it does to generate multiple numbers we're going to say that we want 15 numbers and we want actually the maximum value let's make it 10. so minimum value 0 maximum 10 we're generating multiple numbers and how many numbers will we get 15. the first thing you'll notice is that the minimum and maximum values being 0 to 10 means we only have i guess technically we have 11 possibilities there because it could give us a zero what i wanted to force is to make sure that we get duplicate values and this is going to be useful later on when we're working with lists and we're removing duplicates and stuff like that so when we get 15 numbers some of the numbers in that random list will be the same and that's something that's important to note about when you're generating a random number it is each time when it generates a random number it's not taking into account other numbers it's generated so this will not always be a unique number that's not a good way to use this you'd want to use like a guide generator for something like that but for our purposes just understanding the action we just want 15 different numbers some of them might technically be the same number and matter of fact they will be so will we allow duplicates well in this situation it's not possible if i left if i left this as don't allow duplicates it's going to throw an error so maybe we should just go ahead and test that and of course it's named our variable output as random numbers let's right click on generate random number generate list of random numbers run from here this should throw an error yeah can't generate a list of 15 random numbers between 0 and 10 without duplicates so i'm going to open this back up and we are going to allow duplicates because i do want that for our testing later save and then let's uh run this again this time it gave us a list of numeric values and you can see it's got um sort of rows for us on the left zero index based can i make this can i can i just please please okay let's try this here's the the list of numbers um again don't be confused by the 14 it's zero index base so there's 15 here you can already see that there are three sevens in this there's two zeros so we have some duplicates and we also you know have a good list of random numbers they're in a random order this is going to be useful for us testing some of the other actions the next action is going to be create new list this is another one that i think is funny i don't know why this action exists and i'll show you why in a minute so create new list you can give it a name let's call this countries one and then save this and that's it this creates a list called countries nothing is in it it's just a list that exists with a name now one thing i suppose this is beneficial maybe there's some reason why this is useful but this wouldn't exist over here on the right normally you you know there's you can't go into the variables and do like a a click and add anywhere there's no there's no way to just add a variable by itself there has to be some action inside of your flow that uses the variable for that variable to exist as an example if i scroll all the way up where i have set variable set count to one if i delete this and every other action here that uses the variable count it will get removed from the variables list it won't be there anymore so maybe there's some benefit to that i don't know what it is right now though so this is effectively like a pointless action to me i'm going to run from here it creates a list right you can see the only thing in it oh so it does create it as a list list of general values but you know you can you'll see in a minute why this is pointless now that i have this list i can uh set the list to have values so let's use set variable and what i'll do is choose countries 1 and we're going to manually type the syntax to create an object to create a list in that variable so we we need um so we need percent symbols around the edges we also need open close parentheses inside of that we're going to have square brackets okay so this might be a little bit hard to read but let me put some spaces so it's a little bit easier to read okay inside of all of that we're going to put just a comma delimited set of values we'll we'll do canada with single quotes comma united states india with quotation marks and germany let's save this okay now let's uh let's run from here it should create a list called countries one and then it should set these values into that country's one list it should effectively have four rows of values inside that list afterward okay so let's open up countries one you can see this is a list of text values that's one thing that's different before when i created this it was a list of general values so that makes me think that it's possible to have multiple variable multiple data types in this same list so let's go ahead and test that really quick i'm going to add a comma and just do like a one hit save let's run from here countries one okay so that's actually interesting um i did not realize that before it it says the list of general values now because it sees that there are some text variables and some number variables okay that's kind of cool just learn something new the next action is to add item to a list so let's go over and grab that add item to list and what i'm going to do is add an item with this first one first input and then into a list with a second so let's choose a list to use i'm going to just type countries countries too and then we're going to add the item canada click save let's make sure that this works so see here's where it seems a little pointless to even have create new list because you can just add an item to the list and it automatically exists and then you can use you know what though if this didn't exist if we ran this by itself would it be capable of creating a list without having existed already let's try that so let's let's put a break point here let's run from here and remember when we do run from here it doesn't run anything beforehand so countries one won't exist as a list let's see if just putting this value into a variable automatically makes it a list countries one so it says list of general values so it it could tell that's what we were trying to do um and i suppose just to just to be absolutely sure what we should do is use uh set variable we should set this to like a you know like a one so this would be a number when we first run it countries so this is a numeric value then i set the countries one variable to a list but it's not actually there's no action here that says add to list or or create new list i'm not using that so let's run this action open up countries one yeah so it's a list of general values it seems a little pointless to even have create new list but again could be for maybe readability and just maybe for intuitiveness maybe some people are expecting to see that but it's just like increase variable and decrease variable it's not necessary okay so now let's um let's delete this because it's just complicating our flow too much create new list and then we're actually going to go remove the one from out of here there we go and add item to list let's run that from here just to make sure that it was working oh we gotta step over countries 2 has canada as a text value as one item in this list let's add a few more items so we have some test data for later we're going to put the united states into this list and add one more let's put china into countries two so countries one will have canada united states india and germany and then countries two should have canada united states and china let's run this time from the very beginning to make sure we don't have any errors we're going to let it run all the way through to the end okay so we have countries 1 which has canada united states india and germany and countries 2 which has canada united states and china the next action is sort list so let's go grab sort list and drag it on to the flow we're going to choose a list to sort i'm going to do that on two of the lists that we've already worked with so i'm going to choose countries now you might see sort by list items properties and click that and then look at what it shows and be very confused what to do let me just point out that when it says list items properties it's not referring to columns inside of the list because there is only one column in a list so it's pointless to point to a certain column i will show you after we do a couple of actions here i'm going to come right back during this section of actions under sort list i'm going to show you how to use this part and it's going to be a little more complex involving files but i think that i should go ahead and show you how to use this part without you know just skipping over it so first let's just do something simple let's sort the countries one list and then we'll copy this and paste it and we're also going to sort the random numbers list right so we've got oh we want to delete that so we just want random numbers right here save okay so i'm going to run this whole flow and it is going to let's actually put a breakpoint here break point here let's run this after it's it's run everything up to this point and it's done the break right before sorting those lists i'm gonna go over and open up countries make this window bigger just so we can see it is not sorted alphabetically either way so it's not z to a and it's not a to z because u would come at the very bottom and if it were backwards completely then you would be at the top and then let's look at random numbers so this as we saw before these numbers are in a completely random order so we want to sort those i'm going to step over this step over this both of them should be now sorted so this by default is going to sort a to z so c g i u for that order and then let's go to the random numbers same thing for this one it's done it a to z or well you know in order from in order of ascending so all three zeros are at the top and then ones and there's threes four five seven eight nine so that's sorted in order now let's go back and use sort list again but let's do it a little more complex using that other feature that i kind of glossed over a minute ago let's copy sort list and let's do list to sort we haven't created our list so let's come back to that actually um let me go over here and go down to files i think this is a good way to show that do we want folders yeah we want folders so get files in folders let me open up that folder um here c colon backslash test is what we're gonna we're gonna test on ignore the folders up here those aren't gonna be retrieved it's only gonna be these files right here and what we wanna see is that once we've sorted it we should see it in this this order of challenge challenge temp challenge temp csv um so let's go test that so c colon backslash test first let's let's uh let's see include subfolders no advanced okay we're gonna turn off all the sorting that you can do technically in this action because we're just trying to test the sort list let's save this okay i'm just going to run this one by itself okay so it got all the files in that folder put it into files so this is a list of files but it's instead of being a list of just text items it's a list of a data type or a object type called file and inside this each of these files you know it has shows something here right this is its full file path but it has more properties and more things that go along with it so if you click to see more you'll see that each of these files has different properties that we can use for it these are the properties that you'll use when you want to go sort something now if if this is not a file that you're working with that you're trying to sort and you want to sort on some property i don't have any input to give you for that unless i see that example because i don't know how this works for other types of objects and stuff files i just already knew in windows that they have properties associated with them so like if you read a bunch of a column from excel i don't know that that would have any properties associated with it if that makes sense okay so we're gonna choose um is the creation time and then maybe we'll try something else like let's say uh we could just sort it by name first or size let's do that let's do name and size yeah so sort list oh we got a stop we're going to sort the list let's put in files and we want to sort by list items properties and remember we just saw a bunch of properties so let's put in um name we're going to sort ascending first i'm going to paste this again in the second one i'm going to choose name sort by descending i'm going to copy this and paste it one more time this time i'll put in size ascending so we should see smaller files to larger files let's put a breakpoint and then right so what i'm going to do is run from here this will get all the files that are in this folder over here we have the size column and the name column so first what we should see is it's gonna sort what was it ascending so we should see it in this order let's run the action okay so we should see the list maybe if i make this window a little smaller and we can see all of it let's open up files and now it should have sorted this to be in the same order so we've got challenge challenge temp csv challenge temp xlsx crm data image text so it's sorted in the same order now we'll see we should see it flip the order this way when we run sort list descending okay so now at the top is text file and then process list image text etc and then the last thing we did was i've already forgotten oh we're sorting ascending by size so let me drag the size column over we're going to sort ascending so smallest to largest file so we should see this list change to be this order let's open up files and then it's now text file challenge temp process list crm data so this looks to be working um i wanted to do that little bit of testing i know it took a little bit of extra time but i kind of wanted to show this is one way that you can use this sort list and that it kind of just is dependent upon your having object an object type that has properties in it otherwise it's going to be you know useless the other thing is that the the documentation sometimes it seems like for net documentation matches up with things like property types and it makes sense for files the property types you'd look up in the net documentation for files is going to match up to what you'd use here but i found it to just be a lot easier to go ahead and just do a little test right do do get files in folder and then whatever it gives me from that i look inside the properties here and that's how i know what all my options are for properties rather than trying to go google what are all the properties for files you don't really need to do that you can look at it like this the next action is remove item from list so let's go grab that remove item from list in in this it looks like you can write so you can remove item by index or by value so first let's just do it by index and remember index is the number that is associated with a specific row in that list and it is zero index based so the first row is index zero second row is index one so for our countries list let's remove actually i cannot remember what was in our countries list so countries one has canada united states india and germany let's remove the let's remove germany from that list i'm going to actually add one here germany let's do japan and then what we're going to do is try to remove germany and japan but we're going to reference them by their index number for germany and the actual text for japan let's go back down to remove item from list this one will remove i've forgotten already we'll remove germany so this is index zero one two three so we'll remove index three from list countries one save i'm going to copy this and paste it and in the second one i'm just going to change this to remove item by value we'll put the value japan and remove all item occurrences we'll say true right case you have duplicates in that list remove from list countries one all right so let's put some break points remove the other break points and then let's run it when we get to this point we should see something like five countries in countries one canada germany india japan united states right and we have sorted it so that was actually important to think about so this would actually not work because i'm removing index three which would be japan and then i'm trying to remove japan it probably won't throw an error because i assume what now i have to find out so run next action this removed index 3 so now japan is not in there anymore but if i try to remove japan from this i think it won't throw an air oh it threw an air okay good to know okay let's change this to remove uh germany instead oh yeah we want to remove germany run the action is it running what is it doing oh oops okay so we remove index three from countries one which will be japan obviously you would normally not just do that without looking right you you would know why you're removing index three like you've already retrieved an x3 you've determined it shouldn't be there and now you're removing something from that spot okay run next action note that whenever you change this now index three if you were to remove index three again that would be pointing to united states because it moved one up in the list now we're going to remove all occurrences of germany from countries one so now germany is not on the list we just have canada india and united states the next action is reverse list so let's grab that action this one's pretty simple you're just going to choose a list like random numbers save that and whenever this runs it's going to reverse the order of the random numbers so let's go ahead and run everything up to this point and then once it gets to our breakpoint then we'll look at the random numbers list we'll see what order it is okay so the the random numbers are in order of a to z or um ascending so 0 to 10 and then whenever we reverse the list it should show us 10 to 0 and it does so it's pretty simple just takes your list and reverses the order now that would probably you would need to pay attention to how it's going to be ordering this because there are times when this may not order in the way you wanted so as an example if it was treating these numbers actually as text then it might sort like put it it might put this this one zero if it's treated as text it might be way down here so i'd say that's the only thing to be concerned about just make sure that you know the data that you're trying to sort that it actually makes sense but i think you'd probably check that anyways the next action is shuffle list so this would be kind of like randomize the order of a list sort of the opposite of sorting a list now let's just choose a list like random numbers and let's see what this does so i think we already have right we've already run it but let's let's go ahead and run it again everything from the beginning set the break point on the action that we want to test and it's going to run everything when we get to the point of this new action that we added it should be in reverse order because we just ran reverse list going from 10 down to zero that's the order of it so let's do shuffle list now and now random numbers is in a random order which granted if we had left it like it was originally uh before we sorted this list random numbers would already be in a random order but this is just to show what you can do let's go ahead and use shuffle list on a different one so let's say for countries one it's in its ciu so canada india united states let's use this to shuffle countries one we'll just run from here it looks like it's is that going to work it's not going to work so when i ran it again it cleared out the value so let's run from the beginning and then uh let's go look at countries so canada india united states is the order let's shuffle the list and see what it looks like afterward so now it's india united states canada not crazy different right because it's only three rows but it does work for sure the next action is merge lists let's go grab that from the left side and the two lists that we're going to merge you probably guessed it already but it's going to be countries 1 and countries 2 so let's pick that first list is going to be countries 1 second list countries 2. the variable produced is called output list so i'm going to rename this to say countries and it save this and you'll note as it said this is not going to change the original lists countries 1 countries 2 will still exist but now there's going to be a new list where it has the combined rows from each of these so let's go ahead and test that break point on that action run it like i pointed out earlier there are duplicates between the two right canada for example is in both lists but what i would expect for this to do is to not overwrite that right so i should have a duplicate of canada in the list afterward so countries as yeah canada india can canada and the united states canada united states china so there are two duplicates inside of this united states and canada the next action is subtract lists so let's go grab subtract lists and this is going to ask us again for a first list and a second list it says compare two lists and create a new list with the items that are in the first one but not in the second one what this will effectively do is uh if you say like you had a a list of countries and um you were trying to decide which of the which of them you wanted to travel to uh and then maybe your second list contains a list of countries that are uh banned for you to travel to for whatever reason um so the first list will contain say countries one and let's say countries two has a list of those countries i'm not allowed to travel to i know why i wouldn't be allowed to but i'm just saying then we want to put the results into countries and then um what we're doing is rem taking this countries one list right only countries from this will end up in the result and any countries that show up in list 2 will not show up in the list right so let's save that we will run it up to that point like we always do we'll take a look at these variables before we step over it okay so this is before we have subtracted lists this is um right countries one is india united states and canada so let's say that's a list of countries i want to travel to and then countries 2 has a list of countries for whatever reason that i'm not allowed to travel to canada united states and china so you'll see india is not in this list whenever i step over this the only thing left in the list should be india and so there it is countries only has india because it's the only one in countries 1 that is not in countries 2. the next action is clear list we just choose a list like countries and we want to clear the list right so we have right we have india in it right now we just want to clear all the countries out of it maybe we're doing it for memory sake or maybe we're doing it just to make sure that there's nothing left over when we go to work with it again later whatever the reason is you have the option to clear a list so i'm gonna set a breakpoint here run and we'll see that there is one country in that list by the time we go to run this and then right so clear list countries we've got india inside of this list i'll run the rest of it and then now you can see that all of the countries there was only one but it removed everything from that list the next action is remove duplicate items from list we are just going to choose a list let's do random numbers then there is an option that says ignore text case while searching for duplicate items i feel like they added this i do not remember this um being there you know it's a little frustrating that things get added and changed in power automate desktop without that stuff being in the in the notes for for updates if you're watching this and you and you know of where the specific notes are of every change that occurs from one version to the next for power automate desktop i would love to know that maybe it's on the forum or something i just haven't looked but is kind of frustrating when things are added and changed and i i don't i have no way of knowing that it occurred i have to go just search through everything to figure it out okay um so we're going to remove duplicate items from random numbers and right now i'm going to leave this as not like this doesn't matter what i would do here because these are numbers and numbers don't have case like uppercase and lowercase so i'm just going to save this and then we'll um we'll test this just to make sure it works so this removes duplicate items from the random numbers list when this finishes i'm going to open up remove duplic or i'm going to open up random numbers just to show you what's in here okay so we've got uh right most recently we have shuffled this list so um you can see there's multiple sevens multiple zeros multiple tens whenever we run remove duplicate items we should see all those duplicates go away there we go we've got um one two four zero ten no there's no duplicates here it's a little hard to tell because the list is shuffled so maybe it would be better to have sorted the list right like ascending or something like that first but i think it's pretty clear that it removed the duplicates now to use remove duplicate items from list to use that extra option that we saw let me open this up we're going to remove duplicates from the countries list and we're going to ignore text case while searching for duplicate items you know what actually let's do it twice the first time we will not we will make it case sensitive so ignore a text case while searching for duplicate items if this thing is the color black or this little dot is on the left side right now that means it's false it's on the right it's true so while it's false that means it is um it is going to treat two words that are the same letters but one of them has a case that's different then it's going to treat those as separate values entirely if this is set to true it treats them the same so it's going to ignore case whenever this is true so let's try both of those um i think up here yeah we are clearing the countries list so let's go ahead and copy and paste merge lists drag that down here right after remove duplicate items from list for random numbers we're going to put basically put more values into countries so that we can use that the other thing i'm going to do is add an item to the countries list so add item we actually want to type let's do canada in lowercase and we'll put this into the countries list okay so what we've done is we've added merge lists to merge countries both of those into the countries list we're adding an item to the list which is a lowercase canada so you can see it has a lowercase c and then let's put a breakpoint here and here this one is going to remove duplicate items from the list and it is going to be case sensitive this one is going to remove items from the list and it is case insensitive when we set true here so case sensitive case insensitive all right so let's run this you know one more thing i want to do is sort so let's do sort the countries list i want all the candidates to be next to each other in the list okay so let's run this right and we have just merged the list again add an item and we sorted before we remove duplicates then let's open up the countries list and we'll see that at the top we've got three rows for canada but one of them is a lower case so the first time we run this action it should treat this as a separate row of totally separate value from these two it should remove the second row it'll keep this one and it'll keep this one the same thing for united states it's going to remove this row and keep this one let's step over this open up countries okay so the first time we ran it it did remove the duplicate for canada with uppercase and a duplicate for united states but it left the lowercase canada here so if we want to do this case insensitive remember we set that value to true inside of this and we'll do run next action now open up countries and you'll see it kept the lowercase canada because it was first it removed the uppercase canada because it was it was second and considered a duplicate and it was case insensitive that's pretty cool i also i just learned that i had to look that up right before i did this because well when i say look it up i mean i actually tried it right before this and that's kind of cool i think that that was added later on i do not remember that that setting being there the next action is find common list items so let's go grab that find common list items we are going to use our countries lists so let's choose 1 and choose 2 and then variables produced we will put this into countries let's change the breakpoint locations run this okay so we are trying to find items that are common to both lists which i think is the same thing as saying inner join am i saying that right i think i am anyway uh countries one the united states canada and india countries two has canada canada united states and china so instead this time what we should come out with is canada united states inside of countries because that's common to both lists united states and canada there we go so still on find common list items it occurs to me that remove duplicate items from list has the option to ignore the case of the text but find common list items does not do that that's interesting it seems like it should so let's go ahead and test what happens when we run this when there is um an a row in here where the the case is different so let's just copy canada again and we will put bring this down here okay so we're going to add canada with lowercase c into not into countries one we want to add it into countries uh countries one yeah and so uh it should add into countries one it should find items that are common to both and let's see oh i need i need canada in both sides hmm no no we don't want to do canada we want to try one like china so china is in in countries too but not in countries one if i add china to countries 1 it's technically in both lists but only if you consider it case insensitive let's find out what happens all right so let's look at countries one it has china lowercase c countries two has china with an uppercase c let's run over this and see what comes out okay so it it's definitely case sensitive it did not put china into the output because it considers lowercase c and uppercase c to be completely different it seems like if removed duplicate items from list can be case insensitive then find common items in list should also be able to do that that could be an improvement the next action is retrieve data table column into list so this is the point where having that sub flow if you didn't watch this at the beginning of the video having this get excel data to get some data into a variable called excel data you'll want to make sure you go back and watch that and do it or you can just look at what i have here if you just replicate this it will you know get data out of excel you'll need to make sure you have an excel spreadsheet there though to begin with so um i thought i was going to use it more throughout the rest of these actions but i just didn't so that's why i put the get excel data so high up in at the beginning but it turns out i'm only using it down here so here we are but we retrieve data table column into list is the action that we want to do so let's uh drop this in the data table is going to be excel data and you can actually see the um type it says data table right here and then column name or index let's get the first oops let's get the first name column and then the variable produced let's call this column first name parameter data table argument data table must be a data table oh i did not notice that okay so just get the the variable name for the data table here and then the column name first name output to column first name okay and then let's see what that looks like okay so we're gonna run this item it retrieved the first name column and put it into uh column first name so at the top let's open up this okay so this is a list that just contains the one column from that spreadsheet let me open up the spreadsheet really quick you'll see here's the first name column john down through uh laura and so we can close this john down through laura so column first name this is the name of the list what you can technically do and i think some rpa tools make you do this it's kind of weird maybe some programming languages are intended to be worked with like this instead of having a multi-dimensional array which is effectively what a data table is you have to work with a data table which has multiple columns as entirely separate lists so you would need to extract the first name column the last name column the address column all that stuff a separate entire list and then keep them in sync so when you loop into the first row of one of the lists you loop into the first row of the second list and that kind of stuff very crazy to me i i it blows my mind that there are not better data table operations in micros in power automate desktop there there has to be eventually i'm i cannot imagine they don't add more ability to work with data tables because who wants to extract all the columns as separate lists that's ridiculous now granted we can already loop through data tables now you can do that you can get data out of it you can set data into it and stuff but there has to be better support for it it just blows my mind that there are so few ways to interact with data tables in here like you can't create a data table in here as far as i know there's no way to create a data table directly in here you have to read it out of excel to get a data table or something like that um so you know i don't know how often you actually want to use this but you you do have it as an option um you have a lot of uh there's a lot of actions you can do on lists so you if you need to do it kind of it'd just be annoying honestly to use this so personally i don't i don't think i'm ever going to use this unless i find myself just like i cannot possibly handle it another way i'm never going to use this action that's the fact that's just how it is um just to point out you can loop over this list and uh and you know loop over the rows just like you can for a data table there's there's no difference there and um have fun with this this action i guess it's maybe i should just move on i i probably shouldn't be making fun of this action so much if you see a huge benefit to this please let me know in the comments of the video because i just don't get it the next action is convert json to custom object and then after that it's going to be cut convert custom object to json so converting a string of json into an object in here and then after that converting that object back into json this is the most complex of all of these actions i'm going to show you one way that you can do this i'm in no way suggesting this is what you're limited to or even that this is the best way to do it i don't know all of the ways that this can work i've only tested it a little bit so first let me show you the json that we're going to use to create a custom object let's go into my notepad yeah so let's pretend like what you're doing is you've queried an api and the api is supposed to return to you a single result of like a person and then what you can do is manipulate this json in any way you want as far as like the you know the um values in these fields you can't necessarily add fields to it but let's say you can you change values you want in this and then you send it back to the api so let's do that now pretend like we have just received this i'm going to take this and i'm going to copy it paste it into set variable action and we we're pretending like we just called an api and retrieved this back so let's go in here do set variable let's just name this variable api response and then we're gonna paste this oops did i just mess something up no i think i'm good okay so this is what it looks like it's a little hard to tell from this view but just note that the phone number and address are nested inside of the contact info field maybe i should go ahead and point this out because you're not used to json the way this is formatted is it's wrapped by curly braces and then um in quotation marks for the fields and the values put like a field name which is id value 5321 and then a comma at the end and that's one field value pair and then first name and dave is another field value pair i guess say name value pair but whatever and then another field and a value and then contact info instead of having a single value like dave or 5321 it's got its own little object inside of it and this contains its own fields so here we have um curly braces to wrap the whole thing just like we did up here and down here inside that is field name value comma field name value and there's no comma at the end because there's no more fields after it so i just want to point that out that may not be super helpful that i pointed out but you know i don't know how much you do or don't know i pretty much just typed this out and i'm pretending like this is what the api is uh responding with as far as the structure of a person in a database or something right so we've got this here and i'm gonna hit save okay so this should set our api response variable to have that value and then what i want to do is convert json because json is a string or a text value i'm going to use the variable that i just created api response and then when we when we um when we convert this from json to the object i'm for now i'm uh i might name this person actually yeah so it's going to create an object or a variable called person and then we can work with that so let's save it okay and change my breakpoint run it so we should see a new variable pop up there's api response and it'll get set the data that i told it to so this is what it looks like okay before we have converted it into an object so this is just a string of text i know that it's got different lines but this is effectively like you could make this all in one line and that would be fine this is just one string of text now we're going to convert api response to a custom object called person i named it person remember it doesn't matter what you name this you name it anything you want to run the action unexpected end when reading object okay okay so maybe maybe power automate desktop does not handle this very well yet so let's try removing all of the um line ends i don't know why it wouldn't be able to handle that find out here in a second if that was the problem okay so i think this is all in one line now yep so there's all in one line let's see if that was the problem i do not actually know okay let's run the action unexpected end when reading object did we did we format this incorrectly i think we did yeah yeah oh that's what it is okay so let's go back in here i think what i did is i i accidentally hit backspace and i deleted this so it was expecting you know an identifier for the end of the object and it didn't have it so let's put the curly brace back copy all this so maybe because i thought that power automate had they'd changed it so it could handle multi-line strings or multi-line text let's replace all of this yeah so now you see there's two there's two curly braces now before there was only one curly brace at the end the reason you have to have two is that there's sort of this nested object right here and then the whole object like this so you have to have two sets of curly braces i think that it'll work this time and run okay so it converted it let's see where did we put it person let's go down to person open up this okay so this time instead of it being a string let me go show you what that looked like before this was the string or the text value for api response which yeah it's got the fields and the values but we don't really have a way to reach in and use those values somehow so what we do is we convert it into a custom object it just takes the json it creates an object based upon that structure and so now i can go into person and you'll see that it has a name value pairs in here and inside of contact info it has an object inside there we can click more and now there's this nested custom object inside here that has two name value pairs inside of it let me show you now how you would work with that data one of these is going to be a little bit weird um let's use set variable again we are going to set the variable actually first let's get it let's let's set this to phone number and we want to set it to let's get our custom object person now i know that there's a there's a field called like i've forgotten it already um there's a field called contact info and then inside that a field called phone number so let's get confact contact info dot phone number like that so it references the overall object and then a field inside that called contact info and a field inside that called phone number or property um note that this it would not it would not look exactly like this if you were looping through this i think you might have to use you know index numbers for um for the sort of the row within the json response if there were multiple rows um you'd have to deal with that is all i'm saying but in this case it's a pretty simple one there's there's only one uh person inside this okay so uh it changed it now you see it says set the variable phone number to person dot contact info dot phone number so you can see it understands that it's looking at properties or fields inside of this let's go ahead and test this i think i can run from here maybe [Music] of course not i wish it would just leave the values there so i could run from there uh but we have to run the whole thing again i guess you want to make sure that we are doing it correctly to get the phone number out of there so let's run the next action um okay we have our person object we would expect to get the phone number one one one two two two three three and that should get set into phone number right here there so that worked we have our phone number of one one one two two two three three um so let's say that now we we just wanna check if that phone number is correct for some reason let's do a set variable up here we'll call this new phone number we'll just set this to like one two three four five six seven eight nine zero that's a new phone number we want to set so let's check to make sure that the phone number we just retrieved is correct let's do phone number uh phone where's phone number okay so does first true or false if phone number equals new phone number so maybe to make this more clear so if the phone number is not equal to this phone number then what we want to do inside here is let's say that phone number does not equal new phone number then we will use set variable this is where it gets a little weird not sure why it does this but we know we want to set it to the value new phone number and the custom object was called oh it's person so person dot contact info dot phone number okay so you can see i typed this whole thing out i just just for the record i typed out person.contact info.phone number when i did when i tested this earlier it deleted contact info and phone number when it saved it but it's still there okay so new phone number should go into this field didn't delete it this time okay we need to keep an eye on it i think sometimes this dot contact info dot phone number will will just disappear sometimes and i don't know why okay um so if the phone number is different then we set the new phone number and then at the end when in our next action we'll convert it back but let's make sure this works first run meant to run when it stops at our breakpoint we'll look at the person objects actually so the new phone number is one two three and the person has a phone number of one one one and when we extracted that phone number out it went into phone number which is here so one one one okay so one phone number does not equal new phone number right one two three does not equal one on one so when we step it should go inside the loop and now it's going to set the value that's in that custom object by using the dot notation so if i open up person now you can see that the phone number is one two three four five six seven eight nine zero instead of 1 1 1 like it was before so new this is the new phone number okay so we've now set it in our custom object and then we can go out of this and then in the next action we'll convert it back into json pretending like we're sending it back to the api and the next action and really the last action is convert custom object to json we are going to be using the the custom object that we created when we went over this action for convert json to custom object so we have our object called the person let's convert person back into back to api and then just to be clear what this is referring to so we're going to convert the object that we created before back into the api and then once we run this so let's go and do that now we can compare to make sure that the resp the api response we got before so say we queried for a person we got a person back we want to make sure that our back to api message uh does not match what we got in before because we changed the phone number so let's see um we want to look at api response this is the one we got back initially so you can see phone number is 111 and then what we're expecting is to see a json formatted message called uh back to api okay so this one's not formatted as pretty let's just copy this and put it in a notepad and i'm going to use a plug-in called json viewer to format it okay so you can see this was the original message has one one one as the phone number and now the new message is a phone number of one two three but all the rest of it when i click back and forth between these that's the only thing that changes that's the goal it was get a json um response from an api convert it into an object that we can work with and power automate desktop change a value within that so that now it's something like this but then convert it back into json because you can't send objects uh through the internet you have to send it in a you know a supported format and json is one of those so let's um yeah so that's it um we can now take that and we could we're pretending like at this point we would send it back to the api this would be the payload or the the the body of the message that you send back to it well that's it we have gone through every single action in the variables group of power automate desktop a couple of those were kind of difficult to work with especially the the custom objects and json ones and then some of them were just extremely easy to use there are probably some other things that i could bring up about some of these but i do plan to come back and use a number of these variables actions when we go through and use like conditionals and loops and flow control and stuff like that we will inevitably use a bunch of these actions all over again if you have any questions about any specific ones that i i didn't you know go over a certain scenario or whatever feel free to drop a comment and ask i try to respond sometimes i miss the comments for months but i'll try to notice it right away and respond and as always if you have something that you want to suggest something i should change then let me know if there's something i did wrong that's especially what i want to know if there's a better way to do things and stuff like that but for now thanks so much for watching and i'll see you in the next video

Channel: Dave The RPA Guy

Views: 5,592

Keywords: Power Automate, Power Automate Desktop, PAD, RPA, Robotic Process Automation, Robotic Processing Automation, IPA, ML, Machine Learning, AI, Artificial Intelligence, Intelligent Automation, Automation, Technology, Innovation, Application Development, Dave The RPA Guy, Tutorial, Actions

Id: sdsV66By3Mk

Length: 80min 19sec (4819 seconds)

Published: Sun Dec 05 2021