Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Reference a value in a table to use in another SQL power Query

Hi All   I have a value held in another PQ table. 1 row 1 column containing an invoice number. - Is it possible to use this particular invoice number and use in a SQL query without having to go int...
Need Help
Tips and Tricks
  • KNP's avatar
    KNP
    4 years ago

    Double quote missing before the closing square bracket I think. 

    I notice you're missing a table alias for your SHIP_DATE_RETURN, not sure if that will cause you any abiguity issues.

    This is just the query portion, make sure it goes inside the correct ().

    [Query="
SELECT rd.SERIAL_NUMBER
     , rd.REF_NUM
     , rd.MODEL_ASC
     , pm.MODEL
     , rd.MOTO_PART_NUMBER
     , rd.ASC_SITE_ID
     , ras.ASC_SITE_NAME
     , rd.REPAIR_TYPE
     , acm.ASC_CUSTOMER_NAME
     , rd.WARR_CODE
     , rd.RMA_CREATION_DATE
     , rd.EXPIRY_DATE
     , rd.RECV_DATE
     , rd.SHIP_DATE_RETURN
     , rd.UNIT_STATUS
     , rd.PROJECT_NAME
     , rd.FINAL_UNIT_DETERMINATION
     , rd.INVOICE_NUMBER
     , rd.INVOICE_DATE
     , ROUND((SHIP_DATE_RETURN - rd.RECV_DATE)) AS AGE
     , (rd.RECV_DATE + 30)                      AS TAT
     , rd.LABCOST
     , rd.FREIGHT_COST
FROM rdwadmin.rdw_repair_details rd
   , rdwadmin.rdw_part_master pm
   , rdwadmin.rdw_asc_customer_master acm
   , rdwadmin.rdw_asc_site ras
WHERE rd.moto_part_number = pm.moto_part_number
      AND rd.ASC_CUSTOMER_NUMBER = acm.ASC_CUSTOMER_NUMBER
      AND rd.ASC_SITE_ID = ras.ASC_SITE_ID
      AND rd.INVOICE_NUMBER = '"& #"Current-Invoice"[CurrentInvoice]{0} &"'

"]

     

KNP's avatar
KNP
Super User
4 years ago

From memory, the syntax for the value in SQL is '" &Value& "'.

Depending on your exact scenario, you may want to drill down on that single value table to make it easier to reference, e.g. right click on the single value and choose 'drill down'.

 

Once you've drilled down, say the query of single value is called myValue.

 

I think what you're looking for is something like...

 

SELECT x, y, z FROM myTable WHERE myColumn = '" &myValue& "'

 

 

Hope this helps.

 

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    KNP  and if OP wants to pass on directly a query to SQL

     

     

    let
    Source = Sql.Database("serverName", "dbName", [Query="select * from tbl where WO='"&Table[C1]{0}&"'"])
in
    Source

     

     

     

    Table is following

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi smpa01 

       

      Apologies, i only just read your reply. I have tried to pass to the SQL query and did have a couple of issues with PQ not liking the code, but it seems to have accepted an edited version. 

       

      However it does not bring any data back.

       

       

      Really appreciate your support 🙂

      • smpa01's avatar
        smpa01
        Community Champion
        4 years ago

        Anonymous  can you try this

        rd.INVOICE_NUMBER='"&Table[Current-invoice]{0}&"'"
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi KNP

     

    Thanks for the reply. This is the current scenario is:

     

    We have an Excel spreadsheet which has a powerquery into our oracle database, each time I want to validate an invoice, I have to go into the SQL query and manually change the invoice number for which I want to validate then refresh the query afterwards.

     

    What i was thinking was add the invoice number to a single tab in Excel, then have this loaded as a PQ table like below:

     

     

    In a seperate table I have SQL Query which pulls all the rows of data that relate the invoice reference that is held in 'Current-Invoice' after hitting refresh.

     

    Hope that all makes sense ğŸ™‚

     

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    Anonymous  did you have a chance look into above?