Power Automate Exchange

Hi Guys,

Looking for a bit of guidance or steer on in this one if anyone can help.

I need to do something when a row with a specific value is updated on SQL on Prem.  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.

What I have to done is use a cmbination of schedule, and then transfrorm data with power query, delay and transform data again to get 2 sets of data to compare  - but not so great so far.

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 column 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 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

------------------------------
Roisin Conlon
------------------------------

What might make your life easier, is to do your first query and delay, then your second query I think you'll have to write the raw SQL, as I don't think you can pass parameters to power query. By writing the SQL directly you can do something like

select * from table1 where primarykey= apply_to_each.item()?['primarykey']

using the output from this will still trigger an apply to each, so you'll have to reference the output via an expression:
body('SQLQuery')?['resultsets'].Table1[0].DateColumn

or if you want to get crafty do your comparison in SQL as well
declare @datecolumnone nvarchar(max)
declare @datecolumntwo nvarchar(max)​​

set @datecolumnone ='apply_to_each.item()?['primarykey']'

set @datecolumntwo=(select datecolumn from table1 where primarykey= apply_to_each.item()?['primarykey'])

select case when @datecolumnone=@datecolumntwo then 'Same Value' else 'Different Value' end as DateComparisionOutput

you can then take this SQL output and move your flow along based on the DateComparisionOutput value 

------------------------------
Sam Duval
Data Quality Analyst
Element Financial
Indianapolis IN
------------------------------

Original Message:
Sent: Nov 05, 2021 05:08 AM
From: Roisin Conlon
Subject: Advice or Help - cmparing data from two tables

Hi Guys,

Looking for a bit of guidance or steer on in this one if anyone can help.

I need to do something when a row with a specific value is updated on SQL on Prem.  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.

What I have to done is use a cmbination of schedule, and then transfrorm data with power query, delay and transform data again to get 2 sets of data to compare  - but not so great so far.

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 column 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 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

------------------------------
Roisin Conlon
------------------------------