Feedback by UserVoice

How can we improve Excel for Windows (Desktop Application)?

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.

291 votes
Sign in
(thinking…)
Sign in with: facebook google
Signed in as (Sign out)

We’ll send you updates on this idea

Wyn Hopkins shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

9 comments

Sign in
(thinking…)
Sign in with: facebook google
Signed in as (Sign out)
Submitting...
  • Anonymous commented  ·   ·  Flag as inappropriate

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

    https://social.technet.microsoft.com/Forums/lync/en-US/8b3a4310-ed6f-4cef-b7d4-8120e5adf8e6/formula-firewall

    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.

    https://gallery.technet.microsoft.com/office/VBA-to-automate-Power-956a52d1

  • Jerry Norbury commented  ·   ·  Flag as inappropriate

    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?

  • Anonymous commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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.

  • Sam commented  ·   ·  Flag as inappropriate

    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

Feedback and Knowledge Base