Reference Table loaded multiple times

[Anonymous]

Hello everybody, 

I have a big problem with the performance for queries I wrote in Power Query. To get to know you first: Evaluation of the queries is made in Excel and not in Power BI. I have to evalute our offered articles in the three categories "AssemblyParts", "ManufacuringParts", "PurchaisingParts".

I have a query which is called "Query1". This query is a reference to "Query0". "Query0" is a proccessed form of the raw data. Blank Rows are deleted, unesscary spaces also..This "Query0" contains all three different types of articles. 

"Query 1" is merged with an other Query, i.e. "Query 2"This Query contais the prices for the articles. ."Query 1" is now referring to three other queries: "Query 3","Query4" and "Query5".

Problem now: When I am refreshing all the queries, "Query1" is refreshed three times. This unfortunately takes a long time. I tried to impede this "behavior" with the command "Table.Buffer" at the end of "Query1".

Is there an other possibility to impede that the Query is refreshed three times? 

[BA_Pete]

Hi @Anonymous ,

The surefire way to stop a query loading multiple times is to load it only once.

I'm not being a smartarse - if the multiple loads are causing you significant performance issues, then remove all the referenced queries, strip back to your core query, then handle the rest within the Excel Data Model (exactly as you would in Power BI).

Without seeing your queries and data it's difficult to say whether everything you want to do in subsequent queries is feasible/possible within the model, but certainly things like merges between queries can be removed via Data Model relationships, and so on.

Pete

[ppm1]

Please see this video for an explanation of what is going on and some ideas to try. Also, FYI that Table.Buffer only works within a given query.

https://www.youtube.com/watch?v=3uKNNZqBIkg&t=1s

Pat