How to update Excel sheets from Power Automate - Ultimate guide

[Music] hello it was quite a while ago that i published a video on youtube called how to update an excel sheet from microsoft flow and it was just a few days ago i got a comment under that video saying hey dude i mean it was supposed to be updating an excel sheet or excel table but you actually didn't cover that all you covered was to connect to an excel table from power automate and insert the record where is the update and i looked at that video and say yep that's right so today is the day that i want to go through updating a record inside an excel table from power automate as much as i typically say it's easy to do when it comes to working with excel and power automate especially when it goes to update there are so many odd situations that it makes it actually a little bit more complicated than saying it's easy to do so today i want to go through quite a few of those weird scenarios so basically what i'm trying to do i'm trying to create an excel sheet and i want to update the unit price of the products in an excel sheet and i want to add 10 percent to the unit price of the products that their price is above 100 dollars sounds complex enough let's do it okay we start by creating a table in excel i created this range of content with id product name and the unit price and i converted it to the table so i selected all of that click on the format as table and that's it now if you go to the design view you will see that i also gave this table a name called product sample right so i just save it so that i can work with it i upload it to sharepoint inside sharepoint i created a library called demo lib and i want to upload this file called product sample to sharepoint let me just click on it make sure we uploaded the right file perfect so let me close this and we go back to power automate and start our work we start by creating a flow that i'm gonna fire it as instant flow and i call it update excel file v2 and i select manually trigger flow and i click on create so the flow is created let's start by updating one record first so i go back to my excel and inside my excel sheet for example the price tag of polo t-shirt that the id is 5. let me go back to flow now we need to create an action that updates an excel row so under excel i pick up update row i need the url of this site not the whole url just make sure you pick the url of the site excel excel actions are not that smart to trim it to the side url so i can scroll down and i click on enter custom value and i just paste the url here we are all good and then on the document library i pick up demo lib the file that i need to pick is product sample and the table is going to be product sample that we created inside the excel sheet this is the moment that the updater role action is asking you which row you want to update of course i can pick any field and in this one i pick the id and i need to say which row based on the key column is supposed to be updated if you take a look at this excel sheet that we have here you see i have for example id five so apollo t-shirt let me change the price to something like for example fifty dollars so inside flow when the id is five uh set the unit price to 50. and that's all i click on save and let's run a test i click on continue run flow and apparently it's successful let's go back to our excel sheet and see the polo shirt has been updated to 50 so far so good now the main question is that okay what if this id is not unique so for example i want to update something that the id is 3 and i also pick for example jacket and id is 3. so now if i ask flow to go to excel and update the rows that the id is 3 what do you expect what i was expecting was either to go there and update both of them or throw an error that this value is not unique so i cannot really locate a unique record for this let's see how flow reacts to it so i need to update the record with the id 3. i go back to my power automate i go on edit and this time the key value is 3 and i want to set the unit price to 577 for example let me just save it and run another test save and test run flow completed successfully all green icons let's go there and see what we got how do you like this top is updated to 577 jacket did not change let me just refresh it just to assure you that it's not my mistake or i'm not waiting for any update so you see top is updated jacket is not fantastic the most unexpected answer so that's your responsibility to make sure that the id is unique i would say if there is only one thing that you want to pick up from this video is this make sure you have a unique id in case you decide to update tables in excel from power automate and if you don't do that you're dealing with some data inconsistency that doesn't return you any error completely silent so let's fix the thing and continue now the next thing that i want to do i want to add something to every product that the unit price is above 100 for example like 10 percent to the unit price of anything that is above 100 first i need to pick up everything that has the value of above 100 for this i go back to flow and if you're already familiar with with odata you know most probably i want to use all data and capture only those few records that they fit my criteria so right before update i need to add an action and again i go to excel and this time i want to list rows present in a table when i do that exactly the same way except for the fact that this time i really don't need to use the url because if i use it once i have it in the list sharepoint site flow course and i pick up the document library which is demo lib i pick up the same file and for the table there is only product sample here if i run this it will return all the rows which i don't really need while i have something called filter query using odata i can pick up the rows that they have my criteria for any item that unit price is above 100 great if you're not familiar with all data i already have another video on youtube about odata and using it in power automate i've put the link right on the top of the page and you can look it up and take a look later at the moment i don't want you to interrupt this and go with that and you will know in a minute i put the filter query and the filter query is going to be unit price greater than 100 great i click on save and let's test it save and test run flow done great we got an error so here is the funny part invalid filter clause which basically means that guys we know that gt or greater than is a valid oh data statement but sorry v only support equal to not equal to contains start with and end with that's it is currently supported hopefully in the future we will get more okay so for this one using the o data we are out of luck what is the answer for this you know what forget about it we don't need this there is no other way we have to bring all the records here so let's filter it after we bring it here so we come here i just add an action oh by the way in the same table in the same list rows presenting table if i use the filter query and i put something like equals to it perfectly works regardless that's not going to solve our problem we read all the values in the table and we need to filter them here using data operations and i need to use filter array of course the list of rows is an array so if i get the values from the returned returned items i have an array and this is the value that i get from less rows present in a table let me just pick it up and now i can put my filter criteria filter criteria now it's very easy because now i need to have unit price is greater than for example 100. great let me just save it and see if this filter array does the job and i get the two items i mean this one and this one that the unit price is above 100. so let's run it save test save and test and run flow done great another error which brings us to another weird behavior not from filter array but from excel and here is the reason there's a type mismatch the number that we added here as 100 is considered as integer while what we get from excel is a string no surprise excel does not have data types now what is the solution again we need to write an expression convert this unit price to a number and then we compare it with the number that we have if that's the case let's go for it by the way if you are not familiar with flow expressions i have a udemy course on flow expressions that again i will put the link right on the top take a look if you are interested in dealing with complex expressions to write the expression i click on here and i click on expression now i need to use the mathematical functions that if you just scroll down you will find it here and the function that i want to use is called float because it's a currency regardless and then while the cursor is right here i click on dynamic content and again we don't have anything here so for that again i go back to expression and i need to use the item function that picks up the current row of the array which is being processed and i need the unit price of that so basically we got the current item unit price and we casted it to float now we can compare it with this again dealing with excel is not very straightforward fantastic hopefully this time it works let me just save it and i click on test save and test run the flow done this time i see success but let's see what we got on the filter array let's see we got product number three which is the top unit price is this fantastic and we got the jacket which has this unit price exactly the same two items that they have the unit price of above 100 so far so good so we cut the items that we want to update great now we need to navigate through every single item and run the update after that after you got whatever you want from the filter array now there are two ways to work with it so first of all before we run the flow flow does not have a clue of what comes out of this filter array so if you want to access the output from the filter array visually we need to make it visual if i come here for example if i want to add for example the key value here from the filter array you do not find anything here because filter array does not have it for example if i you look for id you will find id under list rows present in a table but you will not find it under filter array although it has it but before runtime flow does not have a clue of verities so there are two ways ahead of you first of all all my expression freak friends you can write expressions and extract it from the json that this filter array returns which is not for this video the second way is that we get the json output from this filter array and parse it to something more visual that we can play with let's see how i do it i just run another test save and test and click on run flow done so if i scroll down this is the output that filter array produces i copy it and keep it in my clipboard then i go to edit now i have a sample of the return values of this filter array then i use another action called parse json and if i click on the parse json it says hey what do you want me to get the content from i say okay get it from the filter array from body and this guy say okay what is the schema for this okay generate from sample sample is what we put in the clipboard so basically the output that we got after the first runtime from filter array so i just paste it here click on done and it generated the schema now what happens now the output from parse json based on the sample data knows what fields we have on the filter array so now if i go to update and i pick the id i have this id from parse json which is actually the id of the values from the filter array and actually those are the items that have the unit price of above 100. so if this is the case let me just add the id here the id from parse.json which is the filtered value because it's an array as soon as i add this it puts it inside and apply to each and that's fantastic because right after that i can pick the unit price from the source and add it here now we were not supposed to get the unit price and reassign it we were supposed to add 10 to the unit price and reassign it to the field so we logically need to take it out and put the cursor here go to expression and say multiply 1.1 by and then we were supposed to go back here to dynamic content and pick up unit price under parse.json but we don't see it here that's another problem sometimes i call it a bug sometimes i call it okay they are trying to simplify too much but this is one of those times that flow acts weird i actually have another video that shows you this behavior and how to go around it but that trick does not work when it comes to expressions so logically i should say if you cast it to float and i'm looking for a string i should be able to see it but again sometimes it acts silly and you don't see it now here is the trick to go around it now let me click on the dynamic content again so it's going to show me everything so i just reassign the unit price so so far i get the unit price from parse.json and just assign it to the updater rule fantastic if i just can get this value and multiply it by 1.1 i'm done so here's the hack i save it and i click on this three dots here and i go peak code remember the field that i was looking for was unit price so if i scroll down under the fields that i have i have item unit price that this one is the value just remember exclude this add sign just use everything that comes after that so it goes under items that they are coming from apply to each and picks the unit price right click copy and this is the unit price that we want to add great once we are done i don't need this one anymore i go to expressions and i comfortably write my multiply function so multiply 1.1 comma float because what i'm getting is a string and i just copy and paste it here let me put it in notepad so that you can see it not bad and here so multiply 1.1 by float of this string that i just stole it from peak code done we go back here click on ok and now i have an expression i save it and it's time to test but before that i go back to my file because i want to remember the values so let me just write the numbers here 577 was this number the other one was 459.99 so these numbers should be added by 10 inside the table great so let's go and test it test i'll perform the trigger action test and the run flow done it's an apply to each so it takes a while to execute ta-da now let's go back here and you see each one of them has added 10 how do you like it i don't know about you but i am tired that was a lot to cover anyway i try to cover as much as i possibly can when it comes to weird behaviors when it comes to working with power automate and excel thank you for watching i hope you enjoyed the video till next video stay safe you

Channel: Alireza Aliabadi

Views: 81,418

Keywords: update excel from power automate, microsoft power automate introduction, power automate tutorial for beginners, power automate demo, power automate tutorial, power automate excel, update excell from power automate, update excel fomr power automate, Delete excel rows from power automate, Update excel from microsoft Flow, Insert delete update excel table from Power automate

Id: FprcZWGJ1F4

Length: 23min 59sec (1439 seconds)

Published: Sat Aug 29 2020