Integrating Desk365 with Power BI is a straightforward process that involves just three simple steps. You need to import the data from Desk365 into Power BI with the help of the Desk365 API and then you can transform it into customized, impactful reports using Power Query with pagination, which enables data cleansing, shaping, and combining data from multiple sources. And then you can filter the tickets within the report in Power BI.
By the end of this help center article, you’ll be able to create interactive reports and dashboards that provide valuable insights into your customer support operations.
Establish connection with Desk365's API
To initiate the integration of Desk365 with Power BI, you have to establish a connection with Desk365’s API from Power BI. To do that navigate to your Power BI tool and perform the following actions.
1. Select Web from common data sources under Get data.
2. Click on Advanced and fill in the details as mentioned below.
Enter the below link in URL parts.
https://apps.desk365.io/apis/tickets
Under HTTP request header parameters, enter the value as ‘Authorization‘ and set the value to ‘<<YOUR-DESK365’S-API-KEY>>‘ as shown below and click on ‘Ok’.
3. As the next step, select the same link mentioned earlier in the ‘Access Web content’ pop-up and click ‘Connect’.
Your Power BI is now successfully connected to Desk365’s API.
Create a function - 'GetPage'
The second step to integrate Desk365 with Power BI is to create a function called ‘GetPage.’ This function facilitates the retrieval of tickets and their corresponding details from Desk365.
Follow the below steps to create a ‘GetPage’ function.
1. Navigate to your Power BI platform.
2. Select Blank query under Get data as shown below.
3. The Power Query Editor window will open with a new blank query. From there, go to the Power Query Home menu and choose the Advanced Editor option.
4. Copy and insert the provided code into the Advanced Editor.
(page as number) =>
let
Source = try Json.Document(Web.Contents("https://apps.desk365.io/apis/tickets?offset="&Number.ToText(page),
[Headers=[Authorization="<<YOUR-DESK365'S-API-KEY>>"]])) otherwise [tickets=null, count=null],
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded data List" = Table.ExpandListColumn(#"Converted to Table", "tickets"),
#"Expanded data Records" = Table.ExpandRecordColumn(#"Expanded data List", "tickets",
{"ticket_number", "subject", "contact_email", "priority", "status", "type", "assigned_to",
"group", "category", "subcategory", "source", "created_on", "updated_on", "first_response_time",
"due_date", "resolved_on", "closed_on"}, {"tickets.ticket_number", "tickets.subject",
"tickets.contact_email", "tickets.priority", "tickets.status", "tickets.type",
"tickets.assigned_to", "tickets.group", "tickets.category", "tickets.subcategory", "tickets.source",
"tickets.created_on", "tickets.updated_on", "tickets.first_response_time", "tickets.due_date",
"tickets.resolved_on", "tickets.closed_on"}),
next_page = if Record.HasFields(Source, "next_page") then Source[next_page] else null
in
#"Expanded data Records"
Note: <<YOUR-DESK365’S-API-KEY>> refers to the API key of your Desk365 helpdesk.
Now click on ‘Done’.
5. Right click on ‘Query 1’ and rename it as ‘GetPage‘.
Before proceeding with the next step, make sure this function works right.
Test the ‘GetPage’ function:
1. Enter 0 in the Page value and click on ‘Invoke’.
If you have executed all the steps correctly, this function will retrieve the latest 30 tickets from your helpdesk.
Retrieve all the tickets from Desk365 - 'GetAllTickets' function
The ‘GetPage’ function only retrieves latest 30 tickets from Desk365. So to extract all the tickets you need to perform another power query function. Follow the below steps to create the ‘GetAllTickets’ function.
1. In your Power Query Editor, select Blank Query from New Source under the Home tab.
2. Click on Advanced Editor and enter the below code as you did for the previous function.
let
Source = try Json.Document(Web.Contents("https://apps.desk365.io/apis/tickets?offset=0",
[Headers=[Authorization="<<YOUR-DESK365'S-API-KEY>>"]]))
otherwise [tickets=null, count=null, next_page=null],
PageCount = if Source = null then 0 else Number.RoundUp(Source[#"count"]),
#"Page List" = List.Generate(
() => [Page = 0],
each [Page] < PageCount,
each [Page = [Page] + 30],
each [Page]
),
#"Converted to Table" = Table.FromList(#"Page List", Splitter.SplitByNothing(), null, null,
ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "PAGES"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"PAGES", Int64.Type}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "GetPage", each if [PAGES]
<> null then GetPage([PAGES]) else null),
#"Expanded GetPage" = Table.ExpandTableColumn(#"Invoked Custom Function", "GetPage",
{"tickets.ticket_number", "tickets.subject", "tickets.contact_email", "tickets.priority",
"tickets.status", "tickets.type", "tickets.assigned_to", "tickets.group", "tickets.category",
"tickets.subcategory", "tickets.source", "tickets.created_on", "tickets.updated_on",
"tickets.first_response_time", "tickets.due_date", "tickets.resolved_on", "tickets.closed_on"},
{"GetPage.tickets.ticket_number", "GetPage.tickets.subject", "GetPage.tickets.contact_email",
"GetPage.tickets.priority", "GetPage.tickets.status", "tickets.type", "GetPage.tickets.assigned_to",
"GetPage.tickets.group", "GetPage.tickets.category", "tickets.subcategory", "GetPage.tickets.source",
"GetPage.tickets.created_on", "GetPage.tickets.updated_on", "GetPage.tickets.first_response_time",
"GetPage.tickets.due_date", "GetPage.tickets.resolved_on", "GetPage.tickets.closed_on"})
in
#"Expanded GetPage"
Note: <<YOUR-DESK365’S-API-KEY>> refers to the API key of your Desk365 helpdesk.
Now click on ‘Done’.
3. Rename the query as ‘GetAllTickets’.
All the tickets from Desk365 will get imported and will appear as a table view as demonstrated below.
Now that you’re familiar with retrieving all tickets from Desk365, let’s explore customizing and obtaining tickets based on Date and Time using the GetPage Function and GetAllTickets Function.
If you want to retrieve tickets updated after a specific date and time, simply use the “updated_since” parameter in this format “yyyy-MM-dd HH:mm:ss” into your code within the advanced editor for both functions. This addition ensures that only tickets updated from the specified date will be displayed.
Note: When using “updated_since” parameter indicate the specific date and time from which you want to showcase your tickets such as “updated_since=2023-11-01 00:00:00”.
Impactful reports from raw data
Finally, you can use the Power BI tool to transform these raw data into actionable insights, enabling better decision-making, data-driven strategies, and improved business performance.
For instance, here you may see the sample report generated for ‘No. of tickets vs status‘
Here is another report depicting the bar graph for ‘No. of tickets vs groups‘.
Integrating Desk365 with Power BI is a strategic move for any organization looking to leverage its helpdesk data for better customer support and operational efficiency. Embrace the power of data, and watch your customer support operations reach new heights with Desk365 and Power BI integration.
