Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Paull
Frequent Visitor

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

Hi Everyone,

I am a newbie on powerBi and would need your help to figure out how to automate a rest-API call with a Json response having several pages.
The initial URL is the following:
https://api.higring.com/v2/reporting/get?period=custom_date&start_date=2017-01-01&end_date=2017-07-2... &group_by=date,platform,custom_1,custom_2, type,ad_format,connection,country&filter[]=network:network1

I get the following response:

capture reponse.JPG

The idea would be to automatically call the “next_page_url” until there is no “next_page_url”.
Any thoughts on how I can process this ?

Thanks a lot

2 ACCEPTED SOLUTIONS
ImkeF
Super User
Super User

This video will show you: https://www.youtube.com/watch?v=vhr4w5G8bRA&t=6s

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Paull
Frequent Visitor

Hi ImkeF,

 

Thanks a lot for your reply. It helps a lot.
I generate a script that do pretty much the same than the video. 
It looks like this:

let
 Source = Json.Document(Web.Contents(url, [Headers=[Authorization="your token"]])),
 iterations = Source[total_pages],          // get the information within the response
 url = "you URL", // here goes your URL
 
 FnGetOnePage =
  (url) as record =>
   let
    Source = Json.Document(Web.Contents(url, [Headers=[Authorization="yourtoken"]])),
    data = 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 = [Data=data, Next=next]
   in
    res,
 
 GeneratedList =
  List.Generate(
   ()=>[i=0, res = FnGetOnePage(url)],
   each [i]<iterations and [res][Data]<>null,
   each [i=[i]+1, res = FnGetOnePage([res][Next])],
   each [res][Data]),
  #"Converti en table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
  #"Converti en table"

It works perfectly.

Have a good day,

 

Paul

View solution in original post

47 REPLIES 47

Hm, that's a bit strange.

What do you get if you expand the 235th result of the invinite loop?

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

@ImkeF 

 

I realized something today! When I extend the counter to 1000 and look at the expanded tables in detail, I saw that it actually loads the first page 1000 times. I filtered for the ticket ID (which is unique for every member in Source[tickets]) and when I count rows, I always get the same number as my counter... So it seems that the code actually does not jump to the next page, but is stuck in loading the first page over and over again.

 

But I don't see why it won't go beyond the first page. The Source[next_page] is correct, and I tried rewriting the code in many ways...

Do you have an idea?
Could the reason be the fact that Source[tickets] returns an actual list whereas Source[next_page] only provides the URL? 

let
  url = "https://myURL/api/v2/tickets.json", // I am using basic authentication
  Source = Json.Document(Web.Contents(url)),
  Tickets = Source[tickets],
  NextPage = Source[next_page],
   
 FnGetOnePage =
  (url) as record =>
    let
    data = try Tickets otherwise null, //get the data of the first page
    next = try NextPage otherwise null, // the script ask if there is another page
    res = [Data=data, Next=next]
   in
    res,

GeneratedList = 
	List.Generate( 
		()=>[res = FnGetOnePage(url), Counter = 0], 
		each [res][Next] <> null and [Counter] < 1000, 
		each [res = FnGetOnePage([res][Next]),
			Counter = [Counter]+1]	)
in
    GeneratedList

 

I cannot see why this is could be the reason.

Coming back ot my previous post, I'd like to see what is actually returned (on each level, if a nested object is returned).

 

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

@ImkeF 

 

Okay, so the GeneratedList Step looks as follows:

14-10-2019 11-31-24.jpg

When expanding the list to see the full records, it looks like this:
14-10-2019 11-34-26.jpg
And then when I filter for one ID, what I get is the same ID 237 times (as the counter is set to stop at 237):
14-10-2019 11-36-38.jpg
And this is the full code (up until the step when I filter for ID):

let
 Source = Json.Document(Web.Contents("https://MYURL/api/v2/tickets.json")),
   tickets = Source[tickets],
   url = "https:/MYURL/api/v2/tickets.json", // I am using basic authentication
 
 FnGetOnePage =
  (url) as record =>
   let
    Source = Source,
    data = try Source[tickets] otherwise null, //get the data of the first page
    next = try Source[next_page] otherwise null, // the script ask if there is another page
    res = [Data=data, Next=next]
   in
    res,

