serviceNOw Paginated API in power Query-only getting the first page data

[antara_centri]

REST API CODE IN ServiceNow

(function process(/*RESTAPIRequest*/ request, /*RESTAPIResponse*/ response) {

    // Parse limit and page from query parameters, default to 10 and 1 if not provided

    var limit = request.queryParams.limit ? parseInt(request.queryParams.limit) : 10;

    var page = request.queryParams.page ? parseInt(request.queryParams.page) : 1;

    // Validate limit and page to ensure they are valid numbers

    if (isNaN(limit) || limit < 1) {

        limit = 10; // Default limit to 10 if not provided or invalid

    }

    if (isNaN(page) || page < 1) {

        page = 1; // Default page to 1 if not provided or invalid

    }

    // Separate GlideAggregate instance to get the total record count

    var grAgg = new GlideAggregate('u_db_view_task_task_sla');

    grAgg.addQuery('task_opened_at', '>=', '2022-01-01 00:00:00');

    grAgg.addQuery('task_opened_at', '<=', new GlideDateTime());

    grAgg.addAggregate('COUNT');

    grAgg.query();

    var totalRecords = 0;

    if (grAgg.next()) {

        totalRecords = parseInt(grAgg.getAggregate('COUNT'), 10);

    }

    // Calculate total pages

    var totalPages = Math.ceil(totalRecords / limit);

    if (page > totalPages) {

        page = totalPages;

    }

    // Use a different GlideRecord instance to fetch the actual data with limits

    var gr = new GlideRecord('u_db_view_task_task_sla');

    gr.addQuery('task_opened_at', '>=', '2022-01-01 00:00:00');

    gr.addQuery('task_opened_at', '<=', new GlideDateTime());

    gr.orderBy('task_number'); // Ensure ascending order

    gr.setLimit(limit);

    gr.setOffset((page - 1) * limit);

    gr.query();

    var result = [];

    var uniqueKeyOffset = (page - 1) * limit + 1; // Adjust unique key based on page and limit

    while (gr.next()) {

        var record = {

            UniqueKey: uniqueKeyOffset++, // UniqueKey incrementing based on page and limit

            Ticket_Number: gr.getValue('task_number'),

            Ticket_Type: gr.getValue('task_sys_class_name'),

            Company: gr.getValue('task_company'),

            Priority: gr.getValue('task_priority'),

            Created_Date: gr.getValue('task_sys_created_on'),

            Closed: gr.getValue('cs_closed_at'),

            Ticket_Description: gr.getValue('task_description'),

            Ticket_short_description: gr.getValue('task_short_description'),

            Sla_definition: gr.getValue('sla_definition'),

            Ticket_business_duration: gr.getValue('task_business_duration'),

            Ticket_State: gr.getValue('task_state'),

            Category: gr.getValue('Category'),

            Subcategory: gr.getValue('Subcategory'),

            Practice_Area: gr.getValue('Practice_Area')

        };

        result.push(record);

    }

    var responseBody = {

        totalRecords: totalRecords,

        limit: limit,

        page: page,

        totalPages: totalPages,

        records: result

    };

    response.setBody(responseBody);

})(request, response);

REST API Response in SNOW

In Power BI creating a function getPage and table totalPages.

M code for getPage:

Here is the code formatted for easy copy-pasting in a forum:

```m

let

    // Define the source URL

    SourceURL = "https://watservtest.service-now.com/api/ws/test_pbi/read_dbview",

    // Fetch the initial response

    InitialResponse = Json.Document(Web.Contents(SourceURL, [Query=[limit="10"]])),

    // Extract the result from the initial response

    Result = InitialResponse[result],

    // Extract the result from the initial response

    records = Result[records],

    #"Converted to Table" = Table.FromList(records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"UniqueKey", "Ticket_Number", "Ticket_Type", "Company", "Priority", "Created_Date", "Closed", "Ticket_Description", "Ticket_short_description", "Sla_definition", "Ticket_business_duration", "Ticket_State", "Category", "Subcategory", "Practice_Area"}, {"Column1.UniqueKey", "Column1.Ticket_Number", "Column1.Ticket_Type", "Column1.Company", "Column1.Priority", "Column1.Created_Date", "Column1.Closed", "Column1.Ticket_Description", "Column1.Ticket_short_description", "Column1.Sla_definition", "Column1.Ticket_business_duration", "Column1.Ticket_State", "Column1.Category", "Column1.Subcategory", "Column1.Practice_Area"})

in

    #"Expanded Column1"

```

M code for totalPages:

let

    // Define the source URL

    SourceURL = " https://watservtest.service-now.com/api/ws/test_pbi/read_dbview",

    // Fetch the initial response

    InitialResponse = Json.Document(Web.Contents(SourceURL, [Query=[limit="10"]])),

    // Extract the result from the initial response

    Result = InitialResponse[result],

    // Extract the result from the initial response

    List = {1..Result[totalPages]},

    #"Converted to Table" = Table.FromList(List, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),

    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Data1", each getPage([Column1]))

in

    #"Invoked Custom Function"

I am getting correct data as below.limit-it is total records per page

Below column1 column is the page number and the Data1 column contains data  per page.So for the first record in column1 (1) if we click on the Table in Data1 column we get 10 records of page 1.

But the problem is, that every page contains the records of the first page.How to get the records of each page in the tables and then combine in a single table.

[v-heq-msft]

Hi @antara_centri ,
According to your description, each page contains the first page of records. To solve this problem, we need to adjust the getPage function to ensure that the page number query parameter is set correctly when requesting each page of data.
You can try modifying the following function
First, update the getPage function to include the page number query parameter

let
    getPage = (page as number) =>
    let
        SourceURL = "https://watservtest.service-now.com/api/ws/test_pbi/read_dbview",
        // Add the page number to the query parameters
        QueryParams = [limit="10", page=Text.From(page)],
        Response = Json.Document(Web.Contents(SourceURL, [Query=QueryParams])),
        Result = Response[result],
        Records = Result[records],
        #"Converted to Table" = Table.FromList(Records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", 
            {"UniqueKey", "Ticket_Number", "Ticket_Type", "Company", "Priority", "Created_Date", "Closed", 
            "Ticket_Description", "Ticket_short_description", "Sla_definition", "Ticket_business_duration", 
            "Ticket_State", "Category", "Subcategory", "Practice_Area"},
            {"Column1.UniqueKey", "Column1.Ticket_Number", "Column1.Ticket_Type", "Column1.Company", 
            "Column1.Priority", "Column1.Created_Date", "Column1.Closed", "Column1.Ticket_Description", 
            "Column1.Ticket_short_description", "Column1.Sla_definition", "Column1.Ticket_business_duration", 
            "Column1.Ticket_State", "Column1.Category", "Column1.Subcategory", "Column1.Practice_Area"})
    in
        #"Expanded Column1"
in
    getPage

Then, modify the totalPages code to call this custom function and merge the data from all pages:

let
    SourceURL = "https://watservtest.service-now.com/api/ws/test_pbi/read_dbview",
    InitialResponse = Json.Document(Web.Contents(SourceURL, [Query=[limit="10"]])),
    Result = InitialResponse[result],
    TotalPages = Result[totalPages],
    ListOfPages = List.Numbers(1, TotalPages),
    #"Converted to Table" = Table.FromList(ListOfPages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Invoked Custom Function" = Table.AddColumn(#"Converted to Table", "Data1", each getPage([Column1])),
    CombinedData = Table.ExpandTableColumn(#"Invoked Custom Function", "Data1", {"UniqueKey", "Ticket_Number", "Ticket_Type", "Company", "Priority", "Created_Date", "Closed", "Ticket_Description", "Ticket_short_description", "Sla_definition", "Ticket_business_duration", "Ticket_State", "Category", "Subcategory", "Practice_Area"}, {"UniqueKey", "Ticket_Number", "Ticket_Type", "Company", "Priority", "Created_Date", "Closed", "Ticket_Description", "Ticket_short_description", "Sla_definition", "Ticket_business_duration", "Ticket_State", "Category", "Subcategory", "Practice_Area"})
in
    CombinedData

The purpose of this code is to create a table for each page and combine the data from all pages into a single table.

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly