how to export more than one record from power apps as excel using filters in power automate

[Subarna]

Hi,

Here I have a collection which contains certain records from which audit ID acts as a unique record , I am passing this collection into power automate from power apps . My requirement is I have to separate this audit Id and lookup this audit Id in another table and fetch the details relevant to that table and export it as excel , how can I achieve the same?

[creativeopinion]

@Subarna 

Concat ID's into a String with a Separator

Use the Concat() function to gather all the ID's from your collection into a string encompassing each ID with a separator. You can use a comma, semi-colon, pipe. Anything really.

Formula:

Concat(colSchedule, "| " & ID & " ")&"|"

The output should look something like:

| 1 | 5 | 12 | 22 | 55 |

Get ID's from PowerApps

Pass the string of ID's from PowerApps into Power Automate using the Text Input in the PowerApps (V2) trigger.

For troubleshooting purposes, add a Compose action and store the outputs from the Trigger.

Run a test. Check the output of the Compose action. Ensure that your IDs look like they do below. Each separated by a pipe character.

List Rows in Excel Table

Add a List rows present in a table action to your flow.

Filter Rows with a Filter Array Action

Add a Filter Array action to your flow. You can refer to this section of my YT Tutorial on how to use a Filter Array action.

In the From field, insert the value dynamic content from the List rows present in a table action. In the first value field, insert the Outputs from the Compose action (storing the string of IDs from PowerApps). 

Change the operator to contains. 

You'll use this Filter Array action to check if any of the IDs in your Excel table are contained inside the string of ID's from your PowerApp. The reason the IDs are wrapped with a pipe is so that you can use a Filter Array action to search for the entire ID including the pipe. This will prevent the Filter Array action from returning Excel Row items that may contain a partial match (since you are using the contains operator).

For that reason, you need to use an expression to wrap the ID from excel with a pipe. Remember that, the PowerApp formula used above includes a space after the pipe (left side) and before the pipe (right side). 

This is the expression I used. You will need to replace the dynamic content key with the appropriate content key from your Excel table.

concat('| ',item()?['id'],' |')

Return Count of Items

Whenever I use a Filter Array action, I always like to return the count of items returned in a Compose action. This is helpful when building a flow and can also be used to troubleshoot your flow.

Insert a Compose action. Add an Expression. Use the length() function.

Select the Dynamic content tab and insert the value dynamic content from the Get Items action into the length() function.

Run a test. Verify the number of items returned.

Condition Check

Add a Condition action to your flow. In the first value field insert the output from the Compose action above and change the operator to is not equal to. In the second value field enter 0.

This way, you can first confirm items have been found in your Excel table before moving on. All of your other actions can go into the YES branch.

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response!

👉 Watch my tutorials on YouTube
👉 Tips and Tricks on TikTok