Pagination code returns first page forever instead of properly paginating

[mrsalta]

I've been consulting all of the recommended articles shared in this forum (thanks for that!) but I think I'm probably missing something very basic.

My query:

(app_id as text, survey_id as text) =>
let
    url=  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=
        [
            page_size="100",
            order="asc"
        ]
    ])),
    
FnGetOnePage =
    (url) as record =>
    let
        Source = url,
        data = try Source[responses] otherwise null,
        next = try Source[starts_with] otherwise null,
        more = try Source[has_more] otherwise null,
        res = [
            Data=data,
            More=more,
            Next=
                if more = true and next <> null then 
                    url & [Query=[page_size="100", order="asc", starts_with=next]]
                else null
        ]
    in
    res,

    GeneratedList = 
        List.Generate(
            () => [res = FnGetOnePage(url)],
            each [res][More] <> false,
            each [res= FnGetOnePage([res][Next])],
            each [res][Data])
        in
        GeneratedList

The API I'm pulling from has a response that generally looks like this:

{
    "responses": [
        ...
    ],
    "ends_with": "63c43d8fe16df41df30e0855",
    "page_size": 100,
    "has_more": true
}

Hopefully I've just made an easy mistake, but can't seem to find the right example thread to help.

Thanks in advance!

[RamTyagi]

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=eyJkaXJlY3Rpb24i...",
nextPageUrl = "https://prostandard.myshopify.com/admin/api/2024-01/orders.json?limit=250&page_info=eyJkaXJlY3Rpb24i...",

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"

[ReportMaster]

Hi @RamTyagi were you able to find a solution? As a workaround, maybe you can try to test your connection with a 3rd party connector, which pulls data directly from the Shopify API and does not require any complex coding. I've tried windsor.ai, supemetrics and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. In case you wonder, to make the connection first search for the Shopify connector in the data sources list:

After that, you need to follow instructions and install the windsor.ai app from the Shopify App Store:

then on preview and destination page you will see a preview of your Shopify fields

There just select the fields you need. Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url.

[ams1]

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.

[mrsalta · Accepted solution]

Thanks @ams1 -
I'm not able to share the API documentation directly, but here's a sample response object they provided:

{
  "responses": [
    {
      "id": "5e22085d5379215be800002b",
      "survey_id": "6e22085d5379215be800002b",
      "created_at": "2020-09-24T00:20:10",
      "conversation_id": "7e22085d5379215be800002b",
      "answers": [
        {
          "question": {
            "id": "5e22085d5379215be800002b",
            "value": "Choose one option",
            "type": "multichoice | multiselect | range | nps | singleline"
          },
          "answer": {
            "value": "'yes' OR ['Red', 'Blue']"
          }
        }
      ],
      "snapshot_data": {
        "device": {
          "id": "55fbe2e775bf3c760b1000bc",
          "carrier": "Verizon",
          "custom_data": {
            "key": "CustomKey",
            "value": "CustomValue"
          },
          "manufacturer": "Samsung",
          "model": "S22",
          "os_api_level": "3.5.9",
          "os_name": "Android",
          "os_version": "5.2.0"
        },
        "person": {
          "id": "95fbe2e775bf3c760b1000bc",
          "custom_data": {
            "key": "CustomKey",
            "value": "CustomValue"
          },
          "email": "foo@bar.com",
          "facebook_id": "1184928249824",
          "mparticle_id": "-29482958295729572",
          "name": "Jack Daniels"
        },
        "app_release": {
          "cf_bundle_version": "78",
          "cf_bundle_short_version_string": "4.5.11",
          "sdk_version": "4.5.11",
          "sdk_platform": "iOS",
          "sdk_distribution": "source",
          "sdk_distribution_version": "4.5.11"
        }
      }
    }
  ],
  "ends_with": "6e22085d5379215be800002b",
  "page_size": 250,
  "has_more": true
}

And these are the sample params:

I've tried your sample code and I think I just need to make sure the result is the  `"responses": [` portion and the `ends_with`, `page_size`, and `has_more` just used for cursor/paging.

[ams1]

Exactly - you just need to extract "responses" from "pages" - you should be able to do that using the 4th parameter of List.Generate (the selector).

Don't forget to mark as ANSWER the reply if it helped.

[mrsalta]

@ams1 Awesome! Syntax-wise, what would that fourth parameter look like?

• pages[responses]
• getNextPage[responses]
• lastPage[responses]
• None of the above, silly

?

[ams1]

Hi,

Somehow I've missed your reply - sorry for that.

If you haven't figured it out (but I think you did), it can be something like:

...
        pages = List.Generate(
            // initial
            () => getNextPage(null),
            // condition
            (lastPage) => lastPage <> null,
            //next
            (lastPage) => getNextPage(lastPage),
            // selector
            (lastPage) => lastPage[responses] // <---------
        )
...

Thanks for the "thumbs-up" (aka kudo), but please also mark as ANSWER the most relevant replies -> so that the question status is answered. 😊

[mrsalta]

I did! Thank you for the last answer, too.