Yet another: "Can't refresh REST API with pagination query"

Hi Yxalitis 

Sure, here you go. I've also formatted the code with Power Query Formatter.

let

  // Get parameters from the query editor
  BaseUrl = #"Base URL",  // Referencing the Base URL parameter
  Token = #"Bearer Token",  // Referencing the Bearer Token parameter
  PageSize = #"Page Size",  // Referencing the Page Size parameter
  MaxPages = #"Max Pages",  // Referencing the Max Pages parameter
  // Function to get data from ServiceNow with pagination
  GetData = (pageNum as number) =>
    let
      // Build the URL dynamically for each page
      Offset = (pageNum - 1) * PageSize,
      // Use the Base URL for Web.Contents (hopefully resolves refresh issue)
      Url = BaseUrl,
      // Set up the query parameters in a record with one field per parameter
      Query = [sysparm_limit = Text.From(PageSize), sysparm_offset = Text.From(Offset)],
      // Set up the headers (Authorization using Bearer Token)
      Headers = [#"Authorization" = "Bearer " & Token],
      // Fetch the data from the API
      Response = Json.Document(Web.Contents(Url, [Headers = Headers, Query = Query])),
      // Access the 'result' from the response (adjust based on your API response structure)
      Result = Response[result],  // Assuming 'result' holds the list of records
      // Convert the List into a Table
      ResultTable = Table.FromList(Result, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    in
      ResultTable,
  // Retrieve data from multiple pages
  GetAllData = List.Transform({1 .. MaxPages}, each GetData(_)),
  // Combine the results into a single table
  CombinedData = Table.Combine(GetAllData)
in
  CombinedData

Here is a rough summary of the code:

1. Grabbing some parameters up front (BaseUrl to MaxPages).
2. Defining a function GetData which
   1. Takes an argument page number (pageNum) and converts that into an Offset.
   2. It then queries the API for that page number using Web.Contents, with headers and query parameters passed in the 2nd argument (Response).
      (Json.Document interprets the API response as JSON and converts it into a record format.)
   3. Result takes the result field from the record Response.
   4. ResultTable converts to a table.
3. GetAllData calls that function for the required number of pages
4. CombineData Combines the results into a single table.

Hope this helps!

Glad to hear it 🙂

Just FYI, if those additional filters are query parameters within the URL in this format:

?param1=value1&param2=value2&param3=value3...

then you have the option of adding them to the Query record.

Based on the values I think I saw in your earlier post, it would be this kind of structure:

Query =
[
  sysparm_limit         = Text.From(PageSize),
  sysparm_offset        = Text.From(Offset),
  sysparm_query         = "opened_atRELATIVEGT@month@ago@24",
  sysparm_display_value = "True",
  sysparm_fields        = "number%2Csys_created_on%2Csys_created_by%2Csys_updated_on%2Cresolved_at"
],