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

[rhawlor]

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 into the query and manually changing the invoice number?

e.g.

Table 1 with 1 column 1 row value = ABC123

Existing SQL Query select X,Y,Z from Database where value = Value from Table 1 (ABC123)

I did try to search for this, but I wasnt sure how to word this search for this this type of function, so apologies if this already exists. 

thank you

Rob

[smpa01]

@rhawlor  it works for me not sure why it does not for you.

Can you delete the follwoing portion and try

[rhawlor]

I have deleted that portion and added the previous again. Still the same outcome.

When i click ok oi get the below:

[KNP]

Yep, that one got me a few times too.

Try changing the '"&Table[Current-invoice]{0}&"' to '"&Table[#"Current-invoice"]{0}&"'.

The dash can cause a problem when referencing column names.

I recommend not using the dash or other symbols in column names.

[rhawlor]

I have amended the code and now get past the original error and now get the Table wasnt recogonised. 

As per your recommendation, I then went and changed the column name from "Current-Invoice" to "CurrentInvoice" to see if this would help, but as it is trying to find the actual table i don't think at this stage the column name is the problem.

Below is what the Current-Invoice Table with CurrentInvoice column looks like:

Below is the final code with SQL:

[KNP]

This looks like your issue. Helps now that I know what the table is called. 😉

Change the above to...

'"& #"Current-Invoice[CurrentInvoice]{0} &"'

If you renamed your query you wouldn't need to reference the table with the #"".

Pay close attention to the quotes. Opens with single, double. Closes with double, single.

Make sure you don't remove the last ]) that closes off the "Query".

I think this will get you there.

[rhawlor]

So i amended the name, but it didnt like the extra ' at the end as you can see...

I removed the extra ' from the end the, PQ then started loading then came up with the below:

Though the name is spelled correctly?

[rhawlor]

Hi @KNP - Do you have any further suggestions on this, I think we are so close on this.

[KNP]

@rhawlor - sorry, I think this is my typo that has confused things.

I think it should be...

'"& #"Current-Invoice"[CurrentInvoice]{0} &"'

It was missing the double quote to close off the table reference.

Give that a try and let me know how you go.

[rhawlor]

@KNP Thanks for the reply. 

I have added the change: It says there is an invalid literal

Here is the query section, not sure if this makes it easier?

[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 #(lf)from rdwadmin.rdw_repair_details rd, rdwadmin.rdw_part_master pm, rdwadmin.rdw_asc_customer_master acm, rdwadmin.rdw_asc_site ras#(lf)where rd.moto_part_number = pm.moto_part_number#(lf)AND rd.ASC_CUSTOMER_NUMBER = acm.ASC_CUSTOMER_NUMBER #(lf)AND rd.ASC_SITE_ID = ras.ASC_SITE_ID #(lf)AND rd.INVOICE_NUMBER = '"& #"Current-Invoice"[CurrentInvoice]{0} &"'])

Thanks, really apprecite your help on this 🙂

[KNP · Accepted solution]

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} &"'

"]

[rhawlor]

Good spot on the table ref, it wasnt an issue as i have used this via sql into excel previously, but now i know its there I have amended it 🙂

I think we are almsot there! It started to work and then came up with something i have never seen before:

[rhawlor]

@KNP Thank you this is now fixed. I did the old google on the firewall piece and it was down to the privacy settings. - I can't thank you enough as this will save so much time!

Anyone else who comes across the firewall issue, just amend your privacy settings.

[KNP]

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]

@rhawlor  did you have a chance look into above?

[rhawlor]

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]

@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

[rhawlor]

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]

@rhawlor  can you try this

rd.INVOICE_NUMBER='"&Table[Current-invoice]{0}&"'"

[rhawlor]

@smpa01 

This didnt work and caused "Expression.SyntaxError: Invalid identifier."