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

Super User III
Super User III

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/

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

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 III
Super User III

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?

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

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 III
Super User III

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"

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors