Forum Discussion
Advocate IIIHelp with function query that won't work with scheduled refresh
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.
Advocate IIIHi 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.
Community Championjbrines 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
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"- Greg_Deckler
jbrines Looks like the first part you got right but this line needs to be this instead:
itemcount = Json.Document(Web.Contents(baseurl, [Headers=[Accept="application/json"]]))[value],In other words, you don't want to add anything to the url that you are feeding to Web.Contents. The url (baseurl in your case) should be fully formed prior to feeding it to Web.Contents.
- jbrines
Hi Greg_Deckler ,
You are getting there. Now once I make the changes the other queries come up withthe following error.
DataSource.Error: Web.Contents failed to get contents from 'https://clarkcontracts.sharepoint.com/sites/CE4077-SilverburnParkFlaxMillLeven/_api/web/lists/GetByTitle('Site%20Sign%20In')/ItemCount/items?$skipToken=Paged=TRUE%26p_ID=0&$top=5000' (404): Not Found Details: DataSourceKind=Web DataSourcePath=https://clarkcontracts.sharepoint.com/sites/CE4077-SilverburnParkFlaxMillLeven/_api/web/lists/GetByTitle('Site%20Sign%20In')/ItemCount/items Url=https://clarkcontracts.sharepoint.com/sites/CE4077-SilverburnParkFlaxMillLeven/_api/web/lists/GetByTitle('Site%20Sign%20In')/ItemCount/items?$skipToken=Paged=TRUE%26p_ID=0&$top=5000- Greg_Deckler
jbrines OK, that is a 404 not found error so there is something wrong with the URL. So, you'll have to figure out what the correct URL is for what you want and then make sure that you construct it completely and accurately before turning Web.Contents loose on it. As long as you don't have any "dynamic" components to the URL you use in Web.Contents (use the query options if you need to feed in query string parameters) then you shouldn't get the error where it does not allow you to refresh the source.
- jbrines
Hi Greg_Deckler
This is the correct URL
https://clarkcontracts.sharepoint.com/sites/CE4077-SilverburnParkFlaxMillLeven/Lists/Site%20Sign%20In/AllItems.aspxThe issue is that some of the lists have more that 5000 (limit) and that is why I was using that function query.
I suppose if there was a way to change the query so it didn't need the function query then I could use the query
let Source = Query1("clarkcontracts.sharepoint.com", "CE4077-SilverburnParkFlaxMillLeven", "Site Sign In"), #"Expanded value" = Table.ExpandRecordColumn(Source, "value", {"Title", "Modified", "Created", "Company", "SignInDateandTime", "SignOutDateandTime", "Profession", "AreyouaDriver_x002f_Passenger", "PostcodeStart", "ProjectPostcode", "Distance_x0028_includingreturnjo", "SignInLocation", "SignOutLocation", "Contract", "ModeofTransport", "Created_x0020_Date0", "Created_x0020_Time", "Modified_x0020_Time", "Sign_x0020_Out_x0020_Time", "Hours", "AuthorId", "EditorId"}, {"value.Title", "value.Modified", "value.Created", "value.Company", "value.SignInDateandTime", "value.SignOutDateandTime", "value.Profession", "value.AreyouaDriver_x002f_Passenger", "value.PostcodeStart", "value.ProjectPostcode", "value.Distance_x0028_includingreturnjo", "value.SignInLocation", "value.SignOutLocation", "value.Contract", "value.ModeofTransport", "value.Created_x0020_Date0", "value.Created_x0020_Time", "value.Modified_x0020_Time", "value.Sign_x0020_Out_x0020_Time", "value.Hours", "value.AuthorId", "value.EditorId"}), #"Trimmed Text" = Table.TransformColumns(Table.TransformColumnTypes(#"Expanded value", {{"value.Company", type text}}, "en-GB"),{{"value.Title", Text.Proper, type text}}), #"Replaced Value" = Table.ReplaceValue(#"Trimmed Text",null,"CE4077",Replacer.ReplaceValue,{"value.Contract"}), #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"value.Modified", type datetime}, {"value.Created", type datetime}, {"value.SignInDateandTime", type datetime}, {"value.SignOutDateandTime", type datetime}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"value.Title", "Name"}, {"value.Modified", "Modified"}, {"value.Created", "Created"}, {"value.Company", "Company"}, {"value.SignInDateandTime", "Sign In Date and Time"}, {"value.SignOutDateandTime", "Sign Out Date and Time"}, {"value.Profession", "Profession"}, {"value.SignInLocation", "Sign In Location"}, {"value.SignOutLocation", "Sign Out Location"}, {"value.ModeofTransport", "Mode of Transport"}, {"value.AreyouaDriver_x002f_Passenger", "Are you a Driver or Passenger"}, {"value.PostcodeStart", "Postcode Start"}, {"value.ProjectPostcode", "Project Postcode"}, {"value.Distance_x0028_includingreturnjo", "Distance includingr eturn journey"}, {"value.Contract", "Contract"}}), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Skip"}), #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Distance includingr eturn journey", "Distance including return journey"}, {"value.Created_x0020_Date0", "Created Date"}, {"value.Created_x0020_Time", "Created Time"}, {"value.Modified_x0020_Time", "Modified Time"}, {"value.Sign_x0020_Out_x0020_Time", "Sign Out Time"}, {"value.Hours", "Hours"}}), #"Replaced Value1" = Table.ReplaceValue(#"Renamed Columns1","Clark contracts","Clark Contracts",Replacer.ReplaceValue,{"Company"}), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","CCL-Search","Search",Replacer.ReplaceValue,{"Company"}), #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Ccl","Clark Contracts",Replacer.ReplaceValue,{"Company"}), #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","Clarks ","Clark Contracts",Replacer.ReplaceValue,{"Company"}), #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","Clark contracts ","Clark Contracts",Replacer.ReplaceValue,{"Company"}), #"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","Clark Contracts Ltd ","Clark Contracts",Replacer.ReplaceValue,{"Company"}), #"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","Clark Contracts Ltd","Clark Contracts",Replacer.ReplaceValue,{"Company"}), #"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","Clark Contracts ","Clark Contracts",Replacer.ReplaceValue,{"Company"}), #"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","CCL","Clark Contracts",Replacer.ReplaceValue,{"Company"}) in #"Replaced Value9"Any ideas on how o change the cose below so it brings in all the items from the list but doesn't need the Function Query.