Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
MyThumbsClick
Helper II
Helper 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 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 10
MyThumbsClick
Helper II
Helper II

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)

lbendlin
Super User
Super User

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

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?

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

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors