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

[Yxalitis]

Sample code to get data from Service Now 

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,
Url = BaseUrl & "?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])),

// 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

This successfully retrieves the tables, but can't refesh online:

This dataset includes a dynamic data source.

I have tried a dozen iterations, they all end up with this error. 

I think I am close, any help appreciated! 

[OwenAuger · Accepted solution]

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!

[OwenAuger]

Hi @Yxalitis 

The first thing that jumps out at me is that the query parameters should be moved to the options record, alongside Headers (similar to described here). This ensures that the first argument of Web.Contents doesn't change.

Here is how I would try writing the steps from Url to Response:

  Url = BaseUrl,
  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]
      )
    ),

You could also look at providing RelativePath as another field in the record passed to Web.Contents (see the post linked above for an example), though this may not be required in this case.

Does refresh in the Power BI Service work using this type of setup?

[Yxalitis]

Hi, thanks very much for your help!

OK, I updated my query to use your code, and it WORKS!!

[OwenAuger · Accepted solution]

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!

[Yxalitis]

This works amazing, but dumps the entire table.

I added the filters to teh end of the baseURL, and that worked!

Thanks so much! 

[OwenAuger · Accepted solution]

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"
],

[Yxalitis]

I have to say your solution is the neatest I've seen, just so clean and logical!