Forum Discussion
Use existing table column to loop through each id and create a new table with API
Hi All Im working with the Freshservice API (Ticket platform) and have had good success pulling in ticket and associated tables/data into Power BI. I have used this link to get around pagination iss...
Yeah, im totally confused to be honest. Are you saying use the ListGenerate function in the first query?
I would assume you get a JSON array back and you shoud be able to convert that into a table for each ticket. Then at the end you just expand that column to concatenate all the tables.
Using Postman, when I do a GET /api/v2/tickets/19302/, the JSON returns all ticket details but has no data on time entries. To get the time entries, i need to include time_entries like this api/v2/tickets/19302/time_entries
Service Desk API for Developers | Freshservice
//GET 'https://domain.freshservice.com/api/v2/tickets/20' { "ticket": { "cc_emails": [], "fwd_emails": [], "reply_cc_emails": [], "fr_escalated": false, "spam": false, "email_config_id": null, "group_id": null, "priority": 3, "requester_id": 1000000678, "requested_for_id": 1000000670, "responder_id": null, "source": 2, "status": 2, "subject": "Ticket Title", "to_emails": null, "sla_policy_id": 1000000029, "department_id": null, "id": 20, "type": "Incident", "due_by": "2017-09-08T23:03:44Z", "fr_due_by": "2017-09-08T15:03:44Z", "is_escalated": false, "description": "<div>this is a sample ticket</div>", "description_text": "this is a sample ticket", "custom_fields": { "custom_text": null, "auto_checkbox": false }, "created_at": "2017-09-08T11:03:44Z", "updated_at": "2017-09-08T11:37:01Z", "urgency": 1, "impact": 1, "category": null, "sub_category": null, "item_category": null, "deleted": false, "resolution_notes":null, "resolution_notes_html":null, "attachments": [ { "content_type": "text/plain", "size": 5, "name": "attachment.txt", "attachment_url": "https://cdn.freshservice/data/Helpdesk/attachments/production/19852343/original/attachment.txt", "created_at": "2017-09-08T11:03:45Z", "updated_at": "2017-09-08T11:03:45Z" } ], "workspace_id": 3, "created_within_business_hours": false, "approval_status": 4, "approval_status_name": "Not Requested" } } //GET 'https://domain.freshservice.com/api/v2/tickets/20/time_entries' { "time_entries": [ { "id": 901, "created_at": "2019-07-19T10:17:23Z", "updated_at": "2019-07-19T13:18:40Z", "start_time": "2019-07-19T13:18:09Z", "timer_running": false, "billable": true, "time_spent": "03:00", "executed_at": "2019-07-18T18:30:00Z", "task_id": null, "workspace_id": 3, "note": "time entry 1", "agent_id": 1, "custom_fields": {} }, { "id": 902, "created_at": "2019-07-19T10:18:23Z", "updated_at": "2019-07-19T13:19:40Z", "start_time": "2019-07-19T13:19:09Z", "timer_running": false, "billable": true, "time_spent": "03:00", "executed_at": "2019-07-18T18:30:00Z", "task_id": null, "workspace_id": 3, "note": "time entry 2", "agent_id": 1, "custom_fields": {} } ] }So my thought was i need to create a new table for all time entries and link that table with the Tickets table using the ticket ID.
let Source = Json.Document(" { ""ticket"": { ""cc_emails"": [], ""fwd_emails"": [], ""reply_cc_emails"": [], ""fr_escalated"": false, ""spam"": false, ""email_config_id"": null, ""group_id"": null, ""priority"": 3, ""requester_id"": 1000000678, ""requested_for_id"": 1000000670, ""responder_id"": null, ""source"": 2, ""status"": 2, ""subject"": ""Ticket Title"", ""to_emails"": null, ""sla_policy_id"": 1000000029, ""department_id"": null, ""id"": 20, ""type"": ""Incident"", ""due_by"": ""2017-09-08T23:03:44Z"", ""fr_due_by"": ""2017-09-08T15:03:44Z"", ""is_escalated"": false, ""description"": ""<div>this is a sample ticket</div>"", ""description_text"": ""this is a sample ticket"", ""custom_fields"": { ""custom_text"": null, ""auto_checkbox"": false }, ""created_at"": ""2017-09-08T11:03:44Z"", ""updated_at"": ""2017-09-08T11:37:01Z"", ""urgency"": 1, ""impact"": 1, ""category"": null, ""sub_category"": null, ""item_category"": null, ""deleted"": false, ""resolution_notes"":null, ""resolution_notes_html"":null, ""attachments"": [ { ""content_type"": ""text/plain"", ""size"": 5, ""name"": ""attachment.txt"", ""attachment_url"": ""https://cdn.freshservice/data/Helpdesk/attachments/production/19852343/original/attachment.txt"", ""created_at"": ""2017-09-08T11:03:45Z"", ""updated_at"": ""2017-09-08T11:03:45Z"" } ], ""workspace_id"": 3, ""created_within_business_hours"": false, ""approval_status"": 4, ""approval_status_name"": ""Not Requested"" } } "), #"Converted to Table" = Record.ToTable(Source), #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"cc_emails", "fwd_emails", "reply_cc_emails", "fr_escalated", "spam", "email_config_id", "group_id", "priority", "requester_id", "requested_for_id", "responder_id", "source", "status", "subject", "to_emails", "sla_policy_id", "department_id", "id", "type", "due_by", "fr_due_by", "is_escalated", "description", "description_text", "custom_fields", "created_at", "updated_at", "urgency", "impact", "category", "sub_category", "item_category", "deleted", "resolution_notes", "resolution_notes_html", "attachments", "workspace_id", "created_within_business_hours", "approval_status", "approval_status_name"}, {"cc_emails", "fwd_emails", "reply_cc_emails", "fr_escalated", "spam", "email_config_id", "group_id", "priority", "requester_id", "requested_for_id", "responder_id", "source", "status", "subject", "to_emails", "sla_policy_id", "department_id", "id", "type", "due_by", "fr_due_by", "is_escalated", "description", "description_text", "custom_fields", "created_at", "updated_at", "urgency", "impact", "category", "sub_category", "item_category", "deleted", "resolution_notes", "resolution_notes_html", "attachments", "workspace_id", "created_within_business_hours", "approval_status", "approval_status_name"}), #"Added Custom" = Table.AddColumn(#"Expanded Value", "time_entries", each Json.Document(" { ""time_entries"": [ { ""id"": 901, ""created_at"": ""2019-07-19T10:17:23Z"", ""updated_at"": ""2019-07-19T13:18:40Z"", ""start_time"": ""2019-07-19T13:18:09Z"", ""timer_running"": false, ""billable"": true, ""time_spent"": ""03:00"", ""executed_at"": ""2019-07-18T18:30:00Z"", ""task_id"": null, ""workspace_id"": 3, ""note"": ""time entry 1"", ""agent_id"": 1, ""custom_fields"": {} }, { ""id"": 902, ""created_at"": ""2019-07-19T10:18:23Z"", ""updated_at"": ""2019-07-19T13:19:40Z"", ""start_time"": ""2019-07-19T13:19:09Z"", ""timer_running"": false, ""billable"": true, ""time_spent"": ""03:00"", ""executed_at"": ""2019-07-18T18:30:00Z"", ""task_id"": null, ""workspace_id"": 3, ""note"": ""time entry 2"", ""agent_id"": 1, ""custom_fields"": {} } ] } ")), #"Expanded time_entries" = Table.ExpandRecordColumn(#"Added Custom", "time_entries", {"time_entries"}, {"time_entries.1"}), #"Expanded time_entries.1" = Table.ExpandListColumn(#"Expanded time_entries", "time_entries.1"), #"Expanded time_entries.2" = Table.ExpandRecordColumn(#"Expanded time_entries.1", "time_entries.1", {"id", "created_at", "updated_at", "start_time", "timer_running", "billable", "time_spent", "executed_at", "task_id", "workspace_id", "note", "agent_id", "custom_fields"}, {"time_entries.1.id", "time_entries.1.created_at", "time_entries.1.updated_at", "time_entries.1.start_time", "time_entries.1.timer_running", "time_entries.1.billable", "time_entries.1.time_spent", "time_entries.1.executed_at", "time_entries.1.task_id", "time_entries.1.workspace_id", "time_entries.1.note", "time_entries.1.agent_id", "time_entries.1.custom_fields"}) in #"Expanded time_entries.2"Thanks so much for putting that together. I cant get my head around how that would work with pagination and List.Generate(() to get all the tickets.