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.

Leave a Reply

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