Power Automate Exchange

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

    Posted 22 days ago
    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
    ------------------------------


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

    Top Contributor
    Posted 10 days ago

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