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.

246 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Wyn Hopkins shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    Accepting Votes  ·  AdminExcel Team [MSFT] (Admin, Office.com) responded  · 

    Interesting suggestion, Wyn. Thanks for logging it.

    Others coming in new to this, please help us prioritize this item by providing your votes.

    thanks

    Ashvini Sharma
    Lead Program Manager
    Excel

    8 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      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