Forum Discussion
MyThumbsClick
2 years agoHelper II
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...
lbendlin
2 years agoSuper User
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"MyThumbsClick
2 years agoHelper II
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.