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

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
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,559)