To this day, the JIRA connector with Power BI, does not allow queries in JQL format to obtain data. It would be great to query what you want from Power BI to JIRA, right? I’ll tell you how to do it. Just follow this tutorial.

Steps

Open Power BI for desktop and click on New Source-> Blank query:Add two parameters with Manage Parameters-> New Parameter. The parameter’s name must be:

  • JIRA_URL: The URL of your JIRA instance
    • For example: https://jira.atlassian.com
  • QUERY: The query you want to perform in JQL format
    • For example: Project = CLOUD and FixVersion in (ReleasedVersions ()) and type = Improvement and Resolution = Fixed

The parameters will be of type “text”.Right-click on the query and go to Advanced Editor. Then, paste this code:

Let
Source = Json. Document (Web. Contents (JIRA_URL & "/rest/api/2/search? jql =" & QUERY)),
# "converted to Table" = Record. ToTable (Source),
# "translated table" = table. Transpose (# "converted to table"),
# "Promoted Headers" = Table. PromoteHeaders (# "Transposed table", [PromoteAllScalars = True]),
# "changed Type" = Table. TransformColumnTypes (# "Promoted headers", {{"Expand", type text}, {"StartAt", Int64. Type}, {"MaxResults", Int64. Type}, {"Total", Int64. Type}, {"Issues", type any}}),
# "removed Other columns" = Table. SelectColumns (# "changed Type", {"Total"}),
# "Total" = # "removed Other columns" {0} [total],
# "StartAt List" = list. Generate (() = > 0, each _ "total", each _ + 100),
# "converted to Table1" = Table. FromList (# "StartAt List", Splitter. SplitByNothing (), NULL, NULL, extravalues. Error),
# "renamed columns" = Table. RenameColumns (# "converted to Table1", {{"Column1", "StartAt"}}),
# "Added Custom" = Table. AddColumn (# "Renamed columns", "URL", each JIRA_URL & "/rest/api/2/search? maxResults = 100 & jql =" & QUERY & "& startAt =" & Text. From ([startAt])),
data = List. Transform (# "Added Custom" [URL], each Json. Document (Web. Contents (_))),
# "converted to TableQuery" = Table. FromList (data, Splitter. SplitByNothing (), NULL, NULL, extravalues. Error),
# "expanded ColumnIssues" = Table. ExpandRecordColumn (# "converted to TableQuery", "Column1", {"Issues"}, {"Issues"}),
# "Expanded Issues" = Table. ExpandListColumn (# "expanded ColumnIssues", "issues"),
# "expanded issues1" = Table. ExpandRecordColumn (# "Expanded issues", "issues", {"id", "Fields"}, {"id", "Fields"})
In
# "Expanded Issues1"

Credit for this script goes to Tiago Machado, who shared it at the Power BI forum. I just added the use of parameters. At this point you should see something like this:Click on the icon to expand the fields column. At this point you can select the values you want to import from JIRA, as data columns. For this example I have selected “priority” and “Resolutiondate“. When you expand it, you’ll see that the “Priority” field displays Record instead of its value. The solution is to re-expand it and select “Name” at the list of columns.Now set the type of column resolutiondate as date/time and the column priority as type text.

Clicking Close and apply will run the query and bring back JIRA data. For this example, you can choose a stacked column chart, and show the priority of the improvements developed by years :

Security

The most usual scenario is that your JIRA instance ask for a user name and a password. To set up your credentials just  go to:

  • Data source Settings-> Select your JIRA URL and edit permissions
  • In the Credentials section, click Edit
  • In the next screen the most common is the basic type. Enter your user name and password
  • I prefer to use the Web API mode with a token associated to my user, so I do not have to update these settings every time I change my password

I hope this guide will help you. See you next time!