January 6, 2021
I have query that pulls data from an Excel sheet and transforms to grab a few columns and a max of 2 rows (email and street address). Then I converted this process to a custom function. I then created a new query to run this function on almost every sheet in the Excel file (around 300). The query runs and is slower than I would think it should be. However that is not the issue. The odd thing is I then built a new query that references (uses the first query as a source) and does some minor changes to the output. Then I did another referencing the new query.
So each query builds off the first.
Original query on 300 sheets <- new query for minor cleanup <- new query with extra column <- 3 new query that each only show a subset and trans form
I could see my first query being slow but the other queries are built off next so I am confused. In watching them load in the Queries and Connections box (Excel) it is almost like each query is reloading/transforming what the previous query did.
In searching and reading I could not find much that seemed to help. I played some with the queries but nothing helped. I did try using Table.Buffer
BufferedSource = Table.Buffer(Source)
That did not seem to work. I even read that putting it in the wrong query can hurt not help. So even played with moving it from the first to the 2nd query. Still no luck.
Anyone have any thoughts or ideas?
November 8, 2013
The query results are not stored in memory, dependent queries will run again the initial query to load data.
Try loading the first query to sheet, then for the second query, load the table produced by the first query, instead of referencing the query, will be much faster.
Or, try to use Table Buffer to load into memory the entire list of data tables from sheets, just before adding the new column to transform them.