cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
markholland Regular Visitor
Regular Visitor

Re: Rest API _ Json _ several pages _ automatically call the next_page_URL

Hi @ImkeF,

 

There always seems to be a hurdle somewhere. Apparently you can schedule a refresh on a dataset with pagination in the M Language, which is the main part I'm trying to achieve.

 

Do you know any ways around this?

 

Mark

Highlighted
Super User
Super User

Re: Rest API _ Json _ several pages _ automatically call the next_page_URL

Yes Mark, that's an issue. If you skip to the end of the thread that you've mentioned, you'll find a solution for it 🙂

Or check this article which has a very detailled description/solution: http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Re: Rest API _ Json _ several pages _ automatically call the next_page_URL

@Paull@ImkeF

 

I am trying to call APIs from Freshservice. After many weeks, I finally was able to call the site which returned only 30 records. I need to be able to paginate. I have visited every single link mentioned on the blogs but I am not able to follow...can any of you please see what I am doing wrong here

 

Source = Json.Document(Web.Contents("https://swinerton.freshservice.com/helpdesk/tickets/filter/all_tickets/?pages",[Headers=[Authorizati... xxxxxxxxx", #"Content_Type"="application/json"]])),
iterations = Source[total_pages], // get the information within the response
url = "https://swinerton.freshservice.com/helpdesk/tickets/filter/all_tickets/?pages", // here goes your URL

FnGetOnePage =
(url) as record =>
let
Source = Json.Document(Web.Contents("https://swinerton.freshservice.com/helpdesk/tickets/filter/all_tickets/?pages", [Headers=[Authorization="xxxx", #"Content_Type"="application/json"]])),
Page = try Source[connections] otherwise null, //get the data of the first page
next = try Source[next_page_url] otherwise null, // the script ask if there is another page
res = [Page=page, Next=next]
in
res,

GeneratedList =
List.Generate(
()=>[i=0, res = FnGetOnePage(url)],
each [i]<iterations and [res][Page]<>null,
each [i=[i]+1, res = FnGetOnePage([res][Next])],
each [Page][Data]),
#"Converti en table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converti en table"

Super User
Super User

Re: Rest API _ Json _ several pages _ automatically call the next_page_URL

At which step does the query break and what error-message do you get?

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Re: Rest API _ Json _ several pages _ automatically call the next_page_URL

api error.jpg

Re: Rest API _ Json _ several pages _ automatically call the next_page_URL

api error.jpg

Super User
Super User

Re: Rest API _ Json _ several pages _ automatically call the next_page_URL

You're missing the let-keyword at the beginning:

 

let Source = Json.Document(Web.Contents("https://swinerton.freshservice.com/helpdesk/tickets/filter/all_tickets/?pages",[Headers=[Authorizati... xxxxxxxxx", #"Content_Type"="application/json"]])),
iterations = Source[total_pages], // get the information within the response
url = "https://swinerton.freshservice.com/helpdesk/tickets/filter/all_tickets/?pages", // here goes your URL

FnGetOnePage =
(url) as record =>
let
Source = Json.Document(Web.Contents("https://swinerton.freshservice.com/helpdesk/tickets/filter/all_tickets/?pages", [Headers=[Authorization="xxxx", #"Content_Type"="application/json"]])),
Page = try Source[connections] otherwise null, //get the data of the first page
next = try Source[next_page_url] otherwise null, // the script ask if there is another page
res = [Page=page, Next=next]
in
res,

GeneratedList =
List.Generate(
()=>[i=0, res = FnGetOnePage(url)],
each [i]<iterations and [res][Page]<>null,
each [i=[i]+1, res = FnGetOnePage([res][Next])],
each [Page][Data]),
#"Converti en table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converti en table"

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Re: Rest API _ Json _ several pages _ automatically call the next_page_URL

@ImkeF

 

I have posted the error image

 

Also i wanted to give you some context I am able to call Freshservice API but it only returns 30 records

let
Source = Json.Document(Web.Contents("https://swinerton.freshservice.com/helpdesk/tickets/filter/all_tickets.json ", [Headers=[Authorization="Basic xxxxxxx", #"Content_Type"="application/json"]])),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"cc_email", "created_at", "deleted", "department_id_value", "display_id", "due_by", "email_config_id", "frDueBy", "fr_escalated", "group_id", "id", "isescalated", "owner_id", "priority", "requester_id", "responder_id", "source", "spam", "status", "subject", "ticket_type", "to_email", "updated_at", "description", "description_html", "status_name", "requester_status_name", "priority_name", "source_name", "requester_name", "responder_name", "to_emails", "department_name", "assoc_problem_id", "assoc_change_id", "assoc_change_cause_id", "assoc_asset_id", "custom_field"}, {"Column1.cc_email", "Column1.created_at", "Column1.deleted", "Column1.department_id_value", "Column1.display_id", "Column1.due_by", "Column1.email_config_id", "Column1.frDueBy", "Column1.fr_escalated", "Column1.group_id", "Column1.id", "Column1.isescalated", "Column1.owner_id", "Column1.priority", "Column1.requester_id", "Column1.responder_id", "Column1.source", "Column1.spam", "Column1.status", "Column1.subject", "Column1.ticket_type", "Column1.to_email", "Column1.updated_at", "Column1.description", "Column1.description_html", "Column1.status_name", "Column1.requester_status_name", "Column1.priority_name", "Column1.source_name", "Column1.requester_name", "Column1.responder_name", "Column1.to_emails", "Column1.department_name", "Column1.assoc_problem_id", "Column1.assoc_change_id", "Column1.assoc_change_cause_id", "Column1.assoc_asset_id", "Column1.custom_field"}),
#"Expanded Column1.cc_email" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.cc_email", {"cc_emails", "fwd_emails", "reply_cc", "tkt_cc"}, {"Column1.cc_email.cc_emails", "Column1.cc_email.fwd_emails", "Column1.cc_email.reply_cc", "Column1.cc_email.tkt_cc"}),
#"Expanded Column1.custom_field" = Table.ExpandRecordColumn(#"Expanded Column1.cc_email", "Column1.custom_field", {"level_2_12133", "level_3_12133", "preferred_method_of_contact_12133", "best_time_to_contact_12133", "phone_number_12133", "level_1_12133", "office_location_12133", "job_number_12133"}, {"Column1.custom_field.level_2_12133", "Column1.custom_field.level_3_12133", "Column1.custom_field.preferred_method_of_contact_12133", "Column1.custom_field.best_time_to_contact_12133", "Column1.custom_field.phone_number_12133", "Column1.custom_field.level_1_12133", "Column1.custom_field.office_location_12133", "Column1.custom_field.job_number_12133"}),
#"Expanded Column1.cc_email.cc_emails" = Table.ExpandListColumn(#"Expanded Column1.custom_field", "Column1.cc_email.cc_emails"),
#"Expanded Column1.cc_email.fwd_emails" = Table.ExpandListColumn(#"Expanded Column1.cc_email.cc_emails", "Column1.cc_email.fwd_emails"),
#"Expanded Column1.cc_email.reply_cc" = Table.ExpandListColumn(#"Expanded Column1.cc_email.fwd_emails", "Column1.cc_email.reply_cc"),
#"Expanded Column1.cc_email.tkt_cc" = Table.ExpandListColumn(#"Expanded Column1.cc_email.reply_cc", "Column1.cc_email.tkt_cc")
in
#"Expanded Column1.cc_email.tkt_cc"

 

On Freshservice site, it says that to iterate through pages, we need to do this api pagination.jpg

 

 

Thus if you see the new query i have appended page to the end of the url, 

("https://swinerton.freshservice.com/helpdesk/tickets/filter/all_tickets/?pages

Re: Rest API _ Json _ several pages _ automatically call the next_page_URL

@ImkeF

 

I have posted the error image

 

Also i wanted to give you some context I am able to call Freshservice API but it only returns 30 records

let
Source = Json.Document(Web.Contents("https://swinerton.freshservice.com/helpdesk/tickets/filter/all_tickets.json ", [Headers=[Authorization="Basic xxxxxxx", #"Content_Type"="application/json"]])),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"cc_email", "created_at", "deleted", "department_id_value", "display_id", "due_by", "email_config_id", "frDueBy", "fr_escalated", "group_id", "id", "isescalated", "owner_id", "priority", "requester_id", "responder_id", "source", "spam", "status", "subject", "ticket_type", "to_email", "updated_at", "description", "description_html", "status_name", "requester_status_name", "priority_name", "source_name", "requester_name", "responder_name", "to_emails", "department_name", "assoc_problem_id", "assoc_change_id", "assoc_change_cause_id", "assoc_asset_id", "custom_field"}, {"Column1.cc_email", "Column1.created_at", "Column1.deleted", "Column1.department_id_value", "Column1.display_id", "Column1.due_by", "Column1.email_config_id", "Column1.frDueBy", "Column1.fr_escalated", "Column1.group_id", "Column1.id", "Column1.isescalated", "Column1.owner_id", "Column1.priority", "Column1.requester_id", "Column1.responder_id", "Column1.source", "Column1.spam", "Column1.status", "Column1.subject", "Column1.ticket_type", "Column1.to_email", "Column1.updated_at", "Column1.description", "Column1.description_html", "Column1.status_name", "Column1.requester_status_name", "Column1.priority_name", "Column1.source_name", "Column1.requester_name", "Column1.responder_name", "Column1.to_emails", "Column1.department_name", "Column1.assoc_problem_id", "Column1.assoc_change_id", "Column1.assoc_change_cause_id", "Column1.assoc_asset_id", "Column1.custom_field"}),
#"Expanded Column1.cc_email" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.cc_email", {"cc_emails", "fwd_emails", "reply_cc", "tkt_cc"}, {"Column1.cc_email.cc_emails", "Column1.cc_email.fwd_emails", "Column1.cc_email.reply_cc", "Column1.cc_email.tkt_cc"}),
#"Expanded Column1.custom_field" = Table.ExpandRecordColumn(#"Expanded Column1.cc_email", "Column1.custom_field", {"level_2_12133", "level_3_12133", "preferred_method_of_contact_12133", "best_time_to_contact_12133", "phone_number_12133", "level_1_12133", "office_location_12133", "job_number_12133"}, {"Column1.custom_field.level_2_12133", "Column1.custom_field.level_3_12133", "Column1.custom_field.preferred_method_of_contact_12133", "Column1.custom_field.best_time_to_contact_12133", "Column1.custom_field.phone_number_12133", "Column1.custom_field.level_1_12133", "Column1.custom_field.office_location_12133", "Column1.custom_field.job_number_12133"}),
#"Expanded Column1.cc_email.cc_emails" = Table.ExpandListColumn(#"Expanded Column1.custom_field", "Column1.cc_email.cc_emails"),
#"Expanded Column1.cc_email.fwd_emails" = Table.ExpandListColumn(#"Expanded Column1.cc_email.cc_emails", "Column1.cc_email.fwd_emails"),
#"Expanded Column1.cc_email.reply_cc" = Table.ExpandListColumn(#"Expanded Column1.cc_email.fwd_emails", "Column1.cc_email.reply_cc"),
#"Expanded Column1.cc_email.tkt_cc" = Table.ExpandListColumn(#"Expanded Column1.cc_email.reply_cc", "Column1.cc_email.tkt_cc")
in
#"Expanded Column1.cc_email.tkt_cc"

 

On Freshservice site, it says that to iterate through pages, we need to do this api pagination.jpg

 

 

Thus if you see the new query i have appended page to the end of the url, 

("https://swinerton.freshservice.com/helpdesk/tickets/filter/all_tickets/?pages

Re: Rest API _ Json _ several pages _ automatically call the next_page_URL

 

 

I have posted the error image

 

Also i wanted to give you some context I am able to call Freshservice API but it only returns 30 records

let
Source = Json.Document(Web.Contents("https://swinerton.freshservice.com/helpdesk/tickets/filter/all_tickets.json ", [Headers=[Authorization="Basic xxxxxxx", #"Content_Type"="application/json"]])),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"cc_email", "created_at", "deleted", "department_id_value", "display_id", "due_by", "email_config_id", "frDueBy", "fr_escalated", "group_id", "id", "isescalated", "owner_id", "priority", "requester_id", "responder_id", "source", "spam", "status", "subject", "ticket_type", "to_email", "updated_at", "description", "description_html", "status_name", "requester_status_name", "priority_name", "source_name", "requester_name", "responder_name", "to_emails", "department_name", "assoc_problem_id", "assoc_change_id", "assoc_change_cause_id", "assoc_asset_id", "custom_field"}, {"Column1.cc_email", "Column1.created_at", "Column1.deleted", "Column1.department_id_value", "Column1.display_id", "Column1.due_by", "Column1.email_config_id", "Column1.frDueBy", "Column1.fr_escalated", "Column1.group_id", "Column1.id", "Column1.isescalated", "Column1.owner_id", "Column1.priority", "Column1.requester_id", "Column1.responder_id", "Column1.source", "Column1.spam", "Column1.status", "Column1.subject", "Column1.ticket_type", "Column1.to_email", "Column1.updated_at", "Column1.description", "Column1.description_html", "Column1.status_name", "Column1.requester_status_name", "Column1.priority_name", "Column1.source_name", "Column1.requester_name", "Column1.responder_name", "Column1.to_emails", "Column1.department_name", "Column1.assoc_problem_id", "Column1.assoc_change_id", "Column1.assoc_change_cause_id", "Column1.assoc_asset_id", "Column1.custom_field"}),
#"Expanded Column1.cc_email" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.cc_email", {"cc_emails", "fwd_emails", "reply_cc", "tkt_cc"}, {"Column1.cc_email.cc_emails", "Column1.cc_email.fwd_emails", "Column1.cc_email.reply_cc", "Column1.cc_email.tkt_cc"}),
#"Expanded Column1.custom_field" = Table.ExpandRecordColumn(#"Expanded Column1.cc_email", "Column1.custom_field", {"level_2_12133", "level_3_12133", "preferred_method_of_contact_12133", "best_time_to_contact_12133", "phone_number_12133", "level_1_12133", "office_location_12133", "job_number_12133"}, {"Column1.custom_field.level_2_12133", "Column1.custom_field.level_3_12133", "Column1.custom_field.preferred_method_of_contact_12133", "Column1.custom_field.best_time_to_contact_12133", "Column1.custom_field.phone_number_12133", "Column1.custom_field.level_1_12133", "Column1.custom_field.office_location_12133", "Column1.custom_field.job_number_12133"}),
#"Expanded Column1.cc_email.cc_emails" = Table.ExpandListColumn(#"Expanded Column1.custom_field", "Column1.cc_email.cc_emails"),
#"Expanded Column1.cc_email.fwd_emails" = Table.ExpandListColumn(#"Expanded Column1.cc_email.cc_emails", "Column1.cc_email.fwd_emails"),
#"Expanded Column1.cc_email.reply_cc" = Table.ExpandListColumn(#"Expanded Column1.cc_email.fwd_emails", "Column1.cc_email.reply_cc"),
#"Expanded Column1.cc_email.tkt_cc" = Table.ExpandListColumn(#"Expanded Column1.cc_email.reply_cc", "Column1.cc_email.tkt_cc")
in
#"Expanded Column1.cc_email.tkt_cc"

 

On Freshservice site, it says that to iterate through pages, we need to do this api pagination.jpg

 

 

Thus if you see the new query i have appended page to the end of the url, 

("https://swinerton.freshservice.com/helpdesk/tickets/filter/all_tickets/?pages

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 72 members 1,359 guests
Please welcome our newest community members: