Power Automate - Save all failed items from a loop into single file

Question

I have Power Automate flow that takes data from Excel table and inserts it to SharePoint List. This works correctly, however due to user errors (e.g. inserting text into number columns) or SharePoint validation rules (all ID numbers must be unique to prevent duplication) some of the items fail and are not inserted.

I want to be able to generate a csv/excel dated file with all failed items and the reasons for failure after each flow so user can check data rows that failed and amend the issues with the data.

So if Action Fails and row of data from excel table cannot be added to the SharePoint I want it to generate new dated excel file (if this is the first error in current flow) and add the Failed Action row data with the error message plus any other Failed Action and their error messages.

What do I need to add to my current flow in order to achieve this?

Answer 1

You could do something like this.

In your loop you add two scopes: "Try" and "Catch" with the Try scope as the first one. You now pull in your Create Item action into the "Try" scope.

Click on the settings (3 dots) for the Catch scope and select "Configure run after". Set the run after to only "Has failed" (this step is very important as it ensures that the Catch scope only is executed in those iteration of the loop that fails).

In the Catch scope you add a Filter Array action and as input you put result('Try') in the condition in the Filter Array action you put item() is equal to Failed.

Then insert a compose action to compose the error message from the result of the failed try scope. Use this formula to get the error message:

split(body('Filter_array_-_get_error_message_from_outcome_of_try_scope')[0]['outputs']['body']['message'],'client')[0]

Finally add an Add a row to Table action so that you can create a row with the error message and other references needed for the users.