none
Power Query vs MS Query - Passing Parameters & Automatic Refresh RRS feed

  • Question

  • I've been using MS Query to pull data from a SQL database and make use of the ability to link a parameter to a cell and also update the query whenever the cell value changes.  It seems that Power Query is the future so I've been trying to port my MS Query reports to Power Query.  In Power Query I've figured out how to pass a parameter from a spreadsheet cell, but I have to manually update the Power Query each time I change the parameter.  Since I have 6 parameters, this means 6 update clicks every time I pull different data.

    Is there an equivalent capability in Power Query as in MS Query (automatic refresh?)

    Monday, December 29, 2014 10:52 PM

Answers

  • You can use VBA to apply a refresh to the specific Workbook Connections that were created by Power Query.

    For example:

    In this screenshot we have a single (Power) query that loads data to Worksheet. When we open the Connections dialog under the Data tab, we can see the relevant connection (See screenshot below). This connection can be refreshed manually or using VBA.

    The next step is to write a Macro to refresh the relevant connection:

    ActiveWorkbook.Connections("Power Query - query1").Refresh

    In your case, if you have 6 different connections that were generated by Power Query, you can write the relevant VBA code to refresh these connections.

    This screenshot shows the workbook Connections that were generated by Power Query:

    Power Query generates Workbook Connections that can be refreshed using Object Model & VBA

    If you combine the 6 queries into a single worksheet, or load it to the Data Model, you can also consider using a Query Function (here) which will result in a single query that can access the 6 parameters and loads the desired data. 

    Friday, January 2, 2015 9:39 AM

All replies

  • You can refresh all of the Power Query queries by clicking "Refresh All" on the Data ribbon. I don't entirely understand what you mean by "automatic refresh" -- there's no way to use the recalculation chain of the spreadsheet to drive query re-execution, if that's what you're looking for.
    Wednesday, December 31, 2014 3:10 PM
  • Curt:

    MS Query allows you to specify one or more filter parameters in a query by using a question mark instead of a fixed value. This works with only SQL queries that MS query can display graphically (which amounts to fairly simple queries), or stored procedures. When the query is executed, MQ Query displays a dialog box for the user to enter actual values. After the query is returned to Excel, the user can use the "Parameters" property of the query table to specify a cell from which to use a value for the parameter. If the user changes the value in the cell, the query is refreshed automatically.

    Matt:

    The short answer to your question is no - Power Query does not provide such limited application or binding  of parameters. Power Query parameters can be used with any query - not just SQL queries. Also, Power Query parameters can be used anywhere - not just, for example, in a WHERE condition of an SQL query.

    As Curt mentioned, you just click "Refresh All" to refresh the query. Alternatively, you can write a simple Worksheet_Change macro to do the work. 


    • Edited by Colin Banfield Wednesday, December 31, 2014 9:48 PM Added info
    Wednesday, December 31, 2014 7:43 PM
  • You can use VBA to apply a refresh to the specific Workbook Connections that were created by Power Query.

    For example:

    In this screenshot we have a single (Power) query that loads data to Worksheet. When we open the Connections dialog under the Data tab, we can see the relevant connection (See screenshot below). This connection can be refreshed manually or using VBA.

    The next step is to write a Macro to refresh the relevant connection:

    ActiveWorkbook.Connections("Power Query - query1").Refresh

    In your case, if you have 6 different connections that were generated by Power Query, you can write the relevant VBA code to refresh these connections.

    This screenshot shows the workbook Connections that were generated by Power Query:

    Power Query generates Workbook Connections that can be refreshed using Object Model & VBA

    If you combine the 6 queries into a single worksheet, or load it to the Data Model, you can also consider using a Query Function (here) which will result in a single query that can access the 6 parameters and loads the desired data. 

    Friday, January 2, 2015 9:39 AM
  • Colin & Gil,

    Thanks.  I'll try adding the macro to automate the refresh. I don't want to manually click "refresh all" because what is retrieved by each query is dependent on the previous query. Hard to explain but I'm querying a LIMS and the contents of each field I query is restricted by previous information.  e.g. I enter a starting and ending date for sample login.  That restricts the possible "Product" field entries to those just entered during that time period.  Once the Product is chosen, the contents of the next field is restricted.  Currently that means I have to click on refresh after each entry.  I'm using Data Validation to restrict/chose the possible selections for each query parameter.

    I took a quick look at the Query Function. Seems like it might be useful.

    Sunday, January 4, 2015 12:26 AM
  • Curt,

    With MS Query - in the parameters window there is an option to link a parameter to the value in a worksheet cell and a checkbox "Refresh Automatically when Cell Value Changes". I'm trying to duplicate that functionality in PowerQuery.  It seems a simple macro will do the trick as the others have suggested.

    Sunday, January 4, 2015 12:36 AM
  • Hey I saw you have figured out how to pass a parameter from a spreadsheet cell in power query. I am struggling with that. could you guide me towards the resources that would help me understand that. I am also trying to pull data from SQL database while giving parameters from excel spreadsheet but I am lost in power query. Thanks
    Friday, August 3, 2018 8:02 PM
  • Hi hemdeep1985

    For Q1 (pass a parameter from a spreadsheet cell in power query) are you saying that i.e. Use Power Query with Parameter to Select Records from SharePoint Online List isn't clear enough? If not, have a look to i.e. Using dynamic parameter values in Power Query Queries - there are many others, just use an internet search engine...

    Friday, August 3, 2018 8:48 PM