Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Please see this post on a much faster way to pull SP list data.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks for the code. I used it to create a report, uploaded it onto the workspace but when I try to refresh the data set I get the following error:
You can't schedule refresh for this dataset because the following data sources currently don't support refresh:
Here is a link to the question:
At a high-level, what are the steps I need to take to implement this?
I'm starting in PBI Desktop
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Does this work for SP 2016 on prem? The regular connector is attrocious and I have 20k items to load!
Thanks!
SharePoint on-prem and online does some sort of IP address-based throttling. I spent days working on a multi-threaded application to update a "large list" in SP and there was almost no befenift to doing single vs mulit-threading / thread-pooling. In the end, I ended up doing a distribuited app approach.
Some of the API-based solutions mentioned in this thread are FAST, but I've never been able to get them to auto-refresh once published.
Thanks for that! I might try pushing it to an excel file stored in sharepoint online and pulling it in with an odata query. That might have the same problem you were describing though...
So I used this as work around and the refresh takes less than a Minute.
Im exporting every 7 mins the couple of sharepoint lists into a CSV file and dumping them into a shared drive, where I set up the PBI connections.
There is a few links out there for reference on this flow. This is what I used.
http://www.sharepointsamples.com/export-sharepoint-list-items-as-csv-using-microsoft-flow/
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.
Thanks @Joorge_C it seems that i's the only solution. Quite very disappointing that we must resort to such dramatic workarounds to get two Microsoft environment to talk to each other...
I am having this same issue. Wondering if it only happens when you go over the 5,000 item list view threshold limit in Sharepoint online? The list I am working with currently has about 11k records. Not that it really matters, just curious. I would love to see this fixed. I don't think the other way of doing it via OData query gives you nearly as much data. Example, I have a lot of items in my list with custom permissions (shared with specific people), and pulling with Power Query via the standard connector allows access to those security role assignments by item.
Hi everybody,
I experiencing the same issue particularly with the CSV files. Has anybody found a solution? I already updated PowerBi desktop to March 2020 version. The refresh of each step can last up to 10 minutes. If I go to the Sharepoint (Cloud) via Internet Explorer, the access to the site and files is quick fast. Thanks.
I have the same issue. My report is connected to 8 SharePoint online lists which are then appended. Would it make a difference if I connect using the Odata feed?
Hi @Anonymous ,
Please check whether there existing any step in Power Query cosuming too much cpu or memory. And try to use Table.Buffer to improve the data load performance.
SharePoint Online List- Power BI - slow query update
Slow refresh of SharePoint list data
Best regards,
Yuliana Gu
Hi Yuliana,
I do not have anything complicated happening on my queries.
This issue is replicated just by: New query -> SharePoint list -> Browse to list. That is all I am doing and it takes forever.
Another issue may be that SharePoint pulls all the data before filtering, unlike SQL
Bumping this
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |