Power Query Performance Issues

[asjones987]

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 2[SUP]nd[/SUP] query. Still no luck.

Anyone have any thoughts or ideas?

Thanks

Alan

 

[pinarello]

I have made the following experience:

I have implemented an address search (fuzzy search) with PQ. For this the address data are prepared before with a separate query. I would only need the result of this query as a connection, but in order to be able to better check the search results, the result of this query is loaded as a table.

If I now make changes in the address table and then directly start an address search without first running the data preparation, then the changes are still taken into account. However, the loaded table of the formatted address data does not show these changes until I refresh the table.

Only after I had divided the data preparation and the address search into 2 separate workbooks, this effect no longer occurred and the actual address search then ran in half the time.

 

[asjones987]

thanks for the response. Interesting that each query would rerun the previous one...I really thought it was setup to it would not rerun things.

This really seemed true wit the the table.buffer command. Anyone have additional thoughts or ideas .... or know more about the table.buffer? I can't find much of info on it.


thanks for any and all ideas.

Alan