Forum Discussion

jbrines's avatar
jbrines
Icon for Advocate III rankAdvocate 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 becau...
Need Help
  • Anonymous's avatar
    Anonymous
    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.

Anonymous's avatar
Anonymous
Not applicable
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
    Icon for Advocate III rankAdvocate III
    1 year ago

    Hi Anonymous ,

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      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
        Icon for Advocate III rankAdvocate III
        1 year ago

        Hi Anonymous ,

         

        Tried it on a site that has 5295 items and it is only showing 5001. What needs to be done to get the rest?

         

        Thanks