Pass Parameter from Excel Cell to SQL Query

[Shortmeister1]

Hi

I have a query in SQL, an excerpt of which might look something like

DECLARE @myDate DATETIME
SET @myDate = '2020-10-31'
SELECT * FROM myTable WHERE myDate <= @myDate + 1

I want to populate a cell in Excel (e.g. A1) named range "myCell" that will feed through to the SQL variable instead of setting it in the SQL code.

I've not really worked with parameters in Power Query before and a lot of searching has left me with solutions that are similar/cause syntax errors in the M code, and a great big headache.

Can someone point me in the right direction please?

Thanks

 

[Shortmeister1]

Ah ha! Thanks Smozgur.

Thanks to both of you.

I've finally managed to get it to work and I couldn't have done it without your collective help. (How does one mark up all your comments as the solution?)

I used the Text option in Excel. then ditched the parameters, sub queries etc. and loaded it as:

= Sql.Database("SERVERNAME",
               "REPORTING_DB", 
               [Query="DROP TABLE IF EXISTS #TRAN_ALL;#(lf)#(tab)DECLARE @As_At_Date    DATETIME#(lf)SET @As_At_Date = '" & Excel.CurrentWorkbook(){[Name="AS_AT_DATE"]}[Content]{0}[Column1] & "' #(lf)SET @Status= 'ALL' ..."

A few more parameters to add and I've got my working interim model...

 

[smozgur]

Good to hear it is solved.

How does one mark up all your comments as the solution?

Only one post can be marked as solution.
You can change the solution post by clicking on another one that helped you better.
And you can always Like the posts that you like or helps you.