cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mcnemare1
Helper III
Helper III

Sql Statement to filter Excel Table with dynamic data

I have 2 lists that will be changing every day. 

 

List A are Sales requests from clients (From Legacy System) 

 

List B is our CRM System Data Dump (excel b.c. cheap)

 

I am Creating List C, D, E, F, and G. Using the Sales Requests and Legacy system information.

 

We are using PAD because everything needs to stay on network.

 

They only key that links both lists is the account # field. 

 

Originally my flow was using PAD if actions as shown below to see if 

 

IF Excel List A Current Item [Account Number] = List B Current Item  [Account Number] 

Then do X,Y,andZ

 

This was taking forever to run, so, I after some research I am thinking a about pulling in the big excel table with 33k rows 

Via

1.) Open SQL connection to an excel file.

2.) Execute SQL Statement to filter for rows the Account numbers in List A (which is always smaller)?

3. Use for each to loop through each table as I had it before...???

 

I am racking my brain attempting to create a filter for the account numbers via the SQL statement, it would have to be a variable since its changing. What are your guys thoughts? 

 

Any issues with #3?

 

PAD excel if.JPGexcel if flow taking foreverexcel if flow taking forever

 

 

 

2 REPLIES 2
takolota
Super User
Super User

@mcnemare1 

 

Are you trying to update the Excel with values or calculated/transformed values from SQL?

 

What is the reason for needing to stay on network? Some kind of security or something that would prevent the use of cloud flows?

@takolota 

 

Sorry for the belated response. 

 

It has determined that since CRM report holds customer sensitive information, that any flows created should be in-network and cloud flows avoided.  

 

Correct I am trying to filter excel with calculated values from SQL statement.

 

Since List A 'Sales Requests' (excel file) is always going to be smaller than List B 'CRM' Excel File. 

 

I'm thinking it would be easier to filter List B 'CRM' by 'List A' Sales Requests. Via SQL first to reduce the initial loops are taking in PAD. thoughts?

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,439)