Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jojo197
Frequent Visitor

Sharepoint Link api skipToken not working

I have the following Query to get data from a sharepoint list. Currently all the values are manual and I am only viewing and skipping a small number of rows. 

 

let
    Source = Json.Document(Web.Contents("http://<tenant>.sharepoint.com/sites/<directory>/_api/web/lists/GetByTitle('<name>')/items?$skiptoken=Paged=TRUE%26p_ID=5&$top=10&$select=Id",[Headers=[Accept="application/json"]])),
    value = Source[value],
    #"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Id"},{"ID"})
in
    #"Expanded Column1"

 

When changing skiptoken=Paged=TRUE%26p_ID= between 0 and 5, nothing changes in the output; it still shows the first 10 rows of data. I found this code on various websites and am fairly certain I have copied it exactly so I am unsure why the skiptoken part is not working.

 

Thanks in advance

 

The solution in this page contains the video to the code I am using:

https://community.powerbi.com/t5/Desktop/SharePoint-list-query-alternative-or-optimization/m-p/15751...

 

 

 

1 ACCEPTED SOLUTION
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

Hi @Jojo197 - Please use the Power Query SharePoint Online list connector - Power Query | Microsoft Learn connector instead.  I also recomend using Implemention="2.0" for the best performance and features.

 

View solution in original post

4 REPLIES 4
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

Hi @Jojo197 - Please use the Power Query SharePoint Online list connector - Power Query | Microsoft Learn connector instead.  I also recomend using Implemention="2.0" for the best performance and features.

 

Thank you, this did what I needed. Is there a reason why the skip token wasn't working with what I was doing?

Web.Contents does not have easy mechanism to capture the User Credentials to generate a token. You would need to develop a custom connector or a function to call for the token. 

 

Instead, you could have switched to using the OData.Feed function instead of web contents.  This will prompt for user credentials. 

 

But I would not recommend because the new SharePoint.Tables approach is optimised for better performance and would require less additional transformations (e.g. renaming and data type conversions).

@Daryl-Lynch-Bzy 

 

I tried the Web.Contents approach because the 2.0 implementation of the SharePoint connector is giving me a 500 internal server error. I have a list where the 2.0 implementation broke when the list went over 5000 items. I have other SP lists over 20000 items, and the 2.0 implementation is working fine. Any ideas? Hair is falling out over here.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors