Answered

Comparing two excel tables and extracting into new table, do until? Apply to each?

(1) Share

Report

Posted on by ID-30090116-0

Hello! I'm quite new to PA but I am really enjoying it. That is until I spent 6 hours on this issue haha! Please help!

I have 2 very big Excel lists. My flow needs to compare them. If the item from table 1 is present in table 2, it add it to table 3. Right now, what my flow does is the following:

Lists all rows in Table1
Apply to each row of T1
1. Output: List of items (value)
2. Lists all rows in Table 2
3. Apply to each row of T2
4. 1. Output: List of items (value)
  2. Condition
  3. 1. If Item# (from T1) is equal to Code# (from T2)
    2. 1. Add a row to Table 3 (adds all information present in that row from T2)
    3. If false
    4. 1. Nothing

Now, it does work.... but it takes hours! My tables are really big. I have filtered them as much as I could, and the flow now runs for about 2 hours before I end up with my new table (T3) with all the extracted information I wanted from T2).

I looked at the option to use do until so that it might take a bit less time and stop that loop once it find the right row to move on the the next item in T1, but I must be doing something wrong because, depending on where I place my "add a row into a table", it either:

add the first row from T2 to infinity and beyond
lists all rows from T2 until it finds the one I'm looking for.

Can anyone help me to get an efficient way of comparing my 2 lists and extracting the rows with same item #?

Thanks so much!

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (1)

Verified answer

Daniel Bocklandt 5,099 Super User 2025 Season 2 on at

Like (3)

Report

Hey,

Could you try the following?

Instead of listing all the Data from both lists and comparring all the values against each other, I would try to get one list and then filer it on the seocnd list.

This will work if those are unique values.

To accomplish that you would get both tables into your flow and then use this operation:

(second initale will be replaced by Value of T1)

Filter Array you need to put one list at the top and Select what column will be filtered on.

Like this you will have only one apply to each.

To make it even faster you could try to work on the concurrency of the apply to each. You can find it under the seetings of apply to each. There you can turn it on.

Carefull, turning it on and putting the degree of parallelism to high will make the flow slower. You would need to test to find the best Value. I would advise not going much above 20.

The concurrency controlls will make the several records filter at the same time

Let me know if it helped you or if you have any other questions.

If it did solve your problem, please accept this answer as solution so others can find it as well.

If it helped in any other way consider liking it so we can keep supporting each other.

Was this reply helpful? Yes No
ID-30090116-0 6 on at

Like (1)

Report

Thank you so much @DBO_DV! My husband (programmer) kept asking if there was a way to filter and I was like.. i have no idea!! lol!

So I'm trying.. and it is forcing a second "apply to each" on me when I use "add a row"

Was this reply helpful? Yes No
Daniel Bocklandt 5,099 Super User 2025 Season 2 on at

Like (1)

Report

Hey I'm sorry I didn't get any notification about your response.

So I went ahead and marked this question as solved since the other apply to each is normal.

Since the filter could return several rows.

But if you use Unique ID's what I assume you did you will always just get one record back.

Power Automate is smart but not smart enough to know that so he will always through an apply to each in there just so he can be sure.

If you've got any further question don't hesitate to come back to this topic i will check in on it a few times this week just to be sure that there is no notification being missed again.

Was this reply helpful? Yes No
Suggested answer

Michael E. Gernaey 53,369 Super User 2025 Season 2 on at

Like (0)

Report

HI

If you do it the way it is on the thread, it works, but you have to do a Condition, that checks the length of the returned filtered Array, to see if it came back with 1 or 0.

Assuming they are unique, just filtering isnt enough.

Also depends on how many rows you have and if you need pagination or not.

Was this reply helpful? Yes No
Suggested answer

SaiRT14 1,990 Super User 2025 Season 2 on at

Like (0)

Report
Option - using the Dataverse connector with a Service Principal is the preferred approach.

If you need more flexibility or control over your requests, or if you prefer working directly with APIs, using HTTP requests to the Dataverse API is a viable option.

Option-1

register an Application in Azure AD (Tenant 2):

Go to Azure AD in Tenant 2 and register an application.

Obtain the Client ID and Client Secret.

Set the required API permissions for Dataverse (Dynamics CRM API).

Add your Service Principal to Dataverse in Environment B and assign the necessary security roles (like System Administrator).

Authenticate Using the Service Principal:

In Power Automate, use the Dataverse connector to authenticate using the Service Principal.

You can now access the Dataverse tables from Environment B.

Use Dataverse Actions:

After the SPN is authenticated, you can use native Dataverse actions like Get a row, List rows, Update a row, etc., to query and modify records in the Dataverse table in Environment B.

Security:

Using SPN is more secure than using a user account for cross-tenant access, and it can also help with automating processes without needing a specific user's credentials.

SPNs are ideal for non-interactive service accounts that can be used for backend integrations.

Was this reply helpful? Yes No
ID-30090116-0 6 on at

Like (0)

Report

Hi ! I'm so sorry, I finally just got back to this automation. Here is what I'm trying at the moment. It's been running for the past 40 minutes but there is nothing added in the table yet. I'm hoping it'll work, it would save me a lt of time.

In my second Apply to each, should it be the second "list rows" output or the "filter array" output?

Thank you so much btw. It was working fine in September but not anymore :/

Was this reply helpful? Yes No