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 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
      Icon for Community Champion rankCommunity 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],
      • jbrines's avatar
        jbrines
        Icon for Advocate III rankAdvocate III
        1 year ago

        Hi Greg_Deckler ,

         

        Changed it to this and still getting the error, appologies if this isn't what you were suggesting.

         

        (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",
    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"