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

Comparing data from two tables

Hi Guys,

 

I need to do something when a row with a specific value is updated on SQL on Prem. like send an email.  As I can't use the built in trigger for when a row is modified due to the limitations with using via the on-orem gateway i'm trying to work around this.

Using a combination of schedule, and then transfrorm data with power query, delay I am able to get 2 sets of data to compare.  Here is where I get stuck

The  queries do return the right information but I am absolutely struggling with the flow steps to compare these two tables.
The ouputs are body/value and do contain all the columns I need tocompare however every which way I try and check for changes I get stuck in endless apply to each loops.

The query returns approx 800 rows, the columns contain the primary key and date column I need to compare, e.g key, date.

I need to get key 001 from the first query and check if the date is the same or different to key 001 on the second query and so one until I get through each row. 
I can do this fine using two different datasources eg excel or sharepoint but can't seem to get this to work here.


Hoping someone can help with a suggestion or guidance.


Cheers
RC

2 REPLIES 2
VJR
Super User
Super User

Hi @Roisinc21 

 

I need to get key 001 from the first query and check if the date is the same or different to key 001 on the second query

Could you check if using a subquery and the SQL "in" operator could be handy in this case?

 

Example:

Here there are two queries where the result of one query is checked in the result of another query.



SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers);

 

You can add "where" clauses along with your dates and Key 001.

If the query returns 0 then there are no matching rows, else a non-zero number would indicate a match is found.

 

SQL IN Operator (w3schools.com)

 

Roisinc21
Frequent Visitor

I can't execute a sql query via PowerAutomate due to the limitation of the on-prem gateway

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.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

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.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

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