Unable to set refresh schedule for report with sharepoint list

[anuradha01]

Hi Community 

I need to build Power BI reports using data collected in SharePoint lists via a Power app.

Initially, I used a Sharepoint list connector to get data from the online Sharepoint list but data refresh was too slow.

Then I implemented the below function to connect to the sharepoint list which made the refresh on the PBI desktop much faster-

= (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
#"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"

However now the issue is I am unable to schedule auto refresh on PBI service it says 

"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. Learn more: https://aka.ms/dynamic-data-sources.

• Data source for Query1"

Can you please suggest a solution to ensure the data source connection is faster and i can schedule auto refresh on PBI service as well?

Please let me know incase any other details are required.

Thanks & Regards

Anu

[lbendlin · Accepted solution]

Please follow the documentation. Use RelativePath and Query parameters. https://learn.microsoft.com/en-us/powerquery-m/web-contents#example-1

[danextian]

Hi @anuradha01 

The issue arises because Power BI service does not support dynamically constructed URLs. You will need to use Web.Contents relative paths options.

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 & "')/",
    
    // Define root URL separately for Power BI service compatibility
    rootUrl = "https://" & tenantname & "/sites/" & sitename & "/_api/",
    
    // Fetch item count with a relative path
    itemcount = Json.Document(Web.Contents(rootUrl, [RelativePath = "web/lists/GetByTitle('" & listname & "')/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
    
    // Fetch paginated items using a relative path
    #"Added Custom" = Table.AddColumn(
        #"Changed Type", 
        "Items", 
        each Json.Document(
            Web.Contents(rootUrl, 
                [RelativePath = "web/lists/GetByTitle('" & listname & "')/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"

 More on Web.Contents - https://learn.microsoft.com/en-us/powerquery-m/web-contents 

[anuradha01]

Hi Dane

Thank you for the prompt response.

I replaced the code with the one you shared but still it gives same error when I am trying to set automatic refresh.

Can you please share if there is some other workaround for the same?

Thanks 

Anu

[lbendlin]

Please follow the documentation. Use RelativePath and Query parameters. https://learn.microsoft.com/en-us/powerquery-m/web-contents#example-1

[anuradha01]

I did used relative path but still its not allowing to set auto refresh.

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 & "')/",
    
    // Define root URL separately for Power BI service compatibility
    rootUrl = "https://" & tenantname & "/sites/" & sitename & "/_api/",
    
    // Fetch item count with a relative path
    itemcount = Json.Document(Web.Contents(rootUrl, [RelativePath = "web/lists/GetByTitle('" & listname & "')/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
    
    // Fetch paginated items using a relative path
    #"Added Custom" = Table.AddColumn(
        #"Changed Type", 
        "Items", 
        each Json.Document(
            Web.Contents(rootUrl, 
                [RelativePath = "web/lists/GetByTitle('" & listname & "')/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"

[v-saisrao-msft]

Hi @anuradha01,

We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.

Thank you.

[v-saisrao-msft]

Hi @anuradha01,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-saisrao-msft]

Hi @anuradha01,
I wanted to check if you had the opportunity to review the information provided by @lbendlin @danextian . Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[lbendlin · Accepted solution]

Query parameters should go into the Query section, not the RelativePath section.

Delete things that are not relevant (like baseurl).  Make sure your rooturl is actually callable by itself.

[lbendlin · Accepted solution]

Please follow the documentation. Use RelativePath and Query parameters. https://learn.microsoft.com/en-us/powerquery-m/web-contents#example-1