How do you do a parameter query when not using Microsoft Query?

[Matt Appel]

I've been scouring the internet for the better part of a week trying to figure this out, so I'm hoping there's an Excel/data ninja here that can help.

Problem: I have several SQL queries embedded in my workbook, but need the end user to be able to pass values from cells to the query. For the most part, it'll be a couple of simple values, such as start date, end date, and username. Most of the solutions I've found say to go to Connection Properties > Definitions and add parameters, then add them to the query at the bottom of the box, but this field is always greyed out for me - whether it's regarding a Query or a connection.

Additionally, the query I'm running is a monster (I'm trying to make the server do the processing rather than Excel on my local machine), so I need a solution that lets me pass values to a complex query, not just a single table.

This can't be very difficult, but I can't for the life of me get any of the answers I've found to work. Also, many of the answers I've found on StackOverflow and other forums are 6+ years old.

Attempted Solutions:

1. This similar post was answered by telling the user to add parameters in the Connection Properties > Definitions> Command Text box. When I go there, that entire tab is grayed out. I've found ways to setup parameters, but can't figure out how to add them to the query without kicking a SQL error at runtime.
2. This support.office.com page has instructions that should be valid for my version of Excel (2016), but again, they suggest adding parameters and the entire Definitions tab is grayed out. (Also, the images in this page show the ribbon/icons with a blue background, which is from an older version of Excel. So, something was updated?)
3. This post has what looks like the answer, but again, they're trying to edit the query in the Definitions tab, which
4. I've also seen solutions that involved entering a string of code that looked like VBA code into the query, but those would always just cause syntax errors. I don't have any examples of these handy.

What I'm hoping for:

I would be eternally grateful for a simple explanation as to why the Definition tab is always grayed out, and a guide on how to make this work.

I'm running Excel 2016 (Office365) and the SQL Server 2008.

Thanks in advance!

