Forum Discussion
Understanding why Table.Buffer makes a difference in dependency chain
This thread contains a lot of information around how Table.Buffer works: https://social.technet.microsoft.com/Forums/en-US/34e454b5-3a18-4eef-b920-40703c93f390/tablebuffer-for-cashing-intermediate-query-results-or-how-workaround-unnecessary-queries-issue?forum=powerquery
This thread is also very enlightening: https://social.technet.microsoft.com/Forums/en-US/8d5ee632-fdff-4ba2-b150-bb3591f955fb/queries-evaluation-chain?forum=powerquery
Say you have a query Q4 that references Q2 and Q3. And say Q2 and Q3 both reference Q1. And say Q1 is pulling from a flat file. According to Erhen at Microsoft, if you're pulling from a flat file - and because File.Contents results aren't cached - the flat file will be read 5 times! It gets read each time Q1 is directly or indirectly referenced: twice in Q4, once in Q3, Q2, and Q1. And I believe he’s saying that this is the case for either Excel or PBI.
So…when doing any kind of complicated chain, the code produced from the UI just flat out sucks! And that is an epic fail for a tool that is designed to be used by non-experts via the UI.
I think a very low number of people facing performance issues because of this behaviour will be able to form a hypothesis about what's going wrong, find a thread on the web that explains the issue in plain English, then go wrap the first reference in any one query to other queries with Table.Buffer. And if you don’t diagnose the issue, you just think that PQ is a slow dog, and switch to using SQL.
Anyone know if there a UserVoice request that addresses this specific behaviour? And whether this behaviour unaddressable now, given the architecture choices MS has hade?
Amen to that as I have hit that wall as well just recently.