Power Automate Exchange

Please login or click SIGN UP FOR FREE to create your FlowUG account to join this user group.
Expand all | Collapse all

Power Automate SQL Power Query help

  • 1.  Power Automate SQL Power Query help

    Bronze Contributor
    Posted Oct 22, 2020 07:40 AM

    Please help.

     

    We have a SQL database that get populated with form responses.

    The way it has been set up is a little strange.

    There are two separate Tables.

     

    One Table consist of Unique ID.

    Example:

    ID,          SUBMISSION ID,              FORM URL,         FORM DATE

    1             120                                      form3.aspx                       

    2             121                                      form4.aspx                       

    3             122                                      form300.aspx                   

     

    Second Table consist of the answers from the form (but in separate rows)

    Example:

    ID           SUBMISSION ID                QN         VALUE

    400        120                                      1             Mrs Smith

    401        120                                      2             Boston

    402        120                                      3             Manager

    403        120                                      4             Message

    404        121                                      1             James

    405        121                                      2             Yes

    406        122                                      1             Sally      

    407        122                                      2             I Confirm

     

    So we would need to set the trigger for one table but pull out responses from another table.

    Because the response return as multiple rows in the second table, I'm needing to use power query to pivot the table to just have one row per submission.

     

    So on power Query I've done this:

     

    SUBMISSION ID                QN1                      QN2                      QN3                      FORM URL

    120                                      Mrs Smith            Boston                 Manager              form3.aspx

    121                                      James                   Yes                                                      Form4.aspx

    122                                      Sally                      I Confirm                                           Form300.aspx

     

    On Power automate, my trigger is when an item is created on a table

     

    My flow looks like this: 

     

     

    Then I need it to create an item on Sharepoint with those details.

     

     

    How do I limit the power query rows to only show the row from the trigger and not all the other?

    Because now, it's just creating multiple items for all rows, over and over again everytime it's triggered.

     

    I hope that makes sense!

    Please help




    Would you like to be kept up to date with Blackpool Council news? Sign up here - www.blackpool.gov.uk/YourBlackpool http://www.blackpool.gov.uk/EmailDisclaimer/ This message has been scanned for inappropriate or malicious content as part of the Council's e-mail and Internet policies.


    This message may contain confidential information. If you are not the intended recipient please inform the sender that you have received the message in error before deleting it. Please do not disclose, copy or distribute information in this e-mail or take any action in relation to its contents. To do so is strictly prohibited and may be unlawful. Thank you for your co-operation.




  • 2.  RE: Power Automate SQL Power Query help

    Top Contributor
    Posted Oct 23, 2020 07:26 AM
    You should have access to trigger fields which will correspond to the fields in your table. I don't think you can pass dynamic values to the power query function, so you'll probably have to do a filter array with your power query output as the filter input.

    You could also put it in a for each loop and condition it if x=rows if yes insert into the list else do nothing

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



  • 3.  RE: Power Automate SQL Power Query help

    Top Contributor
    Posted Oct 26, 2020 03:49 AM
    In power query you can add a separate query to take the top row of the same table as your flow trigger, when sorted by id in descending order, then you can convert the submission id column to a list and take the first item as a string, then use that query as a variable in another query to filter the table to get all items with the corresponding submission id. If you can't figure it out I might be able to give you some some sample M code tomorrow.

    ------------------------------
    Will Page
    Technical consultant
    Christchurch, NZ
    ------------------------------



  • 4.  RE: Power Automate SQL Power Query help

    Posted 21 days ago
    Hi Kassie

    We're based in Blackpool if we can offer some help.

    https://www.nedwos.co.uk/help

    Andrew S

    ------------------------------
    --
    This and related documents are "Secret" as defined by the UK IPO, EU Trade Secrets Directive, and WTO TRIPS Agreement.

    All communications are subject to the Communications Policy available at https://www.nedwos.co.uk/legal/CommunicationsPolicy where Company, Limited Liability Partnership and Business (Names and Trading Disclosures) Regulations 2015 information can also be seen.

    Please consider the environment before printing this email.
    ------------------------------