write to excel table flow only working the first time its run

[David2831]

I have an excel file with a table I want to write too. Once my data is written, I email it to the user, then overwrite the excel file with an identical blank copy so that I have an empty table ready for the next run of the flow.

But for some reason after the flow runs once successfully, future runs always end up emailing with empty tables because of an API error.  I don't know how to solve this.  I think its because I am overwriting the target file with an empty copy, even though the excel files are identical.

I just need some way to populate the table, email it out, and reset for the next flow run.  I thought of saving my data to a CSV and using PQ to import that into the excel file, but it seems that to refresh the PQ connection, I would have to use a desktop flow, we don't have those licenses.

Any ideas?

[abm]

Hi @David2831 

Believe its a new excel empty file for the next run you have a new table Id, yes its the same table name but Id is different hence you getting this error. Try to create the table dynamically. Have a look at my video tutorial.

https://www.youtube.com/watch?v=Q4Q_OWEa-Jw&t=133s

Thanks

[David2831]

I am trying your solution in my flow to see if it will work, but its changing my available options in a way I don't know how to answer.

Before the "Add a row" action would detect the available columns and ask me to map values into each.  With this setup I instead get a single required "Row" field, and no idea how to fill it.  Can you give me an example to work from?  Google isn't helping.

Also, I used the dynamic content "Name" from the table name value in create table.  I assume that's the key to making this work.

[abm · Accepted solution]

Hi @David2831 

Since the table name is dynamic Power Automate will ask for Row value to update. It won't display the column names dynamically. Here is another video I made. Hopefully this will resolve the issue.

https://www.youtube.com/watch?v=pKKk-sgyDYE&t=36s

Thanks

[David2831]

Double post mistake, sorry.

[David2831]

Thank you very much for your continued help with this.  I have followed your advice and watch the videos you linked.  I have everything working the way you suggested, but unfortunately the "Create Table" action seems to have a similar issue to the "Add a Row" action with excel.  The flow will work once after I reset everything, but a second run will fail at the create table action with a 404 resource not found error.

By 'reset everything', I mean that I first place a fresh copy of my file in the target folder.  Then I go into the flow and on each of the 2 excel actions I click the folder icon at the end of the 'File' setting and point it to the excel file again.  This seems to reset the actions so that they will find the new file.  After this the flow will run smoothly and I am emailed the result.  I have reviewed the email attachment and the file arrives with all the correct data in the proper places.

But I then wait about 5 minutes and run the flow again, and it loops on my error check at the "Create Table" action until I cancel it.

Can you offer any additional advice?

 

[abm]

Hi @David2831 

What's your trigger? Can't you use when a new file is created as trigger?

Thanks

[David2831]

Its manual trigger, this report is run on demand.  The excel file is always just sitting there, waiting for the report to be run.

[David2831]

To be more clear, here are the major steps of my flow =

1. A user that has been given access to the flow kicks it off, and gives start and end dates.

2.  The flow pulls records from dataverse and does some logic to get the values we need, and arranges the data into an array variable.

3. The table in the excel file is created, and the array is written to the table in excel.

4. The excel file (now full of data) is copied to an archive location and renamed to include the date it was run.

5. An empty copy of the excel file is copied over the original one used in step 3 above, this empties/resets it for the next run of this flow.

6. A copy of the filled excel file in the archive is emailed to the user that originally started the flow.

End of flow.

Hopefully this gives you an idea of what I am doing.  My problem is that step 3 fails on the second run of this flow.

[David2831]

For anyone in the future that has this issue, It has now been resolved.  The fix was to use the create table action as ABM recommended, and also to replace the File option with a compose dynamic output, instead of using the file icon to select the exact file.

The compose line is simply the path and filename of my excel file, copy / pasted from the file line above.  This seems to create enough separation for the add row action that it doesn't hard code the excel file ID into the code, instead dynamically finding it each time the flow is run.

My flow works correctly now every time, thank you ABM!

[abm]

Hi @David2831 

Thanks for the screenshare. So what we did is change the flow to copy the existing Excel file before it start inserting the records. This means the we always have a new file to start with. Again for the file and table name use the dynamic mappings. Glad to see that all working as expected.

Thanks