Forum Discussion
How to use the $Skip ODATA expression in a loop?
A simpler approach is shown in this article/video - Power BI - Tales From The Front - REST APIs - YouTube
You can adapt that approach. Basically, you create a list of number that increment by 100 with List.Numbers, convert that to a table, make your number column a text column and then concatenate the number into your URL on each row. You then expand the column of "Table"s to get your result.
Pat
Two things I see that might be the problem.
1. You probably want to use the Query part of the Web.Contents function and not RelativePath. I think this will auto add the ? so probably not needed again.
2. You need an & between "$Skip=" & [Column1]
Try #2 first and then #1 if still not working.
Pat
Hi Pat, thank you for the feedback. I've tried it both:
AddColumn = Table.AddColumn(ColumnType, "Custom", each Json.Document(Web.Contents(BaseURL, [Headers = [Authorization = "Bearer " & Token], RelativePath = Path & "?" & "$Skip=" [Column1]])))
and by creating a FullURL of BaseURL + Path and dropping the RelativePath from the Webcontents completely. Although I'm not sure if I've used Query correctly? Should it still not include the $ - doesn't seem to like that though:
AddColumn = Table.AddColumn(ColumnType, "Custom", each Json.Document(Web.Contents(FullURL, [Headers = [Authorization = "Bearer " & Token], Query = [Skip = [Column1]]])))
Both ways now produce the table with the added column of JSON records, however every call is just the same first 100 records which is back to the problem I had at the beginning.
I don't get it, no matter how many different ways I've tried this now, I can't seem to get anything but the first 100 results 67 times (even though the skip values go up in the correct increments). It's like it's just completely ignoring the Skip parameter even though the API documentation says it supports ODATA expressions:
FILTER, SORT AND PAGING EXPRESSIONS
When multiple records are returned by a GET request, Odata expressions can be appended to the end of the URI. The three types of expressions determine whether the multiple records are filtered, sorted, or paged. Documentation identifies whether a field is filterable or sortable. Paging expressions are always available for any GET method that returns multiple records. Multiple Odata expressions can be combined in the same URI. In the following example, the first Odata expression is delimited by a question (?) character. Subsequent Odata expressions are delimited with an ampersand (&) character.
GET /api/accounts?$skip=30&$top=10&$orderby=Name
Just tested the Skip expression by hardcoding it like so:
Source = Json.Document(Web.Contents(url,[ Headers = [Authorization="Bearer " & AuthKey] ,RelativePath = "servicedesk/tickets?$Skip=200"]))And that works fine. I just can't seem to get it to loop.
Your add column step is still missing the & before [Column1]. Also make sure [Column1] is type text (or convert it to text in this expression).
AddColumn = Table.AddColumn(ColumnType, "Custom", each Json.Document(Web.Contents(BaseURL, [Headers = [Authorization = "Bearer " & Token], RelativePath = Path & "?" & "$Skip=" & [Column1]])))Pat
Sorry Pat, my bad. I did have the & between "?$Skip=" and [Column1] - Must have copied some old code accross.
I have found the answer though in that it's case sensitive and should have been "?$skip=" - A capital S caused me that much grief.
But I love your solution for paginating the results so definitely using that one going forward, thank you very much!