Forum Discussion

Copper Contributor

Jun 21, 2018

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

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...

MVP

Jun 22, 2018

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/

Matt Appel

Copper Contributor

Jun 22, 2018

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.

SergeiBaklan

Diamond Contributor

Jun 22, 2018

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)] & "'")