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

2018

PowerQuery

sql

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

Forum Discussion

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

Resources