Paginate api dinamic next page

[marcio_fornari]

Hello,

I need iterate over dynamic api pages. The Pages is not number like.. page 1, 2..3.. The pages is a string. When i read the page, the api returns one string with the Next Page.

This function returns my first page and one column with "next page":

let
    url = "https://api.intercom.io/tickets/search",
    headers = [
        Authorization = "Bearer dG9rOjJkNzA2NTk4X2JjNjdfNDafddafdafdafd",
        #"Content-Type" = "application/json", 
        #"Accept" = "application/json",
        #"Intercom-Version" = "Unstable"
    ],
    postData = "{
        ""query"": {
            ""value"": ""7"",
            ""operator"": ""="",
            ""field"": ""ticket_type_id""
        },
         ""pagination"": {
            ""per_page"": ""100""
        }
    }",
    response = Web.Contents(
        url,
        [
            Headers = headers,
            Content = Text.ToBinary(postData)
        ]
    ),
    jsonResponse = Json.Document(response),
    pages = jsonResponse[pages],
    total_count = jsonResponse[total_count],
    tickets = jsonResponse[tickets],
    combined = Table.FromRecords({
        [
            pages = pages,
            total_count = total_count,
            tickets = tickets
        ]
    }),
    #"pages Expandido" = Table.ExpandRecordColumn(combined, "pages", {"type", "next", "page", "total_pages"}, {"pages.type", "pages.next", "pages.page", "pages.total_pages"}),
    #"pages.next Expandido" = Table.ExpandRecordColumn(#"pages Expandido", "pages.next", {"page", "starting_after"}, {"pages.next.page", "pages.next.starting_after"}),
    #"tickets Expandido" = Table.ExpandListColumn(#"pages.next Expandido", "tickets"),
    #"tickets Expandido1" = Table.ExpandRecordColumn(#"tickets Expandido", "tickets", {"type", "id", "ticket_id", "ticket_attributes", "ticket_state", "ticket_type", "contacts", "admin_assignee_id", "team_assignee_id", "created_at", "updated_at", "ticket_parts", "open", "snoozed_until", "linked_objects", "category", "is_shared"}, {"tickets.type", "tickets.id", "tickets.ticket_id", "tickets.ticket_attributes", "tickets.ticket_state", "tickets.ticket_type", "tickets.contacts", "tickets.admin_assignee_id", "tickets.team_assignee_id", "tickets.created_at", "tickets.updated_at", "tickets.ticket_parts", "tickets.open", "tickets.snoozed_until", "tickets.linked_objects", "tickets.category", "tickets.is_shared"})
in
    #"tickets Expandido1"

the next page is the column starting_after, i repeated for each line.

i created one function with get a single page, but i don't know how loop with this.

GetSinglePage:

(nextpage as text)=>
let
    url = "https://api.intercom.io/tickets/search",
    headers = [
        Authorization = "Bearer dG9rOjJkNzA2NTk4X2JjNjdfNDRiNV9hZjRiX2NlZWQwMzNlNGI2NDoxOjA=",
        #"Content-Type" = "application/json", 
        #"Accept" = "application/json",
        #"Intercom-Version" = "Unstable"
    ],
    postData = "{
        ""query"": {
            ""value"": ""7"",
            ""operator"": ""="",
            ""field"": ""ticket_type_id""
        },
         ""pagination"": {
            ""per_page"": ""100"",
             ""starting_after"": """ & nextpage & """
        }
    }",
    response = Web.Contents(
            url,
            [
                Headers = headers,
                Content = Text.ToBinary(postData)
            ]
        ),
    jsonResponse = Json.Document(response),
    pages = jsonResponse[pages],
    total_count = jsonResponse[total_count],
    tickets = jsonResponse[tickets],
    combined = Table.FromRecords({
        [
            pages = pages,
            total_count = total_count,
            tickets = tickets
        ]
    }),
    #"pages Expandido" = Table.ExpandRecordColumn(combined, "pages", {"type", "next", "page", "total_pages"}, {"pages.type", "pages.next", "pages.page", "pages.total_pages"}),
    #"pages.next Expandido" = Table.ExpandRecordColumn(#"pages Expandido", "pages.next", {"page", "starting_after"}, {"pages.next.page", "pages.next.starting_after"}),
    #"tickets Expandido" = Table.ExpandListColumn(#"pages.next Expandido", "tickets"),
    #"tickets Expandido1" = Table.ExpandRecordColumn(#"tickets Expandido", "tickets", {"type", "id", "ticket_id", "ticket_attributes", "ticket_state", "ticket_type", "contacts", "admin_assignee_id", "team_assignee_id", "created_at", "updated_at", "ticket_parts", "open", "snoozed_until", "linked_objects", "category", "is_shared"}, {"tickets.type", "tickets.id", "tickets.ticket_id", "tickets.ticket_attributes", "tickets.ticket_state", "tickets.ticket_type", "tickets.contacts", "tickets.admin_assignee_id", "tickets.team_assignee_id", "tickets.created_at", "tickets.updated_at", "tickets.ticket_parts", "tickets.open", "tickets.snoozed_until", "tickets.linked_objects", "tickets.category", "tickets.is_shared"})
in
    #"tickets Expandido1"

[lbendlin]

There's a convenience function for this.

Handling paging for Power Query connectors - Power Query | Microsoft Learn