Refresh SharePoint list data fast in Power BI

Learn how to query data (more than 5000 items) faster from a SharePoint list in Power BI

The challenge

When refreshing data in Power BI from SharePoint, and using the standard SharePoint connecter, the refresh can be painfully slow.

Also, when getting more than 5000 items, and trying to do an incremental refresh, you might also have encountered this error

This dataset includes a dynamic data source. Since dynamic data sources aren’t refreshed in the Power BI service, this dataset won’t be refreshed.

Let’s query data in a faster way that also works with scheduled refresh

The solution

A few things we need to consider

We need to be able to get more than 5000 elements
We need to avoid using dynamic data
We might need to expand column types like people or lookup fields

Step 1
In Power Query create a new blank query and click advanced editor

Insert the following code (with needed adjustments, explained below the code snippet)

let
    BaseURL = "https://yourtenant.sharepoint.com/sites/yoursitename/",
    ListName = "yourListName",
    ExpandColumns = "Author/Title,Editor/Title",
    Expand = "Author,Editor",

    CountItems = Json.Document(Web.Contents(BaseURL, [RelativePath = "_api/web/lists/GetByTitle('" & ListName & "')/items?$select=ID&$orderby=ID%20desc&$top=1", Headers = [Accept = "application/json"]]))[value]{0}[ID],
    StartID = List.Numbers(0, Number.RoundUp(CountItems / 5000), 5000),
    
    #"Converted to Table" = Table.FromList(StartID, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    EndID = Table.AddColumn(#"Converted to Table", "Custom", each [Column1] + 4999, type number),
    #"Renamed Columns" = Table.RenameColumns(EndID,{{"Column1", "StartID"}, {"Custom", "EndID"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"StartID", type text}, {"EndID", type text}}),
    selectedfields = "&$top=5000&$select=*," & ExpandColumns & "&$expand=" & Expand,
    ListItems = Table.AddColumn(#"Changed Type", "ListItems", each Json.Document(Web.Contents(BaseURL, [RelativePath = "_api/web/lists/GetByTitle('" & ListName & "')/items?$filter=(ID ge " & [StartID] & ") and (ID le " & [EndID] & ")&$top=5000&$select=*," & ExpandColumns & "&$expand=" & Expand, Headers = [Accept = "application/json"]]))[value]),
    #"Removed Other Columns" = Table.SelectColumns(ListItems,{"ListItems"}),
    #"Expanded ListItems" = Table.ExpandListColumn(#"Removed Other Columns", "ListItems")
in
    #"Expanded ListItems"

Change the following variables

BaseUrl
Change to your own site (don’t forget the last /)
ListName:
Change to your list name (the actual name, the one you see on your site)
ExpandColumns:
If you want to expand any columns like a people field, and get the title (name) or the EMail, you insert the expand columns here, like below:
ExpandColumns = "Author/Title,Editor/Title,PersonColumn/EMail,PersonColumn/Title,LookupColumn/Title",
Expand:
Everytime you introduce a new column to expand, it is important you write the name here, like this:
Expand = "Author,Editor,PersonColumn,LookupColumn",

Step 2
You can now select the arrow icon and click expand

Unclick use original column name as prefix and click OK

Your list is now ready, and you can begin to expand your columns.

Notice that everytime you introduce a new column, you need to go back to this step, and select the new column

Note the that code above will only work if you have at least 1 expand column introduced.

The code above is currently selecting all the non-expandable columns in your list, so you can improve the code even further, by selecting ONLY the columns that you need

In this part of the code, replace the asterix * in the code below, with the columns that you need

selectedfields = "&$top=5000&$select=*," & ExpandColumns & "&$expand=" & Expand,

Here is an example

selectedfields = "&$top=5000&$select=ID,ProjectName,Title,Street," & ExpandColumns & "&$expand=" & Expand,

And also here

[EndID] & ")&$top=5000&$select=*," & ExpandColumns & "&$expand=" & Expand, Headers = [Accept = "application/json"]]))[value]),

[EndID] & ")&$top=5000&$select=ID,ProjectName,Title,Street," & ExpandColumns & "&$expand=" & Expand, Headers = [Accept = "application/json"]]))[value]),

Another thing to consider is if you really need to expand the people columns.

Instead you could get the UserInformationList from SharePoint, and combine that table with the peopleId

I got the inspiration to this solution from Peter Tadrous

‎

Advanced Power BI

Refresh SharePoint list data fast in Power BI

The challenge

The solution

Further refinement

See also