cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

SharePoint Online slow refresh

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?

24 REPLIES 24
Highlighted

Have you tried this with a list/library with 100k+ items?  Using PowerShell from my desktop I can quickly export list data, but I'm afraid of using Flow due to the possibility of getting throttled.

Once the report is set up, I'm hoping that an incremental refresh will help bridge the load time gap.

Highlighted
New Member

Same here, it's taking ages to load CSV file from SharePoint to PowerBI datamodel. It's terrible, please Microsoft do something it is so annoying. I don't even have complicated data transformation in the query.
Highlighted
Super User III
Super User III

Please see this post on a much faster way to pull SP list data.

https://community.powerbi.com/t5/Desktop/SharePoint-list-query-alternative-or-optimization/m-p/10926...

 

Regards,

Pat





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




Highlighted

At a high-level, what are the steps I need to take to implement this?
I'm starting in PBI Desktop

Highlighted

In that link, I provided some M code both in query form and in function form.  Below is the query form.  Just paste it into a blank query in Advanced Editor in the query editor over the existing text.  Replace the text in quotes in the site, tenant, and list.  Look at the URL for your SharePoint List for reference.  You may need to adapt the hard-coded part of the URL too if it doesn't match.  This will return a List, which you can click on to get a list of records, then convert that list to a table, then expand the records choosing the fields of interest.

 

let
Source =

let

site = "NameOfMySite",

tenant = "NameOfMyTenant",

list = "NameOfMyList",

getdata = Json.Document(Web.Contents("https://" & tenant & ".sharepoint.com/sites/" & site & "/_api/web/lists/GetByTitle('" & list & "')/items?$top=5000", [Headers=[Accept="application/json"]]))

 

in

getdata
in
Source

 

Note the above will return up to 5000 records.  If you want to use pagination to get many more, you need to use a M code and a web call like below (I haven't adapted the friendly form above to work like this yet).  Update the parts in <> (delete the <>).  This gets the count of items in your list, make a list of numbers up to that counting by 5000, makes each web call so you can expand all the result together. 

 

Note there are three rows called "fieldselect"; two should be commented out at any time.  You need to choose depending on if you have lookup/choice columns in your list.  There is example syntax in the latter two on how to choose which fields to expand in the return.

 

I still plan to write a blog post about this one day ...

 

let

    sitename ="<nameofyoursite", // if a subsite use "Site/SubSite"

    listname = "<nameofyourlist>",

    baseurl = "https://<yourtenantURL>/sites/" & sitename & "/_api/web/lists/GetByTitle('" & listname & "')/",

    itemcount = Json.Document(Web.Contents(baseurl&"ItemCount", [Headers=[Accept="application/json"]]))[value],

    skiplist = List.Numbers(0, Number.RoundUp(itemcount/5000), 5000),

    #"Converted to Table" = Table.FromList(skiplist, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Skip"}}),

    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Skip", type text}}),

    fieldselect = "&$top=5000", // all fields with no expansion

    //fieldselect = "&$top=5000&$select = Id,Title,Person,Date", // list desired fields (no expansion)

    //fieldselect = "&$top=5000&$select = Id,Title,Person/LastName,Person/FirstName,Date&$expand = Person", //expand list fields

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Items", each Json.Document(Web.Contents(baseurl& "/items?$skipToken=Paged=TRUE%26p_ID=" & [Skip] & fieldselect, [Headers=[Accept="application/json"]]))),

    #"Expanded Items" = Table.ExpandRecordColumn(#"Added Custom", "Items", {"value"}, {"value"}),

    #"Expanded value" = Table.ExpandListColumn(#"Expanded Items", "value"),

    #"Expanded value1" = Table.ExpandRecordColumn(#"Expanded value", "value", {"Id", "Title", "Person", "Date"}, {"Id", "Title", "Person", "Date"})

in

    #"Expanded value1"

 

Regards,

Pat





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors