Forum Discussion
Table.Buffer for sharepoint datasources
Hi,
I have this power query workbook which I am trying to optimise as it takes a while to refresh. The workbook taps into excel workbooks hosted in two different sharepoint folders. The final query is a merge of the first two and this involves a series of steps that adds extra columns based on certain conditions of the data in the two parent datasources
I was wondering if table.buffer can be used to optimise the time it takes to refresh my query. So far, I have only found table.buffer using excel workbook as a datasource
Thanks
EDIT: I just realised that I accidentally posted this in the wrong forum as this should have been in the Power Query thread
Hi Anonymous ,
Table.buffer can optimise the peformance only when a query that needs to be evaluated over and over. You can refer to the list.buffer document:https://blog.crossjoin.co.uk/2015/05/05/improving-power-query-calculation-performance-with-list-buffer/ it works at the same ways.
You did a merge steps between two excel file? You can try to add a primary key on the linked column, it can prevent files to be load into power query multiple times when it merges two queries, please refer to https://blog.crossjoin.co.uk/2018/03/16/improving-the-performance-of-aggregation-after-a-merge-in-power-bi-and-excel-power-query-gettransform/
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
4 Replies
Anonymous , I think M expert can help
ImkeF , can you help on this
Hi Anonymous ,
Table.buffer can optimise the peformance only when a query that needs to be evaluated over and over. You can refer to the list.buffer document:https://blog.crossjoin.co.uk/2015/05/05/improving-power-query-calculation-performance-with-list-buffer/ it works at the same ways.
You did a merge steps between two excel file? You can try to add a primary key on the linked column, it can prevent files to be load into power query multiple times when it merges two queries, please refer to https://blog.crossjoin.co.uk/2018/03/16/improving-the-performance-of-aggregation-after-a-merge-in-power-bi-and-excel-power-query-gettransform/
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
The two excel files are located in two different folders in the same sharepoint site. I have performed the merge on two columns form both the excel files - Project Name and Project ID from both the datasets
Another question on this would be where to place the Table.Buffer step ? Would it be right after the merge step? The merge I have performed is "merge as new query"
Hi Anonymous ,
You can put it in your first reference of the table before the merge steps like create a step
source1 = table.buffer(source1). Then do the merge step.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
