Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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
Solved! Go to Solution.
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
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
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
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
Okay, so the GeneratedList Step looks as follows:
When expanding the list to see the full records, it looks like this:
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):
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:
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, 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
@ImkeF Thanks for the prompt reply, sure, Here you go:
This is the step "Example"
And 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
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:
If 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
@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
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
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
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
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
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
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:
'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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |