Forum Discussion
PowerQuery Table Buffer at end of query
- Jun 23, 2023
Please check Why Does Power BI Query My Data Source More Than Once? - Chris Webb's BI Blog (crossjoin.co.uk), second part of the post is about your case. Also Referenced Queries And Caching In Power BI And Power Query - Chris Webb's BI Blog (crossjoin.co.uk)
Sorry, but I can't explain better than Chris.
Also he has series of post regarding Power Query performance, perhaps something will be useful. First what Google returned Power Query/M Optimisation: Getting The Maximum Value From A Column, Part 2 - Chris Webb's BI Blog (crossjoin.co.uk)
Then I guess the next part is: does Table.Buffer work if you're referring to a certain step multiple times? Or is that just exactly the same as referencing another query?
As in If you have following setup (and no query folding as it's all excel based sources):
Step 1) GetDataFromSource1 (takes a couple of seconds)
Step 2) GetDataFromSource2 (takes a couple of seconds)
Step 3) Combination of Step 1 and Step 2
Step 4) Combination of Step 1 and Step 2
Step 5) Combination of Step 3 and Step 4
In this setup Step 1 and Step 2 are performed both twice to get Step 5. Can this be circumvented by adding a Table.Buffer at step 1 and step 2?
Based on what you wrote I now believe that Table.Buffer is not at all what I thought it was. I thought it was a temporary save of the Table, (which it kind of is) but it's only within a single step to improve the speed of some very specific functions, not when other queries (or query steps) reference to the steps. Correct?
Please check Why Does Power BI Query My Data Source More Than Once? - Chris Webb's BI Blog (crossjoin.co.uk), second part of the post is about your case. Also Referenced Queries And Caching In Power BI And Power Query - Chris Webb's BI Blog (crossjoin.co.uk)
Sorry, but I can't explain better than Chris.
Also he has series of post regarding Power Query performance, perhaps something will be useful. First what Google returned Power Query/M Optimisation: Getting The Maximum Value From A Column, Part 2 - Chris Webb's BI Blog (crossjoin.co.uk)
- MatthiasVCJun 23, 2023Copper ContributorThanks, that was a very insightful video.
I'm still unsure if doing it in Steps might be better and allow Table.Buffer to share the output to different steps, If I read the article it seems it might.