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.

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

     

    • jbrines's avatar
      jbrines
      Icon for Advocate III rankAdvocate III
      1 year ago

      Hi Anonymous have you had a chance to see my last reply?

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi jbrines,
        Apologies for the delayed response, and thank you for your patience.

        Hope your issue is resolved. If not, please consider the following.

         

        The behavior where only 5001 out of 5295 items are returned suggests that the $skiptoken logic may not be advancing correctly. This often occurs when the p_ID used in the token doesn’t reference the actual last item ID from the previous page.

         

        To ensure accurate pagination, the script needs to dynamically capture the last item’s ID from each page and use that as the starting point for the next. The earlier logic used a precomputed skip value, which isn’t compatible with SharePoint’s paging mechanism.

         

        I'll validate a revised version of the script using List.Generate() with ID-based pagination and share a working solution after testing with a large dataset.

        Thanks again for following up.

        Best regards,

        Vinay.