Continuous Integration, QA, Docker,...

Queries to JIRA from Power BI

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.


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:
  • 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:

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 :


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!


  1. Raviteja G

    02/08/2019 at 11:28

    Token Eof Expected error in advance editor . it’s show ing error st Source.

    • Cristina M

      12/09/2019 at 13:47

      Hi there, did you ever get an answer on this? I’m facing the same situation. Thanks!

  2. Saved my day. I’ve been looking for this solution for quite a long time. Thanks!

  3. 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:

    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.


  4. 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?

    • I’m having the same trouble as imd, is there any solution to this?

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

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

Leave a Reply

© 2020

Theme by Anders NorenUp ↑

Si continuas utilizando este sitio aceptas el uso de cookies. más información

Los ajustes de cookies de esta web están configurados para "permitir cookies" y así ofrecerte la mejor experiencia de navegación posible. Si sigues utilizando esta web sin cambiar tus ajustes de cookies o haces clic en "Aceptar" estarás dando tu consentimiento a esto.