Forum Discussion

jbrines's avatar
jbrines
Advocate III
1 year ago
Solved

Help with function query that won't work with scheduled refresh

Hi Guys,

 

I have uploaded my desktop fil to Power BI in the cloud but it won't allow a scheduled refresh and gives the following error.

 

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

  • Data source for Query1

 

Here is Function Query1 , I have over 30 other quries that call it and this list will grow.

 

 

(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
    //fieldselect = "&$top=5000&$select = Id,Title,Person,Date", // list desired fields (no expansion)
    //fieldselect = "&$top=5000&$select = Id,Title,Person/LastName,Person/FirstName,Date&$expand = Person", //expand list fields
    #"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"

 

 

How can this be amended to work online and scheduled refreshes?

 

Thanks

Need Help
  • v-veshwara-msft's avatar
    v-veshwara-msft
    1 year ago

    Hi jbrines ,

    Thanks for the follow-up and for highlighting the issue with $skiptoken.

    You're right, in the original script, $skip was used, and $skiptoken wasn’t present initially. When both appear together in the request URL (even unintentionally), SharePoint returns an error, as it doesn’t support using both parameters at the same time.

     

    That said, it's great to hear you found a working solution using SharePoint.Tables as discussed here. This is the recommended approach when working with large SharePoint lists, as it handles paging internally and avoids the 5000-item threshold issues altogether.

     

    Thanks again for your engagement on this topic.

    Please consider accepting it as a solution so others with similar scenarios can benefit as well.

     

    Regards,
    Vinay,
    Community Support Team.

19 Replies

  • v-veshwara-msft's avatar
    v-veshwara-msft
    Community Support
    1 year ago

    Hi jbrines ,

    Thanks for engaging with the Fabric Community. Apologies for the delayed response.

    I just wanted to follow up and check if the issue has been resolved. Thanks to Greg_Deckler for the helpful suggestions.

    If the issue is still unresolved, here are some alternative approaches that might work for your scenario:

     

    1. Instead of  function query, you can try using List.Generate() to iterate through the pages within a single query. This allows you to fetch paginated data without needing dynamic URL generation.
    List.Generate - PowerQuery M | Microsoft Learn

     

    2. If the SharePoint API limits or scheduled refresh restrictions persist, consider preloading the data into a Power BI Dataflow first. This allows Power BI to handle the data transformation before importing it into your report, ensuring stable scheduled refreshes.

     

    3. The 404 error you encountered suggests there might be an issue with the API URL. Double-check that the constructed URL matches the SharePoint API documentation and that it's tested outside Power BI to ensure it works correctly.

    For further details on managing large SharePoint lists and libraries, you can refer to this Manage large lists and libraries - Microsoft Support

     

    Please let us know if any of these approaches help or if you need further assistance.

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and a kudos would be appreciated.

    Best regards,
    Vinay.

    • jbrines's avatar
      jbrines
      Advocate III
      1 year ago

      Hi v-veshwara-msft ,

       

      Thanks for getting back to me.

       

      I am fairly new to Power BI as you can see.

       

      Some of the ist will be over 10/15k in rows

       

      Could you give me an example on how the List Generate would help me?

       

      Thanks

       

      John.

      • v-veshwara-msft's avatar
        v-veshwara-msft
        Community Support
        1 year ago

        Hi jbrines,

        Thanks for your patience.

         

        List.Generate() automates fetching multiple pages of data until all records are retrieved.

         

        Here is an example script that fetches data from your SharePoint list:


        let
        TenantName = "yourtenant.sharepoint.com",
        SiteName = "YourSite",
        ListName = "YourList",
        PageSize = 5000,

        BaseUrl = "https://" & TenantName & "/sites/" & SiteName & "/_api/web/lists/GetByTitle('" & ListName & "')/ItemCount",
        ItemCount = Json.Document(Web.Contents(BaseUrl, [Headers=[Accept="application/json"]]))[value],

        // Function to generate pages
        GetData = List.Generate(
        () => [Skip = 0, Data = {}],
        each [Skip] < ItemCount,
        each [
        Skip = [Skip] + PageSize,
        Data = Json.Document(Web.Contents("https://" & TenantName & "/sites/" & SiteName & "/_api/web/lists/GetByTitle('" & ListName & "')/items?$top=" & Number.ToText(PageSize) & "&$skiptoken=Paged=TRUE%26p_ID=" & Number.ToText([Skip]), [Headers=[Accept="application/json"]]))[value]
        ],
        each [Data]
        ),

        // Convert list to table and expand records
        TableData = Table.FromList(GetData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        ExpandedTable = Table.ExpandListColumn(TableData, "Column1")
        in
        ExpandedTable

        Please try the above script in Advanced Editor and let us know if you face any issues.

         

        If you want a more detailed breakdown of List.Generate(), check out this Microsoft guide: List.Generate - PowerQuery M | Microsoft Learn

        If this helps, please consider accepting it as a solution so others can find it easily.

         

        Thanks,

        Vinay.

    • jbrines's avatar
      jbrines
      Advocate III
      1 year ago

      Hi Greg_Deckler , so basically you would remove the comman and use the & to join it to the base url?

       

      I shoud have said up front this isn't my code I found it online as I have multiple lists and some have more that 5000 lines.

       

       

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        1 year ago

        jbrines I was thinking to change these two lines:

            baseurl = "https://" & tenantname & "/sites/" & sitename & "/_api/web/lists/GetByTitle('" & listname & "')/ItemCount",
    itemcount = Json.Document(Web.Contents(baseurl, [Headers=[Accept="application/json"]]))[value],