Using Table.Buffer

[michaelsh]

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:

https://blog.crossjoin.co.uk/2015/05/05/improving-power-query-calculation-performance-with-list-buff...

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?

[mahoneypat]

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

[PhilipTreacy]

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.

[michaelsh]

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

[PhilipTreacy · Accepted 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

[v-alq-msft]

 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.

[Element115]

@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?

[michaelsh]

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)