Forum Discussion
How do you do a parameter query when not using Microsoft Query?
- Hi John,
Keep going it's worth it!
To address your first point, Close and Load to... Connection Only. Those are actually 2 separate steps. In your screen shot your Load To.. is greyed out as you must have already loaded that query once and now you were editing it. In that case you did the right thing and used the Right Click option to access Load To... Connection Only
On the second issue you should (I think) resolve this by going to New Query - > Query Options - > Privacy > Always Ignore Privacy Level settings. There's a hyperlink there as well if you want to find out more about what turning off Privacy warnings does, however it is fairly standard practice to set it to Always Ignore (but find out about it and make your own call :)
Thanks for the tip, but I'm struggling getting something to work.
In my spreadsheet I've put in cell C1 the value "D1" and then in cell D1 I put the value "123456" for example.
Now in the query I modified it to:
Source=DB2.Database("server", "database", Query=["SELECT * " & "WHERE COL1 = 'ABC' and COL2 = '" & pINDIRECT(c1) & "'" "])
I get this:
Expression.Error: The name 'pINDIRECT' wasn't recognized. Make sure it's spelled correctly.
John, pINDIRECT or how you'll call it is the query which you shall generate yourself.
In brief, within Excel give to your D1 cell name, for example, myParam. Stay on it, click in Ribbon Data->From Table/Range. In query editor convert column to Text and take the value from the cell, M script code will look like
let
Source = Excel.CurrentWorkbook(){[Name="myParam"]}[Content],
AsText = Table.TransformColumnTypes(Source,{{"Column1", type text}}){0}[Column1]
in
AsTextAfter that you may add result to your query as ... ["SELECT * " & myParam & "...]"
Wyn Hopkins published recently detailed post how to pick-up parameters from Excel cells into G&T, only i don't remember where. If Wyn is around hope he will give the link and/or comments. On the other hand you may find in other places.
Please check before what you connection works without any parameters, just add your "123456" directly into the Query= string.
- Thanks Sergei
Here's the article
https://www.linkedin.com/pulse/how-used-named-cells-parameter-inputs-power-query-wyn-hopkinsThanks a bunch and I'm getting a lot closer. However.... as I'm following your steps in step 5 you indicate to "Home - Close & Load - Connection only" - I don't see an option to close as "connection only".
I'm on the current channel so my Office365 has been updated with latest Get & Transform stuff.
Now I did finally get there by rmb on the saved query and select "Load to" which gave me this window where I could select "connection only":
I then went and edited my SQL query to now be
....... AND KUNNR = '" & QueryKUNNR & "') ORDER BY KTOKD .......
When it tries running I now get this:
Formula.Firewall: Query 'KNA1 by KUNNR' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
As I said - oh so close now.
- Hi John,
Keep going it's worth it!
To address your first point, Close and Load to... Connection Only. Those are actually 2 separate steps. In your screen shot your Load To.. is greyed out as you must have already loaded that query once and now you were editing it. In that case you did the right thing and used the Right Click option to access Load To... Connection Only
On the second issue you should (I think) resolve this by going to New Query - > Query Options - > Privacy > Always Ignore Privacy Level settings. There's a hyperlink there as well if you want to find out more about what turning off Privacy warnings does, however it is fairly standard practice to set it to Always Ignore (but find out about it and make your own call :)