Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I am connecting to a Folder that has a bunch of JSON files.
I use a custom function to combine the files.
Then I do all kinds of stuff, like dynamycally extracting the column names from nested json lists, etc.
I feel Table.Buffer / List.Buffer can speed things up.
I've read this:
https://www.thebiccountant.com/speedperformance-aspects/
and this:
And I still wonder - when shoud I use Table.Buffer?
Why not put it in the beginning of my query (just after I run the custom function) - and that's it - all will sit in the memory?
Solved! Go to Solution.
Hi @michaelsh
If you buffer a table in a step, then transform that table in some way, then this new transformed table isn't the one that is buffered.
So in subsequent steps you can't continue to refer to the buffered table from your 1st step.
To be honest the workings of the Buffer functions aren't cleary undestood by anyone other than the Microsoft dev team. I've never read a definitive explanation of how or when to use them, hence Pat's suggestion to 'just try them out'.
Cheers
Phil
Proud to be a Super User!
The best way is to just try it out and see if it makes things significantly faster. Hard to predict if it will have an impact as sometimes is does that type of optimization on its own. The key thing is to use it within the same query where it is referenced multiple times (which your proposal does).
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @michaelsh
You could do this, provided that the table you are referencing does not get changed in any way in subsequent steps. This is not usually the case though, so every time a table is transformed resulting in a new table, you'd need to use Table.Buffer again.
Regards
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
Proud to be a Super User!
I see @PhilipTreacy .
But if the subsequent steps modify the table, is it still better to Buffer just the first one, so that the subsequent steps do not go back to original data source, but just to the first buffered table?
I am still missing something...
Maybe you can provide a sample?
Thank you
Hi @michaelsh
If you buffer a table in a step, then transform that table in some way, then this new transformed table isn't the one that is buffered.
So in subsequent steps you can't continue to refer to the buffered table from your 1st step.
To be honest the workings of the Buffer functions aren't cleary undestood by anyone other than the Microsoft dev team. I've never read a definitive explanation of how or when to use them, hence Pat's suggestion to 'just try them out'.
Cheers
Phil
Proud to be a Super User!
Hi, @michaelsh
You may use 'Table.Buffer'/'List.Buffer' when your table or list will br referenced multiple times(like in 'Table.SelectRows', 'List.Generate', 'List.Accumulate').
In the following example, table 'a' is referenced mutiple times.
=Table.AddColumn(a,"Custom",each Table.RowCount(Table.SelectRows(a,(x)=>x[Subject]=[Subject] and x[Score]=[Score]))+1)
Then you may use 'Table.Buffer' to store the table in memory to improve the performance.
A=Table.Buffer(a),
Table.AddColumn(A,"Custom",each Table.RowCount(Table.SelectRows(A,(x)=>x[Subject]=[Subject] and x[Score]=[Score]))+1)
Hope it helps for you to understand 'Table.Buffer'/'List.Buffer'.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-alq-msft Could you please explain why buffering once is not enough? Why does one need to repeatedly buffer a table if it 'is referenced multiple times' as you say?
Yes, @v-alq-msft , this is helpful, thank you!
What I don't get - is why shouldn't I just write Table.Buffer as my second step and then all other steps will just reference this buffered table.
Will it work?
Why not do it? (consider I have enough memory)
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
87 | |
84 | |
73 | |
49 |
User | Count |
---|---|
143 | |
131 | |
110 | |
64 | |
55 |