Add Get Parameter button to Power Query and avoid Privacy / Formula.Firewall problems
It's fantastic that you can pick up named ranges in Excel and pass them to Power Query as parameters. However the process is cumbersome and often results in Formula.Firewall warnings or other issues.
This leads to having to switch Privacy Levels to "Ignore" or having to write a query within a query rather than nice and clean "parameter" queries.
It would really simplify things if we could quickly create parameter queries (including a drop down to select parameter type) and that these queries would be immune from the Privacy warnings.
Thank you for taking your time to suggest and vote for adding Get Parameter button to Power Query. We do think that this suggestion has merit but we don’t think that we’ll be able to devote time to it in the near future. We’ll continue tracking votes for it.
Excel Program Manager
Wyn Hopkins commented
That's a shame Guy, thanks for letting us know though.
Any chance you can add this link (to Ken's post) to your response as a workaround:
Isn't it fixed yet! Couldn't understand why using a worksheet reference query parameter in my query was failing until I read it was privacy settings.
This sounds great!
I must admit facing the Formula.Firewall problems get frustrating when you can't set it to Ignore but most of the problem queries are fetching data (mainly parameters) inside the same workbook where the query is located!
Query in query works most of the time but it gets hard to read sometimes and there's one query type I haven't been able to make work yet,without ignoring security, when using parameters...
Please dot that. I need it. Thnx
I used VBA To avoid the formula firewall warning when trying to combine the cell range parameter with the rest of the query as discussed in the original post. Also subsequently found it mentioned here:-
Amending Privacy levels not really an option, and query within a query method not appropriate. However amending VBA from this site to build the query using the cell range worked OK.
Jerry Norbury commented
I don't understand why people are struggling to get to the data in excel cells - either use a parameter table or simply name the cell and access it as a range. Both fully supported in PQ.
VBA? Whatever for?
I've resorted to writing some VBA code to set the M language query in order do what should be a simple thing - grab a value from a text box and use it as a parameter for a web query. Kind of defeats the purpose of using the Get and Transform in the first place....
Phil Smith commented
Each of the MSQuery extracts I use are updated by users selecting or entering data in a set of cells. They're easy to use for other staff members, who are not excel experts. This simplicity must be maintained.
Chris Bennett commented
Coming from the old MS Query parameters where you simply selected a cell and could edit the SQL query to add or delete parameters on fields - this new version is really clunky and could be simpler, so I support this simplification request.
Ivan Bondarenko commented
Agree with idea. Frequently face the same issue.
Hi Ashvini - Specifying a range as a method of passing a parameter to a query was there in the good old Database query
So logically it should have been in Power Query right from the beginning
What happened to 'find'?