Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
HamidBee
Impactful Individual
Impactful Individual

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

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

HamidBee_0-1665056448425.jpeg

 

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

1 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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
v-yiruan-msft
Community Support
Community Support

Hi @HamidBee ,

As checked this official documentation, SharePoint List support refresh. 

yingyinr_0-1665038884982.pngPlease 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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


HamidBee
Impactful Individual
Impactful Individual

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors