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