1. Home
  2. Docs
  3. Integrations
  4. Learn about Desk365&#8217...
  5. Integrate Desk365 with Power BI

Integrate Desk365 with Power BI

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.

Integrate Desk365 with Power BI

2. Click on Advanced and fill in the details as mentioned below.

Enter the below link in URL parts.


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

from web page filled

3. As the next step, select the same link mentioned earlier in the ‘Access Web content’ pop-up and click ‘Connect’.

establish api connection with Desk365

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.

Integrate Desk365 with Power BI

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.

advanced editor from power query

4. Copy and insert the provided code into the Advanced Editor. 

					(page as number) =>
    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
    #"Expanded data Records"

Note: <<YOUR-DESK365’S-API-KEY>> refers to the API key of your Desk365 helpdesk. 

Now click on ‘Done’.

advanced editor code for get page function

5. Right click on ‘Query 1’ and rename it as ‘GetPage‘.

rename getpage function

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

test the getpage function

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.

Integrate Desk365 with Power BI

2. Click on Advanced Editor and enter the below code as you did for the previous function.

    Source = try Json.Document(Web.Contents("https://apps.desk365.io/apis/tickets?offset=0", 
    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, 
    #"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"})

    #"Expanded GetPage"


Note: <<YOUR-DESK365’S-API-KEY>> refers to the API key of your Desk365 helpdesk. 

Now click on ‘Done’.

advanced editor code for function

3. Rename the query as ‘GetAllTickets’.

All the tickets from Desk365 will get imported and will appear as a table view as demonstrated below. 

get all tickets from Desk365

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

reports for tickets vs status

Here is another report depicting the bar graph for ‘No. of tickets vs groups‘.

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

Require additional assistance? Please reach out to us at help@desk365.io

How can we help?