"..the following data sources currently don't support refresh" error

[HamidBee]

I created a Power BI report using a query called Query1. It connects to a SharePoint List. Here is the query:

let
sitename ="jeilani_thebravanesesociety_co_uk", // if a subsite use "Site/SubSite"
listname = "Customers",
baseurl = "https://thebravanesesocietycouk-my.sharepoint.com/personal/" & 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"

When I try to refresh the dataset I get an error which says: 

You can't schedule refresh for this dataset because the following data sources currently don't support refresh:

• Data source for Query1

How can i ammend this query so that I can succesfully query the data?. Thanks in advance.

[v-yiruan-msft]

Hi @HamidBee ,

As checked this official documentation, SharePoint List support refresh. 

Please check if you faced the similar situation with the one in this blog which written by Chriss Webb...

Web.Contents(), M Functions And Dataset Refresh Errors In Power BI

the data source is a call to Web.Contents() then Power BI only checks the base url passed into the first parameter during static analysis – and as my previous blog post shows, by using the RelativePath and Query options with Web.Contents() you can leave the value passed to the first parameter as a static string. Therefore, the following version of the query does refresh successfully in Power BI...

---

Best Regards

[HamidBee]

Thank you for your reply. I had a look at the link you provided. My M query includes the web.contents function. I'm not sure what else to do. How can I adjust the code I currently have?. @mahoneypat I used your M query for this. I'd kindly like to ask if you can please suggest a solution also.

[mahoneypat · Accepted solution]

FYI that I am working on an update to this function to enable web refresh (the key is using RelativeQuery in the itemcount and GetData steps), but below is some updated M code that works. A few things to note:

1. The base url has to be static for web refresh (i.e., can't pass domain and site info in dynamically like in previous version).

2. You may have to "Skip Test Connection" when you set up credentials in the dataset settings in the Service to get it to work.

3. Note that there are two versions of the itemcount step. Both should work in most cases, but confirm that you are getting all items and try the other (comment/uncomment) as needed.

let
    siteurl = "https://abcde.sharepoint.com/sites/Sandbox",
    listname = "BigList",
    itemcount = Json.Document(
                    Web.Contents(
                        siteurl,
                        [
                        RelativePath = "_api/web/lists/GetByTitle('" & listname & "')/items?$select=ID&$orderby=ID%20desc&$top=1",
                        Headers = [Accept = "application/json"]
                        ]
                    )
                )[value]{0}[ID],
    // itemcount = Json.Document(
    //                 Web.Contents(
    //                     siteurl,
    //                     [
    //                     RelativePath = "_api/web/lists/GetByTitle('" & listname & "')/ItemCount",
    //                     Headers = [Accept = "application/json"]
    //                     ]
    //                 )
    //             )[value],
    skiplist = List.Numbers(0, Number.RoundUp(itemcount / 5000), 5000),
    skiplisttable = Table.FromList(skiplist, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    RenamedColumns = Table.RenameColumns(skiplisttable, {{"Column1", "Skip"}}),
    ChangedType = Table.TransformColumnTypes(RenamedColumns, {{"Skip", type text}}),
    
    //Common in only one of the fieldselect lines below, defining your select and expand columns if needed
    fieldselect = "&$top=5000", // all fields with no expansion
    //fieldselect = "&$top=5000&$select=ID,Title,Date,PersonColumn,ChoiceColumn,LookupColumn", // list desired fields (no expansion) -No Spaces!
    //fieldselect = "&$top=5000&$select=ID,Title,Date,PersonColumn/LastName,PersonColumn/FirstName,ChoiceColumn,LookupColumn/Title,LookupColumn/Project,LookupColumn/ProjectStatus&$expand=PersonColumn,LookupColumn", //expand list fields - No Spaces!
    
    GetData = Table.AddColumn(
        ChangedType,
        "Items",
        each
            Json.Document(
                Web.Contents(
                    siteurl,
                    [
                        RelativePath = "_api/web/lists/GetByTitle('"& listname & "')/items?$skipToken=Paged=TRUE%26p_ID=" & [Skip] & fieldselect,
                        Headers = [Accept = "application/json"]
                    ]
                )
            )
    ),
    ExpandRecordsFromList = Table.ExpandRecordColumn(GetData, "Items", {"value"}, {"value"}),
    ExpandedValue = Table.ExpandListColumn(ExpandRecordsFromList, "value"),
    RemovedOtherColumns = Table.SelectColumns(ExpandedValue, {"value"})


in
    RemovedOtherColumns

Pat

[HamidBee]

Thanks it worked. I can now refresh the dataset, the only downside is that after a few refreshes it starts to take a really long time to refresh. It seems like some sort of bottleneck. I've created a Power App app and I connected it to the SharePoint List. I also created a Power BI report, connected it to the SharePoint List and uploaded it onto the workspace. The idea is to add rows of data or edit rows using the Power App form and then be able to see that in the Power BI report after refreshing the dataset. It needs to be able to handle several refeshes within a minute for example. It starts off fine but then it just goes slow again. I'm not sure why.