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...
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.