Integrating Jira with Power BI

There are quite a few options when you search "How to load Jira data into Power BI" on Google. A lot of them however are not what you want if you want to do your own thing.

In this blog post I will show how you can connect to the Jira REST API and load your data about projects, tasks etc. directly into Power BI using the web connector.

There is some work involved since we need to specify which entities/tables from the API we want and we might not know the terminology.

The advantage of this is that you are completely flexible and do not rely on any third-party solution though.

Before you can pull data from Jira you need to get a personal access token, to create one, log into Jira, go to Profile -> Personal access token -> Create token

After that, in a blank Power BI report, I created a parameter with the access token and a blank query and then added the following M code:

let
  // Base URL without query parameters
  baseUrl = "https://YOUR_JIRA_URL/rest/api/latest/search", 
  // JQL query without encoding
  jqlQuery
    = "project=PROJECT_NAME", 
  // Query parameters
  queryParams = [maxResults = "1500", jql = jqlQuery], 
  // Headers
  headers = [Authorization = "Bearer " & #"Access token", Accept = "application/json"], 
  // Make the API call without encoding in the JQL query
  response = Web.Contents(baseUrl, [Query = queryParams, Headers = headers]), 
  // Parse the JSON response
  json = Json.Document(response), 
  issues = json[issues], 
  #"table" = Table.FromList(issues, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    table

In the ‘baseUrl’ variable, add your Jira URL. If needed, you can also specify a JQL statement (Jira Query Language).

In this example, I wanted to pull issues from a specific project in Jira, so I used JQL to specify the project name. This won’t always be necessary depending on the API call and the data you want to retrieve.

The maxResults = "1500" parameter limits the number of issues retrieved from this project. For more information, refer to the official documentation.

Using the M expression below for the Web.Contents function prevents Power BI from creating a dynamic data source, thereby enabling the refresh in the Power BI service:

response = Web.Contents(baseUrl, [Query = queryParams, Headers = headers]) 

Conclusion

The key benefit of this approach is the ability to easily specify the data to be retrieved by defining the baseUrl and using JQL where necessary. It is important to avoid dynamic data sources, so you can refresh the model in the Power BI service.