Incredibly poor SharePoint performance

[Veles]

I'm getting some incredibly poor performance with queries that reference SharePoint, both SP List and SP Folder queries.

The one I'm working on at the moment mainly has SP Folder queries.

One query appending approx 50 files (similarly structured) into a table.

A couple of queries that take one specific file.

These queries are not loaded to the data model but are appended together into one table. I have to use several queries to feed this as I need to transform the different file structures so they share a common format.

Even to just open a single file in the query editor is taking an absolute age. I've just amended one of the queries and having to wait a very long time just for it to get off the Evaluating.... step of the refresh cycle.

I've made one query which pulls out the file list from SP which I'm then referencing in my other queries using Table.Buffer but that seems to be making no difference.

[v-lili6-msft]

hi, @Veles

The following article lists some useful tips.

https://www.thebiccountant.com/speedperformance-aspects/

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

You may have a try on it as a reference.

Best Regards,

Lin

[Veles]

Hi,

I've tried using Table.Buffer before but never had any success with it improving the query performance.

The below is a relatively simple query I have that is just pulling data from one file (albeit quite a large file but still not massive). This one still takes a significantly long time to evaluate given that it is essentially, open one file, promote headers, done.

let
    Source = Table.Buffer(FinanceSPConnection),
    #"Q3F-Budget20 Data xlsx_XXXX" = Source{[Name="Q3F-Budget20 Data.xlsx",#"Folder Path"="XXXX"]}[Content],
    #"Imported Excel" = Excel.Workbook(#"Q3F-Budget20 Data xlsx_XXXX"),
    Sheet1_Sheet = #"Imported Excel"{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"GL Account", type text}, {"Scenario", type text}, {"YearPeriod", Int64.Type}, {"WBS Ending", type text}, {"Currency", type text}, {"Period Value", type number}, {"Source", type text}, {"Ref", type text}, {"Sage COA", type text}})
in
    #"Changed Type"

[Veles]

I think I've narrowed down our issue.

One of the document libraries contains scans of compliance paperwork - tens of thousands of files. As the initial step on any query to SharePoint is to download the list of all the files this step is taking a very long time to do.

The query has just finished and there are 156k files which isn't a big dataset to download. However, I find the performance of extracting SharePoint lists to be incredibly poor so I think this is the real issue.

[v-lili6-msft]

hi, @Veles

This usually doesn't last, It could due to the network or the server,

It also could due to the hardware for running Power BI Desktop.

So you could try it again in different working environments.

Best Regards,

Lin