[#2]

Hi @Matt Appel

How have you embedded the queries?

Is using Power Query an option for you?

If so you could paste the SQL into the "Advanced" option box when connecting to your database.

I've an article here showing how to use a named cell as a parameter for Power Query

https://accessanalytic.com.au/powerquery_namedcells_parameters/

[#3]

I've imported them via Data> Get & Transform Data > Get Data > From Database > SQL Server. From here I add the server, DB, and the query. Usually I'll load to a worksheet, but I've tried loading to a connection only, but have the same issues.

This is a good guide, and I think I'm a step closer now, but it still doesn't provide a way to feed my values into the query before sending to SQL for processing. When I get to the Advanced Query editor the existing code doesn't have the same fields as in your example. Keep in mind this is a very simple query I'm using just to get this figured out, then I'll bring out the real, big one.

I'm not against learning how to modify the code, but I'd like to have avoided that seemingly unnecessary complexity.

[#4]

Matt, it looks like

    StartDate = Excel.CurrentWorkbook(){[Name="StartDate"]}[Content][Column1]{0},
    EndDate = Excel.CurrentWorkbook(){[Name="EndDate"]}[Content][Column1]{0},

    Source = Sql.Database("Server", "Database",
      [Query="SELECT * From Invoices Where Workdate BETWEEN '" & Date.ToText(StartDate) & "' AND '" & Date.ToText(EndDate)] & "'")

[#5]

@Matt Appel  did you ever figure this out?  It is driving me absolutely bananas.

[#6]

Sorry, @bwdur I never did. I've poured over every forum and StackOverflow post I could find and there's always solutions posted (like the one above), but I can never get them to work. 

To update this thread, I'm still running the latest version of Office 365 Edition of Excel, the SQL databases I'm querying are SQL Server 2016 and 2008.  PowerQuery is now built into Excel, and i'm using it extensively, but there's still not a clear way to pull a value from a cell and inject it into the SQL code before sending it to the server. 

The workbook that spawned all this is still in use and has about 8 different SQL queries that all need the date updated to refresh. What I ended up doing is setting the dates in each query to variables, then just updating the variable dates each time I need to pull data. It's not ideal, but at least it works. 

Edit: added some clarification

[#7]

@Matt Appel 

Crazy this used to work fine and dandy with Microsoft Query but lately all of my spreadsheets have been breaking randomly.  I did just get this to work finally on a file using this thread with some modifications.

First I named some cells.

Then I went down the 2349 clicks to Advanced Editor and did what the above poster did, except I had to put DateTime instead of Date.

let
StartDate = Excel.CurrentWorkbook(){[Name="StartDate"]}[Content][Column1]{0},
EndDate = Excel.CurrentWorkbook(){[Name="EndDate"]}[Content][Column1]{0},
Source =

Sql.Database("server", "database", [Query="select myColumn from myTable where MyDate between '" & DateTime.ToText(StartDate) &"' and '" & DateTime.ToText(EndDate) & "' and Annoyed = yes"])
in
Source

The location of the ' and " really matter around the parameters to get it to go.  I'm surprised there aren't more threads about this online honestly, it is way too difficult to do.  I guess maybe other people are returning the entire table then filtering afterwards in Excel? 

[#8]

@bwdur 

I guess the issue was since on the time of previous post Power Query returned Excel date as date, and now as datetime. I'd don't rely on default behavior, it's better to force date value and use date format converting dates to texts, like

let
    Source = Excel.CurrentWorkbook(),
    StartDate = Date.From(Source{[Name="StartDate"]}[Content][Column1]{0}),
    EndDate = Date.From(Source{[Name="EndDate"]}[Content][Column1]{0}),

    SQLstart = Date.ToText(StartDate,"yyyy-mm-dd"),
    SQLend = Date.ToText(EndDate,"yyyy-mm-dd")
...

[#9]

This works! You beautiful genius!

Here's the code I ended up using. The only thing I'm changing are the server IP and database name:

let
StartDate = Excel.CurrentWorkbook(){[Name="StartDate"]}[Content][Column1]{0},
EndDate = Excel.CurrentWorkbook(){[Name="EndDate"]}[Content][Column1]{0},
Source = Sql.Database("192.168.1.1", "MyDatabase", [Query="SELECT TOP 1000 * FROM Invoices#(lf) WHERE InvoiceDate BETWEEN '" & DateTime.ToText(StartDate) & "' and '" & DateTime.ToText(EndDate) & "' ORDER BY WorkDate DESC"])
in
Source

For anyone else trying to figure out what the heck is happening here, the reason the " and ' are so critical and finnicky is because they're breaking up the text to send it to the right place. The " characters are switching between the primary coding language (M, I think) and SQL, and the ' need to be passed to SQL for the dates to work.

In straight SQL this query would look something like:

SELECT * FROM Invoices WHERE WorkDate BETWEEN '2020-01-01' AND '2020-02-01'

So we need to keep the ' between a pair of " so they're passed to SQL. If they're missing it'll throw an error. The & are concatenating the date values into the string that eventually is passed to the SQL engine. 

Hopefully that makes sense. 

I'm still a little unclear why there's the need for [Content][Column1]{0} after declaring each variable, but whatever. 

Thanks again, @bwdur 

[#10]

@Matt Appel 

 [Content][Column1]{0} is just combining several steps into one. Alternative step by step way using only user interface, except this first step where we call current workbook

Here we may filter on StartDate

and click on Table in Content column to expand it

Right click on first cell and drill it down

Result is

Generated code will be

let
    Source = Excel.CurrentWorkbook(),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "StartDate")),
    StartDate = #"Filtered Rows"{[Name="StartDate"]}[Content],
    Column1 = StartDate{0}[Column1]
in
    Column1

That's okay to do all above steps manually first few times, but most probably one day you'll want to open Advanced Editor and combine all together

let
    Source = Excel.CurrentWorkbook(),
    StartDate = Source{[Name="StartDate"]}[Content]{0}[Column1]
    //#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "StartDate")),
    //StartDate = #"Filtered Rows"{[Name="StartDate"]}[Content],
    //Column1 = StartDate{0}[Column1]
in
    StartDate

It will be more understandable if you know concept of lists and records and how to work with them in Power Query. That's another story.

[#11]

@Matt Appel no problem.  I've been trying to fix this on and off for a year but everything was just too complicated for the simple pulls I wanted to do.  I'm glad we figured it out!

[#12]

@Sergei Baklan Thanks that'll surely "future proof" things.