Flow returning multiple excel rows

[Anonymous]

Hi All

i hope you are well and please let me start by apologising if this is old hat to many of you, however, i am new to this.

I am endevouring to create a flow that will update multiple rows on a master excel work book from a differnt workbook.

I have built a basic flow triggered by a timed recurrence, its a simple flow that comprises of get row (15 columns) and update row (15 Columns), however i want to do this for all rows within the work sheet....... i have been told that this is not possible and that i will have to do each row individually.

is this the case or is there a simpler way of doing this.

[leo85]

Hi @Anonymous ,

welcome to Power Automate!

Could you please post a screenshot of your flow, so it's easier to understand what you currently have built?

You cannot bulk update multiple rows in Power Automate. You need to create a loop and do it one by one.

Try the following steps:

1. Get all rows from the source Excel with "List rows in table"

2. Create an action "Update row" selecting the target Excel and choosing the output from the "List rows in table" action. This will automatically create a loop, which iterates over all tables in the source Excel.

You will need a key column in both Excel sheets, which enable the flow to identify which row it needs to update.

Alternatively, it may be easier to update the row every time the source Excel is edited instead of by a schedule. This way updates will be more or less in real time. Additionally, you only ever have one row that you need to update, so you do not need to iterate through the table in the source Excel.

Regards,

Leo

----------------------------------------------------

If this solved your problem, please mark my answer as complete.

If what I wrote helped you, please give it a thumbs up.

[Anonymous]

Hi Leo

Thanks for getting back to me so quickly, its really appreciated. as i said i am new to power automate. however i have tried the steps you have mentioned and the flow fails.

i am somewhat confused as per the attached images i list rows in a table, then update row, however when i selcet number which is our unique identifier for each row it adds that and #value when testing the flow it then fails. what am i doing wrong 😪

[leo85]

Hi @Anonymous ,

you now have two loops ("Apply to each" and "Apply to each 12"). You only need one.

Power Automate automatically creates a loop when you pass a parameter to an action that is an item in an array. The output "List rows present in a table" is an array (meaning a list) of items. You get one list item for each row. Each item has multiple parameters, depending on the number of columns you have. If you create the action "Update a row" and select a column from the "List rows present in a table" action, Power Automate knows that this is part of an array and automatically creates the respective loop. Otherwise it would not know which row of the column you are referring to.

The value is the array-object that the loop iterates over. If you hover with your cursor over the "value" it should read "outputs('List_rows_present_in_a_table')?['body/value']".

To update a row, Power Automate needs to know which row exactly to update. This means your Excel needs an identifier, like the ID you mentioned.

Your flow should look like this:

Steps:

1. Delete both loops you currently have.

2. Create an action "Update a row"

3. Under "Key Column" select your number column from the dropdown

4. Under "Key Value" select the Number column from the dynamic content. Once you have done this the loop will be created automatically.

5. Select the other information from the dynamic content as you need.

I hope this helps.

----------------------------------------------------

If this solved your problem, please mark my answer as complete.

If what I wrote helped you, please give it a thumbs up.

[Anonymous]

Hi @leo85 

That has helped a lot and its amazing how quick i am learning .......but i still keep getting an error and flow failed any ideas?

[leo85 · Accepted solution]

Hi @Anonymous ,

The error message tells you what the issue is. It says "Excel table already has a row with Id '2'". So there is something wrong with your ID.

In your screenshot it looks like the "key value" is hard coded as "1". This means currently the loop updates row number 1 multiple times. Since you want to update all rows, this should be the dynamic content from the "Ident" column.

Try to put what is currently in the "Ident" field into the "Key Value" field. Note: If you leave a field empty, this field will not be updated. So if you only want to update a couple of fields, you do not need to fill in all of the fields, but only those you want to update.

[Anonymous]

Hi @leo85 

Thats superb and has sorted the flow failure out as per the image. i still cant get the data from one to another though which is strange and my flow keeps getting throttled (what is the action limit?). so i will percivere and see where this takes me next....thank you for your help

[Anonymous]

Hi Everyone, 

I'm looking to get assistance on how to use power automate function to update and copy certain columns from 2 excel files a new third sheet. 

In more detail: I have created a new excel sheet which i want to be my goto file, labelled as Final Excel Sheet. This final excel sheet will pull in data from both excel sample data sheet 1 and excel sample data sheet 2. Both sample data sheets have varying data but common column in both is Name. 

I would like to automate this process as the files "sample data sheet 1 & 2" in reality are updated weekly. 

   

thank you for your help and guidance in this inquiry. 

[Hakim_Bouc]

nice

[BinuMathew]

Thank you for the explanation. I need a bit different solution.

The flow is set to run every day in the morning.

1. list rows in excel table
2. apply to each row - send email to the Focalpoint, if "Email Flag" = Flagged. This works fine
3. Add current date in "Email Date" column, if the "Email Flag" = Flagged (as in the previous step  email was sent). This is to avoid re-sending the email since the flow is set to run every day morning. Only 2 rows are getting updated here

Currently, I put Key Column = "Email Flag", Key Value = "Flagged" and Email Date = Date

• How to make this flow work for all rows where Email Flag = Flagged.
• Is it possible to club steps 2 and 3 as the row can be updated immediately after sending email so that you don't need to loop through the table a second time.

Thank you in advance.