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),
#"Transposed 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!
02/08/2019 at 11:28
Token Eof Expected error in advance editor . it’s show ing error st Source.
12/09/2019 at 13:47
Hi there, did you ever get an answer on this? I’m facing the same situation. Thanks!
17/10/2019 at 13:47
Saved my day. I’ve been looking for this solution for quite a long time. Thanks!
26/11/2019 at 22:48
If your Jira server is running OAuth 1.0 and you’re having trouble connecting because Excel and Power Query both do very poorly at the old OAuth 1.0 standard, you might try taking a look at:
https://github.com/brucephenry/aegle
and
https://github.com/brucephenry/oaf
I struggle with that problem for a while before going the proxy route.
Aegle is a thin proxy to allow you to address unauthenticated localhost and proxy that request to OAuth1.0 authenticated Jira calls.
OAF is a helper that handles doing the “OAuth dance” and writing keys to files for use by Aegle.
These each will require installing Ruby, so there’s that.
Cheers!
09/12/2019 at 17:21
Thanks for the info!
15/01/2020 at 07:29
Thanks for the Info! It works in Power BI Desktop when using refresh.
However, in Power BI Service it gives the following error,
“You can’t schedule refresh for this dataset because the following data sources currently don’t support refresh:
Data source for abcd_xyz
Discover Data Sources
Query contains unsupported function. Function name: Web.Contents”
Is there a way around this?
06/07/2020 at 23:03
I’m having the same trouble as imd, is there any solution to this?
17/09/2020 at 15:23
-You need to format your source as the following the be able to refresh the web_contents function:
Source = Json.Document(Web.Contents(“YOUR JIRA_URL”, [RelativePath=”rest/api/2/search/YOUR_JQL]))
-So basically you need to add the “[Relativepath]” because else the Power BI Service can’t find the endpoint.
17/09/2020 at 15:26
-You need to format your source as the following the be able to refresh the web_contents function:
Source = Json.Document(Web.Contents(“YOUR JIRA_URL”, [RelativePath=”rest/api/2/search/YOUR_JQL]))
-So basically you need to add the “[Relativepath]” because else the Power BI Service can’t find the endpoint.
16/04/2020 at 18:03
Very useful information, thanks for sharing!
Our team created Power BI connector for Jira https://marketplace.atlassian.com/apps/1221150/power-bi-connector-for-jira?hosting=server&tab=overview Connector allows you to import any data from Jira to Microsoft Power BI and create informative dashboards and reports in minutes. You can export jira tables and fields, including Custom fields, History, Jira Service Desk, Tempo Timesheets, Tempo Planner, Agile, etc. It has a 30-day trial version and excellent customer support.
30/09/2020 at 01:46
The code worked great, thanks for the awesome post. I have been trying to find the Issue key (format AAAA-####, but only see the issue ID (all numbers). Is it in one of the fields that needs to be expanded? If you could point me in the right direction, that would be really helpful. Thanks
19/10/2020 at 13:28
A bit off-scope regarding Power BI but just pointing it out: The most easy way (without coding) is to use the standard Jira Data Connector from Azure Data Factory. This will give you back easy-to-read tables (and all records/history). Also Azure Data Factory is much faster in ingesting and loading all data, because in my use-case I need to get all history data of ~ 150 Jira projects. Power Query is a bit “slower”, but can be done if you have the patience 😉