GeneratedList = 
	List.Generate( 
		()=>[res = FnGetOnePage(url), Counter = 0], 
		each [res][Next] <> null and [Counter] < 237, 
		each [res = FnGetOnePage([res][Next]),
			Counter = [Counter]+1	]),
    #"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"res", "Counter"}, {"res", "Counter"}),
    #"Expanded res" = Table.ExpandRecordColumn(#"Expanded Column1", "res", {"Data"}, {"res.Data"}),
    #"Expanded res.Data" = Table.ExpandListColumn(#"Expanded res", "res.Data"),
    #"Expanded res.Data1" = Table.ExpandRecordColumn(#"Expanded res.Data", "res.Data", {"url", "id", "external_id", "via", "created_at", "updated_at", "type", "subject", "raw_subject", "description", "priority", "status", "recipient", "requester_id", "submitter_id", "assignee_id", "organization_id", "group_id", "collaborator_ids", "follower_ids", "email_cc_ids", "forum_topic_id", "problem_id", "has_incidents", "is_public", "due_at", "tags", "custom_fields", "satisfaction_rating", "sharing_agreement_ids", "fields", "followup_ids", "brand_id", "allow_channelback", "allow_attachments"}, {"Column1.res.Data.url", "Column1.res.Data.id", "Column1.res.Data.external_id", "Column1.res.Data.via", "Column1.res.Data.created_at", "Column1.res.Data.updated_at", "Column1.res.Data.type", "Column1.res.Data.subject", "Column1.res.Data.raw_subject", "Column1.res.Data.description", "Column1.res.Data.priority", "Column1.res.Data.status", "Column1.res.Data.recipient", "Column1.res.Data.requester_id", "Column1.res.Data.submitter_id", "Column1.res.Data.assignee_id", "Column1.res.Data.organization_id", "Column1.res.Data.group_id", "Column1.res.Data.collaborator_ids", "Column1.res.Data.follower_ids", "Column1.res.Data.email_cc_ids", "Column1.res.Data.forum_topic_id", "Column1.res.Data.problem_id", "Column1.res.Data.has_incidents", "Column1.res.Data.is_public", "Column1.res.Data.due_at", "Column1.res.Data.tags", "Column1.res.Data.custom_fields", "Column1.res.Data.satisfaction_rating", "Column1.res.Data.sharing_agreement_ids", "Column1.res.Data.fields", "Column1.res.Data.followup_ids", "Column1.res.Data.brand_id", "Column1.res.Data.allow_channelback", "Column1.res.Data.allow_attachments"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded res.Data1", each ([Column1.res.Data.id] = 103733)),
    #"Filtered Rows" = Table.SelectRows(#"Filtered Rows1", each ([Column1.res.Data.id] = 103733))
in
    #"Filtered Rows"


I seriously lost here... much appreciate your support here!

 

Thanks again!

thanks @Anonymous  

but I need to see the following steps:

  •  Expanded Column1
  •  Expanded res
  •  Expanded res.Data

 

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

@ImkeF Alright here you go:

Expanded Column1
Expanded Column 1.jpg

Expanded res
Expanded Res.jpg

Expanded res.Data

res.Data.jpg

Thanks!!

Thanks, but I still need more, unfortunately: What does this return?:

 

let
 Source = Json.Document(Web.Contents("https://MYURL/api/v2/tickets.json")),
   tickets = Source[tickets],
   url = "https:/MYURL/api/v2/tickets.json", // I am using basic authentication
 
 FnGetOnePage =
  (url) as record =>
   let
    Source = Source,
    data = try Source[tickets] otherwise null, //get the data of the first page
    next = try Source[next_page] otherwise null, // the script ask if there is another page
    res = [Data=data, Next=next]
   in
    res,

GeneratedList = 
	List.Generate( 
		()=>[res = FnGetOnePage(url), Counter = 0], 
		each [res][Next] <> null and [Counter] < 237, 
		each [res = FnGetOnePage([res][Next]),
			Counter = [Counter]+1	]),
    #"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"res", "Counter"}, {"res", "Counter"}),
    Sample =  #"Expanded Column1" {230}[res]
in
Sample

 

 

 

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

@ImkeF Thanks for the prompt reply, sure, Here you go:

 

This is the step "Example"

sample.jpg
expanded.jpgAnd this is what happens when I work my way thorugh.

Hi @Anonymous ,

you're not using the function parameter in the fnGetOnePage-function.

 

Change step Source like so:

 

Source = url,

 

instead of: Source = Source,

(so it just calls the first query over and over again..)

 

... I know, it hurts 😉

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

Hi @ImkeF 

Really? that can't be. Because when I change it in the code to this:

FnGetOnePage =
  (url) as record =>
   let
    Source= url,
    data = try Source[tickets] otherwise null, //get the data of the first page
    next = try Source[next_page] otherwise null, // the script ask if there is another page
    res = [Data=data, Next=next]
   in
    res,

GeneratedList = 
	List.Generate( 
		()=>[res = FnGetOnePage(url), Counter = 0], 
		each [res][Next] <> null and [Counter] < 1000, 
		each [res = FnGetOnePage([res][Next]),
			Counter = [Counter]+1])

in
    GeneratedList

It just returns an empty list in the step GeneratedList:

 

LIst.jpgIf I omit the definition of Source in the FnGetOnePage step entirely, what I get is the same result as before with the loop being stuck on one page. And really, since the source does not change for the FnGetOnePage, is it needed to define this parameter?
It should always start on the /MYURL/tickets.json URL, but grab data (the current page, tickets) and next (the NextPage) depending on the return of "NextPage".

Could it be that we need to overwrite the url in each iteration to grab the latest url from the latest step? Right now it is pointing to page 2 over and over again, right? so it's stuck on the next_page reference for page one (the MYURL/tickets.json default response) for every step in the loop...
So I think the problem might be here:

