Use existing table column to loop through each id ...

MyThumbsClick · ‎04-18-2024

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

MyThumbsClick · ‎04-19-2024

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"),

lbendlin · ‎04-19-2024

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)

lbendlin · ‎04-18-2024

you already know the ticket ID as you use that for the call. Include the column when you flatten out the received JSON.

MyThumbsClick · ‎04-19-2024

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 
    Source

and 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
    Records

I cant figure out how to insert the source to loop through being the TicketIDs of the All Tickets table, id column.

lbendlin · ‎04-19-2024

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?

MyThumbsClick · ‎04-19-2024

Yeah, im totally confused to be honest. Are you saying use the ListGenerate function in the first query?

lbendlin · ‎04-19-2024

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.

MyThumbsClick · ‎04-24-2024

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.

lbendlin

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

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.

Use existing table column to loop through each id and create a new table with API

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024