Add a row into a table - Only works if Resubmitted

[nhabel]

I have a flow that is capturing Form responses from 6 different forms and compiling them into a single Excel Document for dashboarding. Up until this week, the flow was working perfectly, however out of the blue the "Add a row into a table" step has stopped adding the row but subsequent steps (condition & email) go through and the flow overall shows successful. Nothing has changed in the automation or in the Excel table. Here's the kicker, if I manually resubmit the flow it works just fine. It is not just one flow either, so far we've confirmed the issue on 3 of the 6 flows. I did rewrite the step but it is still not going through unless I manually resubmit it. 

Here is the formula from the Output step which calculates the start date based on which form was created, in this example it brings the date back to Monday of that same week (form completed on Fridays).

formatDateTime(addDays(utcNow(),-4),'MM-dd-yyyy')

[wskinnermctc]

How many rows are in the table? Power Automate has a difficult time with large tables that have 1000's of rows. 

It could also be that the form submissions are happening too rapidly. When updating an Excel document from PowerAutomate it can lock the file for up to 6 minutes, or updates may have delays.

It's possible the flow is working, but the Excel file is not updated fast enough, so when the next flow runs it is basically overwriting the same row.

So check how many rows are in the table, and check how frequently the flows are adding rows to the table.

You can review more information at the Microsoft Learn Excel Connector page.

If this process is substantial, you could consider using a SharePoint List instead of Excel Table to store responses. You could even look into using Dataverse or Dataverse for Teams Tables. Using Excel as a database usually causes problems in the long run when dealing with data connections or automation.

[nhabel]

Currently only 167 rows in the table. Also, responses don't typically come through back to back and I can resubmit the flow immediately and it works.

[FLMike]

Hello,

I am perplexed by this also. I would like to make a few suggestions and a question.

Question: Does your file have a Table, that is exactly the size of how many rows are in it? The reason I ask is, if you run the flow 1 time. And go check the file, does it appear to have Added a row, but its blank? And then the second time it fills in the blank slow, or is it just "not added". Because technically add row is two (more really) internal steps. Expanding the Table and then putting the data in. Just want to see if the first step does anything, so you can tell support as well.

1. Open a ticket: Power Automate Support | Microsoft Power Platform and it seems as if its not necessarily your issue, so there should be no charge to you for opening the ticket and getting support. 

2. Please verify permissions haven't changed. This is the #1 reason I find weird stuff happens, even though it seems to say it worked.

3. I know you said, it says the flow went through, but did you verify in the Step itself, in the Run history, that it actually "appears" to have updated/added a row? As in when you look at the output of what it said it did, does it say it did it and didn't?

4. Lastly this is a potential short-term workaround until you are able to resolve so it doesn't impact your business. Unfortunately, since I couldn't repro your issue. I am going on a hunch here and would suggest trying it on one of the flows, see below.

Possible: Short-term workaround.

1. After your Add Row, add a list rows in table and verify (using a filter hopefully) that your row is there

2. If it is not, try to add it again, if it is just move on.

Again I know its not what you want to have to do, but putting in this condition + retry may at least get you buy until its time. Heck that or you could..

Add a Do until, set the # of retries to be some configurable number or environment variables (again super short term).

In the Do until

Add the row.

condition => Check (list rows) if its there

If so exit the Do Until

If not try again (up until your configured retries)

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[nhabel]

Great suggestions! We did end up getting it to work by removing the compose date step so whatever the issue, it stems from that step. Again it was working for a while and just out of the blue stopped so not sure what's up with the compose step. Seems to be a fairly straightforward step

formatDateTime(addDays(utcNow(),-4),'MM-dd-yyyy')

We also confirmed that the column in the table is a date column.

[FLMike]

Hi @nhabel 

Was there an actual error that you saw and then when you removed the compose it went away?

Normally issues like this with dates means some form of

1) the date was blank or had a value that wouldn't be converted so it fails (making your flow fail)

2) the date didn't meet ISO standards (and fails)

3) the date format doesn't work inside the "store" in this case Excel and it says no

Can't think of too many more that would be caused by this.

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[nhabel]

No all steps show successful with no errors and the output appeared to be just fine but no line added until we removed the compose step.