I have been making a power query in excel. Initially I was using data locally from the workbook. There are two tables from 2 different workbooks which in practice are kept on sharepoint server and updated every so often.
Ever since I have pointed directly at the file in sharepoint, the refresh has slowed dramatically going from 10-15s to now 3 mins. I ran the trace found in the diagnostic tab and I can see:
DataMashup.Trace Information: 24579 : {"Start":"2020-07-14T08:51:05.4427213Z","Action":"Engine/IO/Web/Request/GetResponse","ResourceKind":"Web","ResourcePath":"https://name_of_org/sites/mysite/_api/web/getfilebyserverrelativeurl('/sites/mysite/Shared%20Documents/a/b/c/d/e/myfile.xlsx')/$value","HostProcessId":"8072","RequestMethod":"GET","RequestUri":"https://name_of_org/sites/mysite/_api/web/getfilebyserverrelativeurl('/sites/mysite/Shared Documents/a/b/c/d/e/myfile.xlsx'')/$value","RequestHasContent":"False","RequestHasHeaders":"False","RequestHasTimeout":"True","UseCache":"True","UseBuffer":"True","ResponseStatusCode":"200","ResponseContentLength":"334883","ProductVersion":"2.78.5740.481 (20.02)","ActivityId":"8e2fd66c-43f4-4e6b-921f-e29e0585baf8","Process":"Microsoft.Mashup.Container.Loader","Pid":14640,"Tid":1,"Duration":"00:00:00.0021930"}
This is tried ~12k times. For a table that has a total of about 1000 cells. Why is this action happening how can I make it retrieve it only once.
In my query the reference to the table is made by:
= Table.AddColumn(#"Changed Type1", "test", (Q1) => Table.SelectRows(test_query, each (Q1[c] = [c1] or Q1[c] = [c2]) and [condition]="ok") )
where "test" is the loaded table from excel stored on a sharepoint site and transformed
Fast Load enabled
Data preview disabled
Ignore privacy settings enabled
I managed to get to the bottom of the issue, I think. The custom add column formula was making a call to the online sharepoint for every line item. I got over this issue by going into advanced editor and adding a buffered table and referencing the buffered table in this query instead.
This cut the time from 600s to about 9s.
test_query = Table.Buffer(test_query_toBuffer)