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 issues and progressing well. I have run into an issue I cant get my head around.
Tickets have 'time spent' by agents logged against them and multiple time entries per ticket can be made. There doesn't appear to be a dedicated table for time entries, instead they are part of the ticket api.
The API looks like below including live example result:
/api/v2/tickets/[ticket_id]/time_entries
{
"time_entries": [
{
"id": 52000858999,
"created_at": "2024-04-15T11:32:48Z",
"updated_at": "2024-04-15T11:32:48Z",
"start_time": "2024-04-15T11:32:48Z",
"timer_running": false,
"billable": true,
"time_spent": "00:30",
"executed_at": "2024-04-15T11:32:43Z",
"task_id": null,
"workspace_id": 2,
"note": null,
"agent_id": 52000014468,
"custom_fields": {}
},
{
"id": 52000853522,
"created_at": "2024-04-11T16:15:41Z",
"updated_at": "2024-04-11T16:15:41Z",
"start_time": "2024-04-11T16:15:41Z",
"timer_running": false,
"billable": true,
"time_spent": "00:10",
"executed_at": "2024-04-11T16:15:37Z",
"task_id": null,
"workspace_id": 2,
"note": null,
"agent_id": 52000014468,
"custom_fields": {}
}
]
}
What I want to do to include this data in my reporting is use my existing Tickets table ticket_id column and loop through the ticket_id's to populate a new time_entries table.
Additionally, because the time entries don't include the associated ticket_id in the entries and to be able to link the time entries table to the ticket table, I want to add the ticket_id to each time entry.
Im struggling to get me head around how i can do this so any pointers would be hugely appreciated
10 Replies
you already know the ticket ID as you use that for the call. Include the column when you flatten out the received JSON.
Im struggling to create a function and then loop through the IDs of an exisiting table to feed into the API call.
This is the function (which works fine):
//GetTicketTimeEntries (TicketID as number) => let Source = Json.Document(Web.Contents(FreshserviceBaseURL, [RelativePath="/tickets/" & Number.ToText(TicketID) & "/time_entries", Headers=[Authorization=FreshserviceAuthHeader]])) in Sourceand this is the table query:
//AllTimeEntries let Records = List.Generate(()=> [Source = GetTicketTimeEntries(1), id=1], each List.Count([Source][time_entries]) > 0, each [Source = TicketID ([id]+1), TicketID=[id] +1], each [Source]) in RecordsI cant figure out how to insert the source to loop through being the TicketIDs of the All Tickets table, id column.
Not sure what you need the second query for. Can't you just use the JSON from the API call directly? Or is there a typo in the
each [Source = TicketID ([id]+1), TicketID=[id] +1],line?
Can you provide an example of the query to loop through all ticket ids from the ticket table? Would i need to use some like this to load the values into a variable?
source = Table.SelectColumns(AllTickets,"id"),
As you can probably appreciate it is nearly impossible to help with API queries without access to said API (which you may not be willing to provide for understandable reasons)
