locked
Refresh single query RRS feed

  • Question

  • I have a series of Get & Transform queries in Excel 2016 which pull in data from other sources.  There is then a query which merges that data ("Merged Data") and a final query which sub-selects and reformats the Merged Data acccording to various user choices input via a menu.  it is only this final query and its output which are visible to one class of users

    When these users are using that final query, the data needs to be refreshed from the "Merged Data" query to get the right selection of data.  However "Refresh" both from the ribbon and via VIA tries to refresh all the queries.  I do not want this - these users do not have the right to refresh the data pulled in from external sources into the Merged Data table. 

    How do I achieve a simple refresh of the single final Get & Transform query without doing what appears to be a RefreshAll in old parlance?

    Tuesday, July 10, 2018 10:49 AM

Answers

  • Since the results of query 1 aren't stored anywhere, there's no way to do what you're asking. Refreshing query 2 will always involve executing any queries it depends on.

    One way I can think of to address this would be to actually store the results of query 1 in a worksheet (i.e. load it to a sheet). Then use this copy of the data as the source for query 2. This would allow you to refresh them independently.

    Would that work?

    Ehren

    Friday, July 13, 2018 4:54 PM

All replies

  • Hi there. You said:

    the data needs to be refreshed from the "Merged Data" query to get the right selection of data

    But it also sounds like the source data that feeds into "Merged Data" is not accessible to the users. So I guess I'm confused how this is supposed to work. Can you clarify?

    Thanks,

    Ehren

    Thursday, July 12, 2018 8:08 PM
  • The data goes  Sources -> Merged Data ->Final query.

    There are users who have the right to pull in new data from the updated sources into "Merged Data".
    However there are users who can only query the "Merged Data" file as it stands through the "Final Query" but are not entitled to update the Merged Data from the sources.

    As an example, suppose I was pulling into the Merged Data file lots of information from datasets recording the temperature at varying dates.  The Merged data has information on dates, times, locations, operator and temperature.    The Final query allows users, by way of combo boxes, to select various cuts of the information by operator or location.  But they are not allowed to pull more data into the Merged Data file from the Source file.

    In other words, I want them to be able to run the Merged Data -> final query  BUT not to run the Sources -> Merged Data query.

    Does this help?

    Friday, July 13, 2018 12:14 AM
  • So the only thing that changes when a user refreshes should be the filters applied against the source data?

    Ehren

    Friday, July 13, 2018 12:17 AM
  • No.  The filters are applied against the Merged Data not against the Source Data.  Furthermore, it is not just filters which are applied in the Final query.  The Final query filters the data based on the user selection, but once filtered there are further steps applied by way of the M formula to transform the filtered data before it is displayed to the user.

    In essence my question is just this:

    I have an Excel spreadsheet which does this:

    1.  Sources (pulled from various non-excel  and excel files) --> Merged Data (transformation by the Merged Data query cleans and merges the data files into a single set of data)
    2.  Merged Data --> Final (transformation via the Final query which filters the merged data and then transforms it into a presentable table including further arithmetic steps)

    How do I run query 2. without running query 1.

    Friday, July 13, 2018 9:24 AM
  • Since the results of query 1 aren't stored anywhere, there's no way to do what you're asking. Refreshing query 2 will always involve executing any queries it depends on.

    One way I can think of to address this would be to actually store the results of query 1 in a worksheet (i.e. load it to a sheet). Then use this copy of the data as the source for query 2. This would allow you to refresh them independently.

    Would that work?

    Ehren

    Friday, July 13, 2018 4:54 PM
  • Just managed to get around doing this.   Required a bit of playing around with because when the results are stored into a worksheet and then used as a datasource, a lot of the types get lost.  Critically a Date type stored into the table then reloaded get automatically retyped as a DateTime variable.  Then Get & Transform throws a fit because you can't compare a Date with DateTime.  Ditto with nulls being converted to blank cells in Excel and then coming back in as General type with a value of 0.

    Having sorted these out, it does look as if it working.  

    Thanks very much

    Malcolm

    Wednesday, July 25, 2018 8:19 PM