Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Solved! Go to 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.
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"
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.
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.
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.
@ams1 Awesome! Syntax-wise, what would that fourth parameter look like?
?
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. 😊
I did! Thank you for the last answer, too.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
21 | |
20 | |
13 | |
13 |