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
Hi mahoneypat ... I am using your approach to get the items using REST API and it works really fast. But I have published the file to the power bi service and I cannot set the schedule refresh because I have this error: "You can't schedule refresh for this dataset because the following data sources currently don't support refresh".
Do you know how to overcome this problem?
Thanks
Someone in this forum said to look up a solution here: https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code/
I did so, and after reading a number of posts on the subject I was able to get it working in PowerBI online service. I won't bore you with a lot of details, here's just a simple modified M Code you need to use instead.
What this does is uses a static URL for the first parameter of the Web.Contents() function, then uses a little known (apparently) 2nd parameter to actually pass the rest of the URL (relative path) and query/value pairs into the query string.
You should be able to just modify the bold parts of this and be ready to go. I modified this from the original on the blog post and it even has the original commented-out bits. You can remove those if you want.
I actually hard-coded most of the paths instead of using baseurl or other variables. I probably could have still used them but I wanted immediate success 🙂
This has been an aboslute lifesaver to me. Now my list with 120K list items only takes 15 seconds to refresh.
let
baseurl = "https://TENANT.sharepoint.com/sites/SITE/_api/web/lists/GetByTitle('LIST')/",
itemcount = Json.Document(Web.Contents("https://TENANT.sharepoint.com/sites/SITE/_api/web/lists/GetByTitle('LIST')/"&"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,Choice,LookupColumn/Title,LookupColumn/Project,LookupColumn/ProjectStatus,Date,Person/LastName,Person/FirstName,Person/EMail&$expand=LookupColumn,Person",
//Custom1 = Table.AddColumn(#"Changed Type", "Items", each Json.Document(Web.Contents("baseurl" & "/items?$skipToken=Paged=TRUE%26p_ID=" & [Skip] & fieldselect, [Headers=[Accept="application/json"]]))),
Custom1 = Table.AddColumn(#"Changed Type", "Items", each Json.Document(Web.Contents(
"https://TENANT.sharepoint.com/sites/SITE/_api/web/lists/GetByTitle('LIST')/",
[
RelativePath="/items?$skipToken=Paged=TRUE%26p_ID=" & [Skip] & fieldselect,
Headers=[Accept="application/json"]
]
)
)
),
#"Expanded Items" = Table.ExpandRecordColumn(Custom1, "Items", {"value"}, {"value"}),
#"Expanded value" = Table.ExpandListColumn(#"Expanded Items", "value")
in
#"Expanded value"Glad you found that approach. I was about to post a link to same article.
Pat
Hello CmdrKeene,
Where do I paste this code in Power BI? Do I just paste it intop a Blank query?
Thanks
Hello lcasey,
Yes that's basically the process. Create a new blank query, go to the Advanced Editor from the view toolbar, delete everything and paste in my code instead.
Hi CmdrKeene, first of all, thank you for your code, it worked in Power Bi Service, however I have a question, your code get all the values of the SharePoint List but i want to get also the records (see my picture), do you know how i could do that?
Great question. For the Author/Editor, those are really just references to another SharePoint table/list called "User Information List" that exists on all SharePoint sites. So for my purposes I retreive this list as well and then setup a relationship in my PowerBI report between the two.
SP Tickets (my main giant list with 200,000 records and growing)
SP UserMeta (just the User Information List with a custom name)
This could be handled in later steps of the query instead but I went this path.
I got this to work.
Change the Custom1 to the following -Custom1 = Table.AddColumn(#"Changed Type", "Items", each Json.Document(Web.Contents(baseurl& "/items", [Headers=[Accept="application/json"],Query=[#"$skipToken"="Paged=TRUE",p_ID=[Skip],#"$top"="5000"]]))),This was based on a blog from Chris Webb - https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/This fixed it for me. It was not refreshing as PBI App was recognising it as dynamid data source. Replaced Custom1 step with this: Custom1 = Table.AddColumn(#"Changed Type", "Items", each Json.Document(Web.Contents(baseurl, [RelativePath = "/items?$skipToken=Paged=TRUE%26p_ID=" & [Skip] & fieldselect, Headers=[Accept="application/json"]]))),
It works all right for me
