SharePoint Online slow refresh

[Anonymous]

As the title says.

I have seen this issue posted several times but never found anything that may help.

My reports pull from both a SQL db and several SharePoint lists. The SQL dbs will import 100,000s of records - typically faster than the time it takes for the SharePoint list, which pulls in 25,000 rows. I can see on the Data Refresh the size that has been downloaded for the SharePoint list, and it seems to be pulling in the data slower than 500kb/s - what is this?

On my Excel version of this sheet, I can use the

let
    Source = Table.Combine
    ({
        OData.Feed("mysite/_api/web/lists/GetByTitle('mylist')/Items?$filter=
        Date ge '" & DateSplit(1) & "' and Date le '" & DateSplit(2) & "'"),
        OData.Feed("mysite/_api/web/lists/GetByTitle('mylist')/Items?$filter=
        Date ge '" & DateSplit(3) & "' and Date le '" & DateSplit(4) & "'"),
        OData.Feed("mysite/_api/web/lists/GetByTitle('mylist')/Items?$filter=
        Date ge '" & DateSplit(5) & "' and Date le '" & DateSplit(6) & "'")
    }),

Where the user will pick a date and it will fill a table with the dates for the month. It essentially splits the query into three, due to the 5000 limit threshold, but it is faster than it would otherwise be.

For BI, where the expectation is that the user can pick any date (ranges), I will need to have the full data imported preferably.

There is nothing demanding in my query,

let
    Source = SharePoint.Tables("mysite", [ApiVersion = 15]),
    #"GUID" = Source{[Id="myGUID"]}[Items],

So what gives with the SQL db being so much faster than SharePoint Online? Is it because SharePoint Online is given less resources due to it not being premium?

[Anonymous]

Im having the exact same issue,

Origionally i thought it was an old powerbi file with too much going on (queries etc) so i started one from fresh to just use two lists and its unbearably slow, the download from sharepoint to powerbi is going at a rate of 500Kb/s (it aint my connection ive tried from two very good internet connections)

How the hell am i supposed to do very simple reports on large lists? (its over 30000+ entries)

---

@Anonymous wrote:

Will bump again.

Surely others using SharePoint Online have the same concern with how the standard PowerQuery (Excel & BI) downloads so much in file size and so slowly?

Again in Excel it can be worked around but not in BI as far as I can see, so refreshing datasets will just get slower and slower.

It seems that SharePoint online will not filter the data on the standard query until it has all been downloaded. I can see this by, in preview, when I filter a column and find it downloads the same file size again before completing the filter

---

[Anonymous]

Is it possible to have an official response on this?
Is it that the connector is just badly done, a fault on our sides, or something else?

And yes @Anonymous I notice the same with the slow connection speed. It is barely 400KB/s which is unacceptable.

To be clear: If I write a VBA SQL connection, it pulls it much faster.

Another thing: Go to SharePoint Online -> Your list -> Export to Excel. You will notice it pulls it much faster, so what gives with the slow PowerQuery connection?

[jvondermans]

I have the same issue, sometimes the download speed drops to 2 KB/s, so the 10 MB I'm trying to retrieve takes ages...

[Anonymous]

Having the same issue and never found a workaround. I experience the same speeds that @jvondermans mentioned with no solution. I tried changing some of the option settings related to Data Load but that never showed any significant changes.

[Anonymous]

There seems to be no Power Query solution with the standard SharePoint Online connector.

If you can filter and bring in less than 5000 items, you can do a Odata feed which pulls in much much faster.

As an example, I do

let
    Source = OData.Feed("<SharePoint Site>/_api/web/lists/GetByTitle('<List>')/Items?$filter="
            &"Date eq '" & DateSearch() & "'")
in
    Source

Where

DateSearch()

is a custom function to get the date.

This might be enough for you.

[jvondermans]

Hi DCM,

Thanks for this solution, but for me this is not working. It is much faster though, but I miss the necessary columns.

regards, Jordy

[StephenF]

Same issue but with a .TSV stored in a sharepoint 365 documents folder.

Takes ages to even start drawing down data.

[HamidBee]

Hi. I'm just curuous to know if you ever did resolve this issue. I'm currently having the same problem. I tried to use the M query provided here but I get a data refresh error here:

https://community.powerbi.com/t5/Desktop/quot-the-following-data-sources-currently-don-t-support-ref...

[Anonymous]

We've been having identical issues for a long time - if there is a way to diagnose performance issues/bottlenecks, I'd love to see where things are slowing down.