A Faster Way to Get Data from SharePoint Lists in Power Query

The default method Power Query uses to retrieve SharePoint Lists records is unbearably slow. Here’s a better way to retrieve many records from a SharePoint list in a flash for faster data loads.

If you need to sync a large Microsoft SharePoint List to an Excel workbook or Power BI report, this will save you some considerable time waiting on data refreshes. While the “Get Data From SharePoint Online List” feature works, it is far from efficient, particularly when expanding columns like person fields. If your data load or sync refresh from these connections is too slow for you, this post is for you.

Solution: Rest API

SharePoint has a REST API that can retrieve lists in chunks of 5000 records at once¹. With some clever combination of OData methods and M scripting in Power Query shared below, data from these lists can be synced with lightning speed. This is worthwhile even if your list contains fewer than 5K items, it just means it will be retrieved effectively instantly!

The Methodology

Here’s the general steps we’ll take:

1. Determine how many items are in the list and thus how many batches of 5K it will take to retrieve them all.
2. Build a table with a row for each batch.
3. Add a column to the table and have it’s value be the result of a REST query to SharePoint. This query will reference the “skip” value for the given row to set how many records to skip over when pulling each subsequent batch.
4. expand the results to reach the SharePoint columns embedded in the JSON response.

M Query Code

The Power Query M code will make a couple or more calls to your SharePoint Online site (at least one, plus another for each batch of 5000 records to fetch). Let’s look at each step of the Power Query and examine what it does. Some of this could be broken into separate variables if you desired.

baseurl is a user-entered text variable that stores the URL of the site and name of the list to fetch.

itemcount variable fetches and stores the total number of items in the List.

skiplist is an array that determine how many batches of 5000 it will take to fetch all records in the itemcount.

• For example, if the list had 12,000 records to retrieve, this skiplist would have three items in it: one for records 1-5000, another for second batch of records of 5001-10000, and a final for the third batch to get records 10001 to 12000.
  

The next three steps — Convert To Table, Rename Column, and Changed Type — transforms the skiplist into single table column called Skip, formatted as text.

• We transformed the Skip to text so it can be concatenated easily into the URL later. Power Query is strict about data types, so we couldn’t concatenate it if the Skip was at numerical rather than text value type.
  

FieldSelect and Filters is a user-provided text variable that controls how many items download per batch (5000 here), as well as allow the user to select specific fields or filter criteria for controlling what to retrieve.

• In this case I’m using it to only retrieve the ID number, Title (name), and email address from the User List of our site. My filter to only include records where the email address contains an @ symbol helps me exclude inactive users and non-human system accounts for this use case.
  

The Retrieve JSON step adds an additional Items column to the skiplist table built earlier (which in this case has 3 rows in it). The value for this Items column is set to be the results of an API call to the SharePoint URL. This URL references to the current row’s “skip” value, so each row of the skiplist downloads a 5000-record “chunk” of items and stores it in that Items column.

Expand Items step expands the Items column to go one level down in the JSON response hierarchy to reveal the “value” records containing the list of records we’re retrieving.

Expanded Values step expands the embedded lists so every item is seen on a distinct row.

• Now instead of 3 rows you have one for every SharePoint record in your list!
  

Expand Value now reveals the columns you want to see (or selected if you used the filters in the fieldselect).

Trying It Yourself

To try it yourself, just copy the M code below and paste it into the Query pane of Excel or directly into the Advanced Editor in Power Query. The only things you need to edit are your tenant, site name, and the name of the List and Columns to query.

I use this to sync any data from SharePoint because it’s vastly faster than the default method. Power BI dashboards in particular can refresh in a few seconds rather than hours!

I’ve used this with lists of several thousand items, as well as lists that contain at this time more than 300,000 records like extensive customer case tracking history lists or other quickly-growing lists. Seriously, do not sleep on using this in your reports or data.

Code

// User Info List
let
    baseurl = "https://tenant.sharepoint.com/sites/sitename/_api/web/lists/GetByTitle('User Information List')/",
    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 and filters" = "&$top=5000&$select=ID,Title,EMail&$filter=substringof('@', EMail)",
    //"fieldselect with expand" = "&$top=5000&$select=Id,Title,Choice,LookupColumn/Title,LookupColumn/Project,LookupColumn/ProjectStatus,Date,Person/LastName,Person/FirstName,Person/EMail&$expand=LookupColumn,Person",

    #"Retrieve JSON" = Table.AddColumn(#"Changed Type", "Items", each Json.Document(Web.Contents(
            baseurl,
            [
                RelativePath="/items?$orderby=ID&$skipToken=Paged=TRUE%26p_ID=" & [Skip] & #"fieldselect and filters",
                Headers=[Accept="application/json"]
            ]
            )
        )
    ),
    #"Expanded Items" = Table.ExpandRecordColumn(#"Retrieve JSON", "Items", {"value"}, {"value"}),
    #"Expanded value" = Table.ExpandListColumn(#"Expanded Items", "value"),
    #"Expand Value" = Table.ExpandRecordColumn(#"Expanded value", "value", {"Title", "EMail", "ID"}, {"Title", "EMail", "ID"}),
    #"Remove Skip Column" = Table.RemoveColumns(#"Expand Value",{"Skip"})
in
    #"Remove Skip Column"