Forum Discussion
SharePoint list query alternative or optimization
hjaf FYI that I finally made a video to describe this approach, and am adding it here for others that may find this post. It also gets the count of items and makes the right number of API calls.
Get SharePoint List Data with Power BI ... Fast - YouTube
Also, a reminder to mark one of these as the solution.
Regards,
Pat
That looks good to me. I'm glad it works for you. I'm curious, how much of an upgrade time improvement did you see?
I wanted to write a blog with this hoosierbi.com (my blog about making Power BI pro bono with non-profit benefits). Your question exactly on this subject. I ended up doing a query version and function of it which makes it easier to modify/use. The function could be used if there were multiple lists that had the same columns and one had a tenant table, site, list (tenant, of course, would not change within a company).
Here they are:
As a function -
Leave
Origin (tenant name, name, name, and so on) >
Leave
site : site name,
tenant - tenant's name,
list : list name,
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
As a consultation
Leave
Source?
Leave
"NameOfMySite" website,
tenant : "NameOfMyTenant",
list of names "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
If this works for you, mark it as a solution. Praise is also appreciated. Please let me know if you don't.
Best regards
Pat
I probably did a lot sub-optimal steps in the previously used standard method, so I went from literally 3-4 hours, down to less than 3 minutes! But even just getting the raw data with the traditional query method still took 1+ hour, I believe its due to a lot of choice-columns and lookups in the list.
It would be interesting to create a function that determines the maximum ID and automatically get all the items. I think there is some room for further optimizing, regarding the overlapping queries this method produces. because the first 5k items have IDs ranging from 5k to 80k means that the first 16-ish queries will basically return the same data, but anyways. going down from hours to mere minutes is a giant leap I am very satisfied with 😄 Thank you again mahoneypat!
PS Updates to previous query: I realized did not clear out all the duplicates, so I added an additional duplication removal on IDs. I also put tennantId, sitename and list into parameters which made it a bit easier to configure:)