Hi, I am looking for some help in regards to connecting to FreshDesk API. The results are paginated, so the link would be:
https://domain.freshdesk.com/api/v2/tickets?per_page=100&page=2
I have gotten PowerBI to return the first 100 rows, ie page 1 using this query
let
Source = Json.Document(Web.Contents("https://domain.freshdesk.com/api/v2/tickets?per_page=100")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"cc_emails", "fwd_emails", "reply_cc_emails", "ticket_cc_emails", "fr_escalated", "spam", "email_config_id", "group_id", "priority", "requester_id", "responder_id", "source", "company_id", "status", "subject", "association_type", "support_email", "to_emails", "product_id", "id", "type", "due_by", "fr_due_by", "is_escalated", "custom_fields", "created_at", "updated_at", "associated_tickets_count", "tags", "internal_agent_id", "internal_group_id", "nr_due_by", "nr_escalated"}, {"cc_emails", "fwd_emails", "reply_cc_emails", "ticket_cc_emails", "fr_escalated", "spam", "email_config_id", "group_id", "priority", "requester_id", "responder_id", "source", "company_id", "status", "subject", "association_type", "support_email", "to_emails", "product_id", "id", "type", "due_by", "fr_due_by", "is_escalated", "custom_fields", "created_at", "updated_at", "associated_tickets_count", "tags", "internal_agent_id", "internal_group_id", "nr_due_by", "nr_escalated"}),
#"Split Column" = Table.SplitColumn(#"Expanded Column1", "to_emails", each _, {"to_emails.0", "to_emails.1"}),
#"Expanded custom_fields" = Table.ExpandRecordColumn(#"Split Column", "custom_fields", {"cf_l1", "cf_l2", "cf_l3", "cf_fsm_contact_name", "cf_fsm_phone_number", "cf_fsm_service_location", "cf_fsm_appointment_start_time", "cf_fsm_appointment_end_time"}, {"custom_fields.cf_l1", "custom_fields.cf_l2", "custom_fields.cf_l3", "custom_fields.cf_fsm_contact_name", "custom_fields.cf_fsm_phone_number", "custom_fields.cf_fsm_service_location", "custom_fields.cf_fsm_appointment_start_time", "custom_fields.cf_fsm_appointment_end_time"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded custom_fields",{{"cc_emails", type any}, {"fwd_emails", type any}, {"reply_cc_emails", type any}, {"ticket_cc_emails", type any}, {"fr_escalated", type logical}, {"spam", type logical}, {"email_config_id", Int64.Type}, {"group_id", Int64.Type}, {"priority", Int64.Type}, {"requester_id", Int64.Type}, {"responder_id", type any}, {"source", Int64.Type}, {"company_id", type any}, {"status", Int64.Type}, {"subject", type text}, {"association_type", type any}, {"support_email", type text}, {"to_emails.0", type text}, {"to_emails.1", type text}, {"product_id", type any}, {"id", Int64.Type}, {"type", type text}, {"due_by", type datetime}, {"fr_due_by", type datetime}, {"is_escalated", type logical}, {"custom_fields.cf_l1", type any}, {"custom_fields.cf_l2", type any}, {"custom_fields.cf_l3", type any}, {"custom_fields.cf_fsm_contact_name", type any}, {"custom_fields.cf_fsm_phone_number", type any}, {"custom_fields.cf_fsm_service_location", type any}, {"custom_fields.cf_fsm_appointment_start_time", type any}, {"custom_fields.cf_fsm_appointment_end_time", type any}, {"created_at", type datetime}, {"updated_at", type datetime}, {"associated_tickets_count", type any}, {"tags", type any}, {"internal_agent_id", type any}, {"internal_group_id", type any}, {"nr_due_by", type any}, {"nr_escalated", type logical}})
in
#"Changed Type"
I have gone through various posts and online guides but I cannot fathom how to iterate through the next pages.... The FreshDesk API guide says this:
The 'link' header in the response will hold the next page url if exists. If you have reached the last page of objects, then the link header will not be set.
Headers:
"link":< https://domain.freshdesk.com/api/v2/tickets?filter=all_tickets&page=2>;rel="next"
Can anyone assist? I am not a PowerBI expert and our MI team dont really know about getting the data, just manipulating...
Thanks
Neil
