Answered

Issues with Apply to Each when trying to update excel tables from an API

(0) Share

Report

Posted on by Community Power Pla...

Hi there,

I'm attempting to build a flow which goes to an external API retrieves a list of recent tickets, compares this list of tickets to an excel sheet containing all previous tickets to ensure there are no duplicates and then adds any new tickets to the excel sheet. So far I've tried two methods neither of which seem to have worked, I beleive because the flow adds in extra apply to each action where they aren't needed.

1. Filter Array on the Json file retrieved from the API by ID does not equal ID from the excel file. The use this filtered array in an add row action.

2. Use a condition action to check whether the ID from the API data is not equal to the ID from the excel sheet and if yes add row to the excel table:

The issue I am getting with both of these solutions is that they add multiple identical rows to the excel table. I assume this is something to do with the apply to each looping through the actions multiple times but I can't work out how to get around them.

Thanks for all your help!

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (1)

DamoBird365 8,942 Microsoft Employee on at

Like (1)

Report

Hi @Anonymous

You want to perform an except on two arrays first, to work out which ID's exist in one but not the other, then using that new array, work through them in an apply to each and create the new rows.

I have an example of this is action here

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Hi @DamoBird365

Thanks for your help, I've got to the point where I've filtered the array, however I'm now struggling to use the add row to into a table action with the output of the filter array action. For whatever reason the output from the filter array doesn't pop up in the dynamic content section:

Full flow for clarity:

Thanks for your help so far!

Was this reply helpful? Yes No
DamoBird365 8,942 Microsoft Employee on at

Like (0)

Report

HI @Anonymous

Without worrying about the row to table action, does your filter return what you expect in the history of the flow? Is it all of the fields that you require but based on the new ID's only? I think your expression should be output does not contains item() but it looks like you have output does not contain output.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

@DamoBird365

Not entirely sure what you mean by history of the flow (sorry a bit of a newbie to Power Automate). I've checked in the output of the filter array action and it seems to include the fields I'm needing:

I had a check of trying to swap the filter array to output does not contain item() but was struggling to work out how to input an item variable into the second box as nothing other than output appears in the dynamic content options:

Thanks,

Was this reply helpful? Yes No
Verified answer

DamoBird365 8,942 Microsoft Employee on at

Like (1)

Report

Hi @Anonymous

WHen providing data to an action, you need to either supply a dynamic content or you can use expressions. The particular expression item() simply returns the first item in the array during the condition check on all items of that array. If you click on the expression tab and type item() the press OK or Update (slips my mind right now), it will convert that to dynamic content for you.

Then during your apply to each with your array as the source to that apply to each, it should give you the dynamic content for the keys within the array. If not, you might have to use the parse JSON action which will need a copy of the output from your filter array as a "payload" and then it should identify those elusive keys for you as dynamic content.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here

Was this reply helpful? Yes No
Community Power Pla... on at

Like (1)

Report

After having to play around with the schema's in the parse Json action (not sure why filtering it changes it's schema but nevermind) this seems to have worked perfectly.

Thanks very much for all your help @DamoBird365 , greatly appreciated!

Was this reply helpful? Yes No