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 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.
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 :)
I looked at the other ways to do this in the other posts and while it was tempting to work on putting both of these in one query I went with this simple approach of setting the privacy settings to "always ignore". The main reason for this is in reality I don't have just one query that I need to read this parm and execute, but I have about 20 different queries against other tables that need to use this same query value.
Thanks everyone for your great input on this.
My $0.02 regarding Formula.Firewall
1) I agree that's quit common to switch private settings off to avoid the issue. Here https://blog.crossjoin.co.uk/2017/07/04/data-privacy-settings-in-power-bipower-query-part-4-disabling-data-privacy-checks/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+wordpress%2FCpjz+%28Chris+Webb%27s+BI+Blog%29 is step by step how to do that and why it's not always good practice.
2) I personally prefer to keep privacy settings by default and combine the query by the way to avoid Formula.Firewall. Security is the point, but not the main one here - when you distribute your workbook to other people you have to ask everyone to change default settings and explain how to do that.
In his another post Chris gives explanation how to avoid the issue practically for exactly the same case https://blog.crossjoin.co.uk/2017/06/26/data-privacy-settings-in-power-bipower-query-part-3-the-formula-firewall-error/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+wordpress%2FCpjz+%28Chris+Webb%27s+BI+Blog%29
I didn't try Value.NativeQuery, but combining Excel parameter and SQL queries in one works fine, used that approach before seen above post.
- Thanks for sharing the links
