Pagination code returns first page forever instead of properly paginating

Hi,

I won't go into many details, but one of the issues is that you should be using previous page ends_with as the next page starts_with -> I haven't seen you using ends_with anywhere in your code.

I'm not sure what is the relevant link for the API documentation (?) - I had a quick look at https://learn.apptentive.com/knowledge-base/apptentive-api-beta/#paging

BUT I could not see there starts_with - instead I saw starts_after.

Anyway, please give the below code a try - it's based on your code, I have NOT tested it:

(app_id as text, survey_id as text) =>
    let
        getNextPage = (lastPage) =>
            let
                getPage = (starts_with) =>
                    let
                        query =
                            if starts_with = null then
                                [page_size = "100", order = "asc"]
                            else
                                [page_size = "100", order = "asc", starts_with = starts_with],
                        result = Json.Document(
                            Web.Contents(
                                "https://data.apptentive.com/raw/v2/apps/",
                                [
                                    Headers = [
                                        Accept = "application/json",
                                        #"x-api-key" = "MY-KEY-HERE"
                                    ],
                                    RelativePath = (app_id) & "/surveys/" & (survey_id) & "/responses",
                                    Query = query
                                ]
                            )
                        )
                    in
                        result,
                nextPage =
                    if lastPage = null then
                        getPage(null)
                    else if lastPage[has_more] = true then
                        getPage(lastPage[ends_with])
                    else
                        null
            in
                nextPage,
        pages = List.Generate(
            // initial
            () => getNextPage(null),
            // condition
            (lastPage) => lastPage <> null,
            //next
            (lastPage) => getNextPage(lastPage)
        )
    in
        pages

If the above code does NOT work, please reply AND also please give us the relevant link to the API documentation.

Finally, don't forget to mark this reply as answer if it helped.

Hey Everyone ! I am currently delving into the world of connecting Power BI to Shopify using its API. As I'm relatively new to APIs, I have been experimenting with pagination in my Power Query. However, I am encountering a bit of a snag – I can only seem to retrieve 250 records at a time.

According to the Shopify Docs, it appears that I can only fetch a maximum of 250 records per request. However, I need to retrieve all records, and I'm uncertain about the total number of records or pages available.

Could you lend me a hand in resolving this issue? 
I am currently using the following query to retrieve all records, but I am stuck at retrieving only 250 records:.

let
GetOrders = (url as text) =>
let

response = Web.Contents(url),

json = Json.Document(response),

orders = json[orders]
in

orders,
GetAllOrders = (previousPageUrl as text, nextPageUrl as text) =>
let

allOrders = {},
GetPageOrders = (pageUrl as text) =>
let

orders = GetOrders(pageUrl),

nextPageHeader = try Record.Field(Web.Contents(pageUrl, [Headers=[#"Accept"="application/json"]]), "Link") otherwise null,
nextPageUrl = if nextPageHeader <> null then Text.BetweenDelimiters(nextPageHeader, "<", ">; rel=""next""") else null
in

if nextPageUrl <> null then
let
nextOrders = GetPageOrders(nextPageUrl)
in
List.Combine({orders, nextOrders})
else
orders
in

GetPageOrders(previousPageUrl),

previousPageUrl = "https://prostandard.myshopify.com/admin/api/2024-01/orders.json?limit=250&page_info=eyJkaXJlY3Rpb24iOiJwcmV2IiwibGFzdF9pZCI6NTU3OTAwNTc4ODI1OSwibGFzdF92YWx1ZSI6IjIwMjQtMDMtMDcgMTE6NDQ6NDAuMjg5NDE0In0",
nextPageUrl = "https://prostandard.myshopify.com/admin/api/2024-01/orders.json?limit=250&page_info=eyJkaXJlY3Rpb24iOiJuZXh0IiwibGFzdF9pZCI6NTU3MDQ0NTA4MjcyMywibGFzdF92YWx1ZSI6IjIwMjQtMDItMjkgMTk6NDc6MzkuNTQ1Njg0In0",

allOrders = GetAllOrders(previousPageUrl, nextPageUrl),
#"Converted to Table" = Table.FromList(allOrders, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "admin_graphql_api_id", "app_id", "browser_ip", "buyer_accepts_marketing", "cancel_reason", "cancelled_at", "cart_token", "checkout_id", "checkout_token", "client_details", "closed_at", "company", "confirmation_number", "confirmed", "contact_email", "created_at", "currency", "current_subtotal_price", "current_subtotal_price_set", "current_total_additional_fees_set", "current_total_discounts", "current_total_discounts_set", "current_total_duties_set", "current_total_price", "current_total_price_set", "current_total_tax", "current_total_tax_set", "customer_locale", "device_id", "discount_codes", "email", "estimated_taxes", "financial_status", "fulfillment_status", "landing_site", "landing_site_ref", "location_id", "merchant_of_record_app_id", "name", "note", "note_attributes", "number", "order_number", "order_status_url", "original_total_additional_fees_set", "original_total_duties_set", "payment_gateway_names", "phone", "po_number", "presentment_currency", "processed_at", "reference", "referring_site", "source_identifier", "source_name", "source_url", "subtotal_price", "subtotal_price_set", "tags", "tax_exempt", "tax_lines", "taxes_included", "test", "token", "total_discounts", "total_discounts_set", "total_line_items_price", "total_line_items_price_set", "total_outstanding", "total_price", "total_price_set", "total_shipping_price_set", "total_tax", "total_tax_set", "total_tip_received", "total_weight", "updated_at", "user_id", "billing_address", "customer", "discount_applications", "fulfillments", "line_items", "payment_terms", "refunds", "shipping_address", "shipping_lines"}, {"id", "admin_graphql_api_id", "app_id", "browser_ip", "buyer_accepts_marketing", "cancel_reason", "cancelled_at", "cart_token", "checkout_id", "checkout_token", "client_details", "closed_at", "company", "confirmation_number", "confirmed", "contact_email", "created_at", "currency", "current_subtotal_price", "current_subtotal_price_set", "current_total_additional_fees_set", "current_total_discounts", "current_total_discounts_set", "current_total_duties_set", "current_total_price", "current_total_price_set", "current_total_tax", "current_total_tax_set", "customer_locale", "device_id", "discount_codes", "email", "estimated_taxes", "financial_status", "fulfillment_status", "landing_site", "landing_site_ref", "location_id", "merchant_of_record_app_id", "name", "note", "note_attributes", "number", "order_number", "order_status_url", "original_total_additional_fees_set", "original_total_duties_set", "payment_gateway_names", "phone", "po_number", "presentment_currency", "processed_at", "reference", "referring_site", "source_identifier", "source_name", "source_url", "subtotal_price", "subtotal_price_set", "tags", "tax_exempt", "tax_lines", "taxes_included", "test", "token", "total_discounts", "total_discounts_set", "total_line_items_price", "total_line_items_price_set", "total_outstanding", "total_price", "total_price_set", "total_shipping_price_set", "total_tax", "total_tax_set", "total_tip_received", "total_weight", "updated_at", "user_id", "billing_address", "customer", "discount_applications", "fulfillments", "line_items", "payment_terms", "refunds", "shipping_address", "shipping_lines"})
in
#"Expanded Column1"