Forum Discussion
How do you do a parameter query when not using Microsoft Query?
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?
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
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!
[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 Column1That'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 StartDateIt 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.
