cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mcnemare1
Frequent Visitor

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
Memorable Member
Memorable Member

@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 Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Top Solution Authors
Top Kudoed Authors
Users online (1,713)