GeneratedList = 
	List.Generate( 
		()=>[res = FnGetOnePage(url), Counter = 0], 
		each [res][Next] <> null and [Counter] < 1000, 
		each [res = FnGetOnePage([res][Next]),
			Counter = [Counter]+1])

But I don't have enough experience with M to really see what's going on...

 

Again, I think I am very close but I just don't seem to get it right...:)

 

you're right - the url need to be called somewhere as well 🙂

so how does this work?:

 

let
 Source = Json.Document(Web.Contents("https://MYURL/api/v2/tickets.json")),
   tickets = Source[tickets],
   url = "https:/MYURL/api/v2/tickets.json", // I am using basic authentication
 
 FnGetOnePage =
  (url) as record =>
   let
    Source = Json.Document(Web.Contents(url)),
    data = try Source[tickets] otherwise null, //get the data of the first page
    next = try Source[next_page] otherwise null, // the script ask if there is another page
    res = [Data=data, Next=next]
   in
    res,

GeneratedList = 
	List.Generate( 
		()=>[res = FnGetOnePage(url), Counter = 0], 
		each [res][Next] <> null and [Counter] < 237, 
		each [res = FnGetOnePage([res][Next]),
			Counter = [Counter]+1	]),
    #"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"res", "Counter"}, {"res", "Counter"}),
    Sample =  #"Expanded Column1" {230}[res]
in
   Sample

 

 

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

@ImkeF Success!! Finally, it worked. So here's the final overall code, including the steps to get to the final full table:

 

let
 Source = Json.Document(Web.Contents("MYURL/api/v2/tickets.json")),
   tickets = Source[tickets],
   url = "MYURL/api/v2/tickets.json", // I am using basic authentication
 
  FnGetOnePage =
  (url) as record =>
   let
    Source = Json.Document(Web.Contents(url)),
    data = try Source[tickets] otherwise null, //get the data of the first page
    next = try Source[next_page] otherwise null, // the script ask if there is another page
    res = [Data=data, Next=next]
   in
    res,

GeneratedList = 
	List.Generate( 
		()=>[res = FnGetOnePage(url), Counter = 0], 
		each [res][Next] <> null and [Counter] < 1000, 
		each [res = FnGetOnePage([res][Next]),
			Counter = [Counter]+1]),
    #"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"res"}, {"res"}),
    #"Expanded res" = Table.ExpandRecordColumn(#"Expanded Column1", "res", {"Data", "Next"}, {"Data", "Next"}),
    #"Expanded Data" = Table.ExpandListColumn(#"Expanded res", "Data"),
    #"Expanded Data1" = Table.ExpandRecordColumn(#"Expanded Data", "Data", {"url", "id", "external_id", "via", "created_at", "updated_at", "type", "subject", "raw_subject", "description", "priority", "status", "recipient", "requester_id", "submitter_id", "assignee_id", "organization_id", "group_id", "collaborator_ids", "follower_ids", "email_cc_ids", "forum_topic_id", "problem_id", "has_incidents", "is_public", "due_at", "tags", "custom_fields", "satisfaction_rating", "sharing_agreement_ids", "fields", "followup_ids", "brand_id", "allow_channelback", "allow_attachments"}, {"url", "id", "external_id", "via", "created_at", "updated_at", "type", "subject", "raw_subject", "description", "priority", "status", "recipient", "requester_id", "submitter_id", "assignee_id", "organization_id", "group_id", "collaborator_ids", "follower_ids", "email_cc_ids", "forum_topic_id", "problem_id", "has_incidents", "is_public", "due_at", "tags", "custom_fields", "satisfaction_rating", "sharing_agreement_ids", "fields", "followup_ids", "brand_id", "allow_channelback", "allow_attachments"})

in
    #"Expanded Data1"

Thanks ever so much for your help!

Very pleased to hear @Anonymous ,

you might leave a kudos then 😉

Thx!

 

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

api error.jpg

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

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

 

 

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

@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

@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

Having had a closer look at your code and it seems like you're new to the M-language.

But this task requires at least some basic understanding of the M-language. For example what to do with the parameter of a function, as you're not using it in your function FnGetOnePage (video recommendation: https://www.youtube.com/watch?v=GgwXt4LVmsU&t=666s) . To get a basic understanding what's happening in the code you have to build I recommend this video: https://www.youtube.com/watch?v=vhr4w5G8bRA

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hi Mark,

you might have to adjust the field names in the code (these are strings in the square brackets) to match what your API returns.

If you need help, please post a picture of the result that your API-call (Source-step) returns.

Cheers, Imke

Imke Feldmann

www.TheBIccountant.com -- How to integrate M-code into your solution  -- Check out more PBI- learning resources here

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Thanks @ImkeF,

 

Here's a picture of the result I get once I pass the API through Query Editor:

 

01.PNG

 

'time_entries' gives me the 100 records for the last page. 'Links' shows the 3 URLs - first page, second page and last page.

 

I've been playing around with the fields in the square brackets but I'm not totally sure which ones to place where.

 

What would you suggest?

 

Thanks,

Mark

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.