Last week, Maxim Zelensky (whose blog is well worth checking out) tweeted about a very interesting answer he had received to a question he posted on the Power Query MSDN forum, on the subject of caching and referenced queries in Power Query. You can read the thread here:
…but since this is such important information – it’s certainly something I’ve wondered about myself – I though I would share Maxim’s question and the response from Ehren of the Power Query dev team here in full so it gets the wider visibility it deserves. I’m very grateful to Maxim for letting me share this and to Ehren for writing such a detailed response.
First, Maxim’s original question:
There are two different scenarios I am working with:
1) Query1 connects to the data source (flat file) and make basic cleaning and transformations.
Then Query2 and Query3 reference to Query1, performing other transformations needed.
Query3 also take some data from Query2.
Query2 and Query3 then exported to sheet, Query1 – connection only.
As far as I can understand, PQ can define refresh chain like this: evaluate Query1, then evaluate Query2, then evaluate Query3 (as it need the results from Query2). The question is: When PQ performs calculation of Query3, will it recalculate Query1? Or, as it was evaluated in the chain before, Query3 will use cached results of previous Query1 calculation (performed when Query2 was evaluated)?
2) I have a set of flat files, and I take data from them with one Query1. Query1 also performs some transformations. Then I have two independent Query2 and Query3, both connected to Query1 performing different transformations of source data. Results of Query2 and Query3 evaluations then used in Query4, which exports its results to the sheets, Query1, Query2 and Query3 – connection only
The second question is: performing "Refresh" on Query4, how much times will be Query1 evaluated – one, two or three ? Or there also will be chain: calculate Q1, caching, then Q2 or Q3, calculate next using cached results of Q1 evaluation, and then – Q4?
3) Is there is a difference with connection to database?
4) Is there any rules of evaluation chain (like each expression/query will be calculated once in the evaluation chain)?
And here’s Ehren’s reply:
There’s a lot involved in answering your question, so let me back up and explain a few things first.
Caching
Power Query (in both Excel and Power BI Desktop) utilizes a "persistent cache", stored on disk, when refreshing queries. But what exactly does that mean?
First, let’s look at what gets cached. The persistent cache does not store the results of your M Queries (Q1, Q2, etc. in your example). Instead, it stores the results of the behind-the-scenes requests sent to data sources.So if Q1 queries a SQL Server database called "MyServer/MyDatabase" and returns a single unfiltered table called "MyTable", the query sent to the server might be "select [Col1] from [MyTable]". In this case, the persistent cache will now know the result of sending "select [Col1] from [MyTable]" to "MyServer/MyDatabase". If another M query (whether through referencing Q1, or by querying the same table directly) needs the same result, the persistent cache can provide it, and the result won’t have to be fetched a second time from the SQL Server.
"Great," you might say. "So if I’m pulling from a flat file in Q1, and in a few places in Q2 I need to do Table.RowCount(Q1), the file should only be read from disk once, right?" And the answer would be…no. This is because not all data sources are cached. Specifically, the results of calls to File.Contents are not stored in the persistent cache. Why not? Well, the cache is stored on disk, and caching local files (which are already on disk) elsewhere on disk doesn’t really make sense. (Using Table.Buffer in this context may help…see more on Table.Buffer below.)
"Okay", you might say. "But if Q1 is pulling from a SQL table, and in a few places in Q2 I reference Q1, that should hit the persistent cache, right?" Maybe. It depends on how Q2 is using Q1, since doing additional operations on Q1 (such as filtering or merging) might cause the M engine to compute a different SQL query, resulting in the server being hit again.
Next, let’s look at the scope of caching. The scope of caching differs depending on what you’re doing, as well as what tool you’re using.Previewing
If you’ve opened the Power Query editor in Excel or Power BI Desktop, you might have seen warnings like "This preview may be up to 3 days old". This is because there is a persistent cache used for interactive previewing of query results. As you can imagine from the fact that we have warnings about preview results being days old, this cache is long-lived and is intended to make the experience of working in the editor faster.Loading to Excel
If you load/refresh three queries in Excel, each of them gets their own persistent cache. So the fact that a SQL result is cached during the load of Q2 won’t benefit the loading of Q3, even if it needs the same result.Loading to Power BI Desktop
If you load/refresh three queries in PBI Desktop, they all share a single persistent cache. When you refresh multiple times, each refresh operation gets its own cache (shared by all the queries being refreshed at that particular time). This means that if SQL result is cached during the load of Q2, it will still be cached during the loading of Q3 (assuming they’re both being loaded at the same time).What about Table.Buffer?
Table.Buffer can be useful if you want to store an intermediate result in memory and avoid pulling content from disk, a remote file share, a SQL Server, a website, or any other data source multiple times during an evaluation. Think of Table.Buffer as, "load this table into memory, and stop folding subsequent operations back to the data source".However, because buffering happens in memory and is not persisted on disk, buffering during the load of one query does not affect the load of another query. If Q1 is buffered when Q2 is loaded, Q1 will be re-buffered when Q3 is loaded.
And now to answer your question…
Now let’s take a look at your example (Q4 references Q2 and Q3, and Q2 and Q3 both reference Q1).
Since you’re pulling from a flat file, and File.Contents results aren’t cached, the flat file will be read each time Q1 is referenced (twice in Q4, once in Q3, Q2, and Q1). If you buffered the result of Q1, then Q4 would only read the file once. But when Q1, Q2, and Q3 are loaded (even in PBI Desktop), they will still each also read the file.What about immutability?
You asked about the fact that M values are supposed to be immutable. This is true for the "pure" parts of the language, but breaks down when you introduce external data sources and folding. (In fact, you could think of Table.Buffer as transferring a table from the fuzzy unpredictable world of folding to the immutable world of pure M values.) You can see this in action by doing the following test, using a query called "MyTextFileQuery" that pulls from a local file on disk.Reads the file five times
= Table.RowCount(MyTextFileQuery) + Table.RowCount(MyTextFileQuery) + Table.RowCount(MyTextFileQuery) + Table.RowCount(MyTextFileQuery) + Table.RowCount(MyTextFileQuery)Reads the file once
= let rowCount = Table.RowCount(MyTextFileQuery) in rowCount + rowCount + rowCount + rowCount + rowCount
I’ve read this response several times and it’s still sinking in, but clearly there are some important implications here for anyone doing more advanced data loading work in Power Query and Power BI. I’m sure it will be the inspiration for many future blog posts on tuning Power Query query performance.
I asked a similar question where Ehren responded as it being an issue…hopefully there may be some improvements soon?
https://social.technet.microsoft.com/Forums/en-US/73735c82-fd5b-464b-9c13-8a3433851149/buffering-referencing-queries
The biggest concern here is that due to Excel using a persistent cache for EACH query in the chain, this has huge implications on performance, not to mention that the original data source can be called multiple times. What’s unfortunate is that we are also forced to build complex query chains to avoid formula firewall issues (although to be fair, you want longer chains to some degree for auditability.) Power BI’s methods of one single persistent cache actually loads data from the original data source less times, and is MUCH faster than Excel’s method today. (Hopefully this gets addressed soon, as it’s a pretty major issue in Power Query adoption for larger projects.
@Ken – Formula firewall is no longer a concern once you set “Always Ignore Privacy” – at Global Level
@sam, yes but I’ve never been a fan of just turning off security. I try to work within it’s bounds where I can. Iirc, that also works for Excel, but doesn’t port well to Power BI (although I could be mistaken there.)
Some of these problems might go away if it were possible to emit more than one table from the same M query, e.g. Q4.
[XSLT 1.0 had the same limitation: one or more input docs but only one (implicit) output doc. Support for multiple (explicit) output docs was added in XSLT 2.0.]
Any chance for something similar in M vNext? I would think that if PQ were ever to be integrated with SQLServer that such a feature would be useful there as well.
I have 5 queries as follows:
Q1 /Q2 : to get csv files (fact tables) from specified folders A and B(connection only)
Q3 : to get csv files (also a fact table) from specified folder C (connection only)
Q4 : to get data (lookup table) from specified single excel file (connection only)
Q5 : to append Q2 onto Q1(connection only)
Q6 : to merge Q5 and Q4 plus Q3 then load data to Excel spreadsheet
I would add one csv file each on above mentioned folders on daily basis and it’s up to 140 files in folders A & B respectively, and 280 files in folder C as of today.
Is there any way to improve the query performance as it took 2 minutes or so for data refreshing currently?
I would try using Table.Buffer inside Q6 to buffer the results of Q5, Q4 and Q3 (create new steps in Q6 to do this) before you do the merge operation.
Hi Chris,
Why would this work?
Q3, Q4, Q5 are already imported in Q6, and there is no reuse of this data when you want to merge them.
So if before in Q6 it was:
– Get Q3, Get Q4, Get Q5
– Merge Q3, Q4 and Q5.
Now it will be:
– Get Q3, Get Q4, Get Q5
– Buffer Q3, Buffer Q4, Buffer Q5
– Merge Q3, Q4 and Q5.
Why would you see better performance here if anyway the data was imported once?
Two possible reasons: (1) As far as I can see, sometimes Power Query reads data from a data source many times for a single query execution. (2) It could be the performance problem is actually down to loading preview data in the background and not the for the query that is actually loaded – I saw this with a customer last week and will be blogging about it soon.
Chris, sorry for my late reply as I was waiting for new data input for testing. According to my experiment It took 1 minute and 40 seconds after adding the table buffer as you coached, while the original file took 2 minutes 30 seconds. I guessed even though creating table buffer also consumed some time, but it would save more time to do the merge operation with the buffers implicitly. Am I right?
It’s hard to say. I suspect that creating the buffers is pretty quick and that there’s something else that needs to be optimised here.
Especially important as M gets loaded into SSDT in CTP1.1
Not strictly on-topic, but on the topic of query references, is it possible to reference a query in Power Query or BI with a variable name instead of hard coding it in the query?
Instead of:
Source = #”Some Other Table”
how about
nmTable = “Some Other Table”,
Source = Table(nmTable)
(obviously the “Table()” command is not in M, but you get my idea).
This would allow another layer of indirection.
Does this do what you want?
https://blog.crossjoin.co.uk/2015/02/06/expression-evaluate-in-power-querym/
https://blog.crossjoin.co.uk/2014/02/04/loading-power-query-m-code-from-text-files/
? Make sure you read the comments for details on drawbacks of using this.
Got it. Thanks for the help.
What I found was that Expression.Evaluate(“query name text”) worked fine if the query name has no spaces. The following works for all query names:
(nmSrc as text) =>
let
nmSrc2=Expression.Identifier(nmSrc),
Source = Expression.Evaluate(nmSrc2,#shared)
in
Source
APJ
Is this not a contradiction?
In the ‘Caching’ section I read;
=======================
“If another M query (whether through referencing QUERY_1, or by querying the same table directly) needs the same result, the persistent cache can provide it, …”
In the ‘Load to Excel’ section I read;
===========================
“So the fact that a SQL result is cached during the load of Q2 won’t benefit the loading of Q3, even if it needs the same result.”
They both reference ‘needs the same result’ but, to my interpretation, in contradictory ways. Can anyone clarify?
An old post, but I think this is still relevant. In my case, I use PowerQuery from Excel to load 6 tabs from a single, static Excel file. The static Excel file is a data dump from proprietary software, and remains untouched once exported. So, I need 6 separate queries, one for each tab of the same Excel file.
Performance isn’t great, so in hopes to improve this, I tried implementing a single, Connection Only query with the Excel.Workbook( File.Contents( … ) ) command. Let’s call this Q1. Then to load the 6 tabs, I would reference Q1 from the 6 separate PowerQuery pulls, say Q2-Q7. In Q1, I tested with and without a Table.Buffer step.
Of course, what happens is that the full workbook gets loaded once for each of the sub-queries, 6 times in total, whether Q1 is a separate Connection only request wrapped around Table.Buffer or not.
It would be nice if Excel.Workbook or File.Contents could be buffered, and only refreshed if the file had been modified since it was last queried. I feel like the static file only needs to be downloaded once, but of course based on the above and my own observations, this isn’t the case.
Are there any other ways to improve performance in such a design, when using static files?
Hi Chris,
what about using Table.StopFolding to prevent subsequent calls to the source of the initial data pull?
That wouldn’t work – it would stop folding, not subsequent calls to the data source