none
Filtering a query from another query before the rows are loaded RRS feed

  • Question

  • I have 2 data sources I connect to in an Excel file using Query. One query has 50 rows of data from the original source, the other data source has 800,000 rows of data from the query. I want to use a field in the data source with only 50 rows to filter the query of the other data source so 800,000 rows are not loaded and therefore the refresh is quicker. These 2 queries are connected via a relationship where the smaller table as unique fields for the relationship and the larger table has many rows for the relationship.

    For example, the small data source has a field called project name which is unique for each row, the large data source has multiple rows for multiple project names. I want to use the project name from the small data source to filter the large data source so only the rows for a specific project name are loaded.

    I've researched my issues and found plenty of suggestions to use the merge option which gives me the final result I want, in that I get a much reduced number of rows in my table for a specific project name, but means all the rows in my original data source are loaded and therefore the refresh takes a while. 

    Has anyone got any suggests?


    Monday, March 2, 2020 5:39 PM

Answers

  • Perseverance has paid off, and after continuing to search on how to solve my problem and not finding anything, I thought i'd try on clicking on every option available to me to see what they all did and soon found how to do what I needed.

    When you connect to a data source and create a query you get an option to Load or Load To. If you choose load to you get a option of loading the data to a sheet, pivot table, pivot report or Connection Only. If you choose connection only, the connection is created without the data being loaded.

    You can then do the same for your 2nd data source.

    Then go into one of the queries and merge the queries, that allows you to add a column from one query into the other that you then use as a filter. Then set the filter up.

    At this point the data is still not loaded into you excel sheet, so right click onto one of the queries choose merge, and then that creates a new sheet with all you data in it and pre filtered.

    Easy when you know how. My refresh has gone form taking 1 minute 30 seconds, to 10 seconds

    Thursday, March 5, 2020 2:03 PM