Power Automate - Community Question - How to update a row of dynamic Excel file?

hi this video is about Microsoft power automate Community thread so this is the thread we are looking here the user is looking for how to update a dynamic Excel file so here you can see there is an update arrow action step here and there is a key column and a table here but if you look the file name it is dynamically it comes in so it when the Excel file is dynamic how can we update it you can see the last part here provide the item property properties so this is where this is where I'm going to focus my video on okay so let's start building the flow so for the demo I got an employee table here I'll show you that in a minute here it is and I have got ID which is my key column because in any database if you want to update a row or a record you need to have a unique identifier okay and then that's my unique identifier then I have got name age and postcode suppose I want to update the postcode so that's a plan let me close this foreign so the first thing I'm going to do here is I'm going to use Excel online business then looking for update Arrow okay the location it is in one drive okay find the file is in my OneDrive the directory is called demo okay so I'm going to put slash demo slash then the file name is dynamic so to create that scenario what I'm going to do is similar similar like in the thread I'm going to add a compost and adding the file name here the file name is called employee dot xlsx okay so employee Dot xlsx then under here slash demo slash then mapping that file name which is almost similar like here also here the file name dot xlsx here yeah and certain path also here then there is a table associated with that so I have created a table also for that Excel file and my table it's called EMP so here for that say don't come up in the drop down list because it's Dynamic I need to select add as custom item okay and here I'm going to call the table name as EMP that's our table name which I created for that Excel sheet then the key column so to update the key column you need to know the unique identifier so again I'm going to say add a custom item because it's not exposed it's all Dynamic here I'm going to make ID that's my unique ID here so in this example here if you're using this here the key is there is you can see here the key column is key and 0 I believe 0 is the unique identifier there okay then the next thing I'm going to do here is I'm going to update my first let's take the second one yeah second key value here so let me open the Excel sheet again and see what's the current value there okay the second one it's kl22 k i okay let me close that all right so here for that provide the item properties so this is the this is where we need to focus now how can we provide that values here for the update so for that you need to put curly brackets open and close then between that you need to put double quotes then you can provide that column name so that is postcode that's my column name there colon then double quotes and then you know you can specify the value you want so I'm going to put here like um l 1 8 9 H okay and then removing this line also here or if it's coming from the dynamic one what you could do is you can replace this with click on the dynamic and use the value there authorities yeah that's where it's coming from the previous step I believe in my case I don't have anything coming from the previous step that's the reason I'm hard coding the value here okay let me save this flow and run run the flow now Okay click test manually and click test again okay looks like flow runs successfully let's let's let's expand the step now so you can see here update arrow and here see the body here so it knows what key value 2 based on that even though I only given the postcode as l189h rest of the values it's all been pulled out in that body action step once update is done yeah so that means it it has updated that value here yeah so let me go back to the Excel sheet and make sure that is updated thank you here it is so that's updated there and if you want to update the age from 27 to 35 or something so let me show you that as well so did the flow and here you need to put comma and then you know put that um column name and that column name is called h and it's it's a numeric column so I don't need to put that as a hardcoded value there like sorry string value so I'm going to make it 35 like that yeah click a test manually and save and test okay looks like a floor and successfully here you can see here it should update at the age also now along with the same as that postcode which I typed earlier yep okay let's go back to my OneDrive and make sure the value is correct here it is so it's updated the age as well so the main thing you need to remember here is the syntax of that update item properties yeah provide the item property properties so that is two curly brackets then the column names in double quotes give the column name then the values and if it's a numeric column you can leave it as um you know we don't need the double quotes so that's where you update a dynamic uh you know Excel sheet but it still need still you need the table without the table there are no operations you can it can't be done under power automate so make sure it should have a table and you know this is a technique you can use for updating the Excel sheet hope this is useful thank you for watching

Channel: abm abm

Views: 6,047

Keywords: Dynamic filename, Excel, Power Automate, Table, Update, flow, Microsoft flow

Id: peYNaU43_dg

Length: 7min 19sec (439 seconds)

Published: Tue Jul 25 2023