Jira data in Power BI

Often, project or work related data exists in Jira and there is value in reporting/visualizing it in Power BI. Perhaps the reporting capabilities within Jira are not sufficient, there is value in extracting it to use in Power BI, or there is desire to combine the data from Jira with other data sources.

The are commercial Jira plugins available for Power BI, but I’m going to roll our own. For this technique, you’ll need basic understanding of JIRA, JQL, Power Query M, REST, and JSON.

Helpful tools

Postman

Power BI Managed Parameters

Parameter	Description	Example
JIRA_URL	Base url for JIRA instance	https://prod-1-jira.bogus-example.com
JIRA_PAT	Personal Access Token (PAT) for JIRA API use	HGA?Q`Z-DJ{H@4M2NZF2~&CS>A’2QQ;DLZ`){VWIGB’L

Jira Fields

Jira includes many predefined fields as well as an custom fields that have been created on a Jira instance. It is important to know the unique ID, field name, and schema type for the fields you which to use in reports. Here is how to get it.

We’re going to leverage Power Query M and REST APIs to retrieve data from JIRA.

Define a Power BI Transformation with the first step below to retrieve the data

= let
    Source = let 
  headers = [#"Authorization" = Text.Combine("Bearer",JIRA_PAT," ")]],
  Source = Json.Document(Web.Contents(JIRA_URL,[RelativePath="/rest/api/2/field", Headers=headers]))
in
  Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtractValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(Source, "Column1", {"id", "name", "custom", "order able", "navigable", "searchable", "clauses", "schema"}, {"id", "name", "custom", "orderable", "navigable", "searchable", "clauses", "schema"}),
    #"Filtered Rows" = Table.SelectRows("Expanded Column1", each true)
in
    #"Extract Values" = Table.TransformColumns(Source, {"clauseNames", each Text.Combine(List.Combine(List.Transform(_, Text.From), ","), type text})
    #"Expanded schema" = Table.ExpandRecordColumn(#"Extracted Values", "schema", {"type"}, {"schema.type})
    #"Renamed Columns" = Table.RenameColumns(#"Expanded schema", {{"schema.type", "schema_type"}})
in
    #"Renamed Columns"

Retrieving JIRA data of a particular issue type

It is often useful to write a Jira JQL query, first, to retrieve all issues of a particular type, that are of interest for the reporting needs. Issues retrieved should have the same issue type because different issue types have different fields. We’re going to need to map the data from JIRA into specific fields in Power BI.

JQL

Sample JQL query

project in (prj_a, prj_b) AND issuetype=Story AND status!=Canceled

Data Retrieval

The more data requested from Jira, the longer it will take to retrieve it. To reduce the data payload, it is helpful to explicitly request only the needed Jira fields.

Don’t ask for “*all” fields, changelog, or history. It makes the quantity of data explode resulting in slower transfers or a timeout.

Extraction Code

Ok, hold on tight for some extraction code and preliminary transformations.

= let
MAX RESULTS=200,

headers = [#"Authorization"= Text.Combine("Bearer",JIRA_PAT), " "],

QUERY = "project IN(prj_a, prj_b) AND issuetype=Story AND status!=Canceled"

FIELDS = "&fields=key,summary,status,assignee,priority,issuetype,resolutiondate,custom field_123456",

RenameColumnsFromMap = (data_table, fields) => 
  let 
    // Transform custom field numbers back to their textual names
    fields_rep = List.Transform(fields, each {_[id], _[name]})
    expand1 = Table.ExpandRecordColumn(data_table, "Column1", {"id", "key", "fields"}, {"id", "key", "fields"}),
    expand2 = Table.ExpandRecordColumn(expand1, "fields", Record.FieldNames(expand1{0}[fields]), null),
    renamed = Table.RenameColumns(expand2, fields_rep, MissingField,Ignore)
  in Value,

GetFields = () => 
  let 
    // extract field ids and names for transformation back to names
    response = Json.Document(Web.Contents(JIRA_URL,[RelativePath="/rest/api/2/field/", Headers=headers])),
    Value = List.Transform(response, each [id=_[id], name=_[name], schema_type=_[schema][type]])
  in Value,

TransformToIdKeyFields = (data_list) => 
  let 
    // custom field id to key map
    xformed = List.Transform(data_list, each [id=_[id], key=_[key], fields=_[fields]])
  in xformed,

GetTotal = () => 
  let 
    // Retrieve total number of issues that would be returned by JQL
    // total needed for retrieving pages of data from JIRA API, in pages of MAX_RESULTS size
    Skip = "&StartAt=0",
    Top = "&maxResults=1",
    RawData = Web.Contents(JIRA_URL, [Headers=headers, Timeout=#duration(0, 0, 2, 0), RelativePath="rest/api/2/search?jql=" & QUERY & Skip & Top & "&fields=key"]),
    Json = Json.Document(RawData),
    Value = Json["#issues"]
  in Value,
  
GetPage = (Index) => 
  let 
      // get 1 page of data, based on index and MAX_RESULTS
      Skip = "&startAt=" & Text.From(Index * MAX_RESULTS),
      Top = "&maxResults=" & Text.From(MAX_RESULTS),
      RawData = Web.Contents(JIRA_URL, [Headers=headers, RelativePath="rest/api/2/search?jql=" & QUERY & Skip & Top & FIELDS]),
      Json = Json.Document(RawData),
      Value = Json["#issues"]
  in Value,

GetPages = (Loop) => 
  let // Get all the pages of data 
      total_issues = Get_Total(),
      total_pages = Number.RoundUp(totaL_issues / MAX_RESULTS),
      page_indices = {0 .. total_pages -1},
      pages_data = List.Transform(page_indices, each GetPage(_)),
      pages = List.Union(pages_data)
  in pages,

fields = GetFields(),
pages_list = GetPages(0),
data_id_key_fields = TransformToIdKeyFields(pages_list),
data_table = Table.FromList(data_id_key_fields, Splitter.SplitByNothing(), null, null, ExtractValues.Error),
data_table_mapped_columns = RenameColumnsFromMap(data_table, fields)

Transformation code

After acquiring the data, there are multiple transformations which you’ll want to apply.

One category transforms is about extracting individual properties from complex JSON objects. Some of those are

Assignee
Priority
Status

The Assignee’s Full name can be extracted from the Assignee object, by extracting the “displayName” field. Here is what an Assignee object might look like:

{
  "self": "https://prod-1-jira.bogus-example.com/rest/api/2/user?username=ABC123",
  "name": "ABC123",
  "key": "JIRAUSER123456",
  "emailAddress": "REALLY_JUST_AN_EXAMPLE@bogus-example.com",
  "avatarUrls": {
    "not_going_into_more_detail": "sample_url_for_avatar_at_certain_size"
  },
  "displayName": "Zaphod Beeblebrox",
  "active": true,
  "timeZone": "America/Los Angeles"
}

A transformation step to get just the displayName would be like below.

= Table.ExpandRecordColumn(#"Prior Step Name", "Assignee", {"displayName"}, {"assignee"})