How to use the $Skip ODATA expression in a loop?

[jwillis07]

Good afternoon all,

I'm trying to call all of the results within an API that has:

• 6640 total records
• 100 records per page
• 67 pages of results (total records / records per page)
• This is an ever growing list so I've used variables to create the above values.

I can obviously use the $Skip ODATA expression to get any one of the 67 pages by adding the expression to the end of the URL like so (which would skip the first 100, therefore returning the 2nd page:

https://psa.pulseway.com/api/servicedesk/tickets/?$Skip=100

What I'm trying to do though is to create a custom function that will loop through each of the 67 calls, changing the $Skip value by an increment of 100 each time.

I thought I'd accomplished the goal with the below code:

let 
    
    Token = "Token",
    BaseURL = "https://psa.pulseway.com/api/",
    Path = "servicedesk/tickets/",
    RecordsPerPage = 100,
 
    //the below line returns the total number of records (6640) which when divided by the RecordsPerPage (100) results in the number of pages (67)
    CountTickets = Json.Document(Web.Contents(BaseURL,[Headers = [Authorization="Bearer " & Token],RelativePath = Path & "count"])),
    TotalRecords = CountTickets[TotalRecords],

    //the below custom function calls each page using the URL & variable skip from the "GetPage" custom function.
    GetJson = (Url) =>
        let Options = [Headers=[ #"Authorization" = "Bearer " & Token ]],
            RawData = Web.Contents(Url, Options),
            Json = Json.Document(RawData)
        in  Json,

    //The below custom function handles the URL and changing $Skip value.
    GetPage = (Index) =>
        let Skip    = "$Skip=" & Text.From(Index * RecordsPerPage),
            URL     = BaseURL & Path & "?" & Skip,
            Json    = GetJson(URL)
        in  Json,

    TotalPages       = Number.RoundUp(TotalRecords / RecordsPerPage),
    PageIndicies    = {0.. TotalPages - 1},
    Pages           = List.Transform(PageIndicies, each GetPage(_))
in
    Pages

I got all happy when it successfully made the 67 API calls, and combined the results into a list for me to load in, however what I'm actually seeing is the first 100 records repeated 67 times.

That tells me that my GetPage custom function which handles the $Skip value isn't changing and is stuck on the first one. To make sure the Skip index was generating them properly I duplicated the query and changed the code to load in the $Skip values and see what they are, expecting them all to be $Skip=0, what I see though is the correct $Skip values as below:

 

It seems everything is working as it should be, only I'm only getting the first page 67 times.

I've made a couple of posts around this issue before but I realise the problem I was (poorly) describing was far too broad to get any meaningful assistance. I think now I've gotten to the point where I understand what my own code is doing and have really zoned in to the problem - I just don't know how to fix it when I'm at the final hurdle...

Any help/advice would be massively appreciated. Thank you.

[mahoneypat]

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

[jwillis07]

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

[mahoneypat]

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

[jwillis07]

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!

[jwillis07]

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.

[mahoneypat · Accepted solution]

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

[jwillis07]

Hi Pat, thank you for your response. I've watched the video - Excellently narrated and if I understand it right, it seems a very efficient way of doing this - So I've come up with my version of the code as below:

let 
    
    Token           = "Token",
    BaseURL         = "https://psa.pulseway.com/api/",
    Path            = "servicedesk/tickets/",
    RecordsPerPage  = 100,
 
        //Count the total number of records
        CountRecords = Json.Document(Web.Contents(BaseURL, [Headers = [Authorization="Bearer " & Token], RelativePath = Path & "count"])),
        TotalRecords = CountRecords[TotalRecords],

        //Create table of calls
        ListCalls   = List.Numbers(0, TotalRecords/RecordsPerPage, RecordsPerPage),
        TableCalls  = Table.FromList(ListCalls, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        ColumnType  = Table.TransformColumnTypes(TableCalls,{{"Column1", type text}}),
        AddColumn   = Table.AddColumn(ColumnType, "Custom", each Json.Document(Web.Contents(BaseURL, [Headers = [Authorization = "Bearer " & Token], RelativePath =  Path & "?" & "$Skip=" [Column1]])))

in

    AddColumn

This produces a table of 67 rows for each of the pages I will be calling and correctly increases in increments of 100 which is the total records per page that the API I'm using returns.

It turns that column to text, adds another, but then fails at either creating the URL per line, or returning the JSON. Not sure which. This is what the code results in:

 

Any tips on where I could be going wrong? Is perhaps that I'm trying to pass the authorization headers?

Thank you.