Excel – How to set a cell value using the result of a parameterized SQL query using Power Query

microsoft excelmicrosoft-excel-2016power-query

I have a worksheet with columns A, B and C. Then I need to run a query on my SQL Server using the values from columns A and B to find the value that should be placed on column C.

I've created the query like this:

SELECT * FROM Tablename

Then added the filtering steps considering two parameters I've created on Power Query Editor.

Now, how can I connect this existing worksheet to the query, passing the cells values as parameters to get the result (single row)?

UPDATE

Adding more info…

My worksheet is like this:

Column A | Column B | Column C
   1     |  223     |    ?
   2     |  545     |    ?
   8     |  542     |    ?

Then my database table has all of these 3 columns (along with several others that are not important), I need compare columns A and B from the worksheet with the equivalent columns A and B in the database table, to return column C value from the database (without importing the entire table into a new worksheet).

Best Answer

Based on what I best understand your question, it appears that you have successfully created a Parameterrized Query and most likely you have put ? for the Field Value and you are able to retrieve the results by passing values to it.

Now you need to refer the Cell Value as Parameter values.

Try these steps.

  1. Select the Table Go to Design Tab and go to Properties
  2. Click the button next to Connection Name. enter image description here
  3. Go to Definition Tab. If you have correctly created a Parameterized Query, Parameter Button should be enabled for you.
  4. For the specified parameters now select radio button 'Get the value from the following cell' and enable Refresh automatically when cell value changes. enter image description here
  5. Click OK on all Dialog Boxes and exit.

Now ideally the query shall run everytime you update the values in your Excel sheet cells.

See the below example based on MS Access database. I hope the steps should be quite similar on SQL Server too. Do explore and experiment further. Hope this helps.

enter image description here

enter image description here

Related Question