Table.Buffer for sharepoint datasources

[Anonymous]

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

[v-deddai1-msft · Accepted solution]

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

[Anonymous]

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"

[v-deddai1-msft]

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

[amitchandak]

@Anonymous , I think M expert can help

@ImkeF , can you help on this