Power Automate Exchange

Please login or click SIGN UP FOR FREE to create your FlowUG account to join this user group.
 View Only
  • 1.  Advice or Help - cmparing data from two tables

    Posted Nov 05, 2021 05:08 AM
    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.


    Roisin Conlon

  • 2.  RE: Advice or Help - cmparing data from two tables

    Top Contributor
    Posted Nov 17, 2021 07:21 AM

    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:

    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