Get Data From SharePoint Lists … But Fast

SharePoint lists remain a convenient way to store data. However, get data from SharePoint lists with lots of items can be frustratingly slow. The updated SharePoint list connector improved things, but not completely. The video below walks through a way to use the Web connector with the SharePoint REST API to get data much faster (hours -> minutes, minutes -> seconds).

Below are the function and query versions of the M code described in the video to get your SharePoint list data much faster. Enjoy!

Function Version

(tenant_name, site_name, list_name) =>
   let
     tenantname = tenant_name,
     sitename = site_name,  // if a subsite use "Site/SubSite"                                                                                                      
     listname = list_name,
     baseurl = "https://"
       & tenantname
       & "/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")
   in
     #"Expanded value"


Query Version

let
   sitename = "",  // if a subsite use "Site/SubSite"                                                                                                      
   listname = "BigList",
   baseurl = "https:///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,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"]]
       )
     )
   ),
   #"Expanded Items" = Table.ExpandRecordColumn(Custom1, "Items", {"value"}, {"value"}),
   #"Expanded value" = Table.ExpandListColumn(#"Expanded Items", "value")
 in
   #"Expanded value"

You May Also Like

About the Author: Pat Mahoney

Pat Mahoney is a chemist in Indiana who enjoys teaching others to use Power BI and other tools, and providing pro bono data science help/instruction to nonprofits, schools, etc.

5 Comments

  1. Hi Pat! I’m using the code above to successfully gather data from a number of SharePoint lists our company keeps. However, when I publish the report to PowerBI Service I am unable to schedule a refresh because the queries contain “dynamic data sources.” Have you encountered this before and do you know if it can be fixed? My suspicion is that the api calls are dynamic in the sense that they rely on the ItemCount to create a skip list that will be iterated through. Thank you!

  2. Pat, thank you so much for this! Worked like a charm for a dashboard we use. Instead of 1.5-2 hours, it now finishes a refresh within a minute.

  3. Hi Pat,
    I noticed that the query didn't retrieve all items in some cases. Changed itemcount to the following:
    itemcount = Json.Document(Web.Contents( baseurl & "items?$select=ID&$orderby=ID%20desc&$top=1", [Headers = [Accept = "application/json"]]))[value]{0}[ID],

    This helps when the you deleted items from the list, and thus the IDs are getting bigger than the actual item count.

    1. Rene you are a genius! Thank you for posting that fix. I came across the anomaly today and checked back here in case someone had come across it and Voila!!!

  4. Except don't do this mod on a site's hidden User Information List – it gives an item count of millions when the actual count is just hundreds. For my non-special lists, I am using Rene's mod but for the special site Users Information list I'm using Pat's original.

Leave a Reply

Your email address will not be published. Required fields are marked *