'Update a row' in excel not moving to next row.

[_NT_]

My goal is to copy this data from excel file A:

into this table on excel file B:

My current flow looks like this:

What this does is repeatedly update only the first row of my paste table, overwriting the data repeatedly. What I want it to do is update the first row, then move to the next and update that one, then the next, etc. etc.

How do I get the 'Update a row' in my flow above to update all rows continuously so that all data is fully copied from one table to the other?

[DAllen365 · Accepted solution]

@_NT_, I believe what you want to do is use the Add a row to a table instead of the update row. Your destination Excel needs to be formatted with a table, and you insert rows into that table.  Update a row is used to only update an existing single row in a table at a specific row ID.  It will never move on to another row unless you did crazy counting.  You just want to say foreach row in Excel A, insert rows of that data into existing table of Excel B.  

----------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

[_NT_]

@DAllen365 Thank you for your reply. The reason I am trying to update the row is because I have pre existing formulas in file B that I want to do calculations with upon pasting the data from file A to B. Otherwise, I will have to learn how to include formulas when I update the new row so the formulas are included. But from your response, it sounds like 'Update a row' does not move to the next row so I'll have to go with "Add a row to a table' and learn how to insert formulas with my new row. Thanks!

[DAllen365]

@_NT_ Oh ok I can see why you would want to do an update if you want to maintain existing formulas in the table.  I would think it would be possible to do that then, but it would take more effort.  It wasn't moving on before because your key value was manually typed as 1.  Try making that number dynamic to the row ID of the source but I am unsure if that would work.  Or you would need to count the number of rows from Excel A like using length() function, then use like a do until and count the number up from 1 to that number (e.g. do until source count = current count).  Inside the do until is the update using the current count as the row ID.  Or you could move your columns with the formulas outside the table and include in the table later (that wouldnt scale if this is a recurring process).  Glad I was helpful!

----------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

[GeoffRen]

Another solution is to use Office Scripts, specifically Office Scripts with Power Automate. This is the 'Run Script' action on the Excel connector. With Office Scripts you can use the 'Run Script' action to execute javascript against a workbook. You can manipulate essentially anything in the workbook in any way you want. So here, you can pass the Excel file A data in as a parameter to the script and then paste it in to Excel file B in the exact way you want. You can even remove the formulas and do the calculations solely in the script. This has the advantage of being more portable. If you decided you wanted this same functionality in another Excel file, you would just need to change the file target since the script does everything else for you. Even if you don't have experience coding, Office Scripts can help generate a script by recording your actions via the Action Recorder.