Jira to Power BI (Recursive approach)

It is not so obvious to get data from Atlassian Jira into Microsoft Power BI. I share here my learnings and solution about it.

Update: see improved version

Basics and Constraints

The basic idea is to get data from Jira using a REST API call.

One good trick is to use a filter key or name for the query definition. (This avoids defining the query in PowerBI but instead at the source in the Jira UI.)

The basic Rest API request URL looks then something like:

Source = Json.Document(Web.Contents(JiraRootUrl&"/rest/api/2/search?jql=filter="&JiraFilterId&"))

where JiraFilterId can be the Jira filter key or simply the name of the predefined filter.

1000 issues limit

Jira limits the number of issues you can get by an API call to 1000, so that you have to play with the parameters &startAt and &maxResults and export the issues by bunches.

The simple request above will pull all the Jira fields and most likely you might accounter memory limitations. Therefore you have to restrict the list of fields you want to import by adding the query parameter &fields=... followed by the fields Ids separated by a comma.

Fields to export: id vs. name

Here you have to mind that api requests the fields Ids and not the fields display names you see in Jira.

Tip: To make the bridge between the two you can have a look at the response

<JiraRootUrl>/rest/api/2/field or <JiraRootUrl>/rest/api/latest/field

This display all the fields definition in a json with following format (you can view it nicely for example using a Json formatter):

{"id": "customfield_17700","name": "Customer Priority","custom": true,"orderable": true,"navigable": true,"searchable": true,"clauseNames": ["cf[17700]","Customer Priority"],"schema": {"type": "option","custom": "com.atlassian.jira.plugin.system.customfieldtypes:select","customId": 17700}},

The first two elements "id" and "name" are what you are looking for.

Tip: You can export the list of field names of your predefined filter by exporting the filter to a .csv.

If you get an error because you reach the 1000 issues limit, add to the url ?delimiter=,&tempMax=1

Core Implementation

In PowerBI M-query language the core implementation looks like this, available in Gist here:

let
    // Get total
    Source = Json.Document(Web.Contents(JiraRootUrl&"/rest/api/2/search?jql=filter="&JiraFilterId&"&maxResults=1&fields=["&JiraFields&"]")),
    total = Source[total],
    
    GetChunk = (previousTable) as table => 
    let        
        startAt = if previousTable = null then 0 else Table.RowCount(previousTable) + 1,
        maxRes = if (maxRows - startAt < maxResults) then maxRows - startAt else maxResults,
        MergedTable = if (startAt > total) or ((maxRows>0) and (startAt > maxRows)) then previousTable else 
            let
                Chunk = Json.Document(Web.Contents(JiraRootUrl&"/rest/api/2/search?jql=filter="&JiraFilterId&"]&fields=["&JiraFields&"]&maxResults="&Text.From(maxRes)&"&startAt="&Text.From(startAt))), 
                ChunkTable = Table.FromList(Chunk[issues], Splitter.SplitByNothing(), {"Column1"}),
                table1 = if previousTable = null then ChunkTable else Table.Combine({previousTable,ChunkTable}),            
                table2 = @GetChunk(table1) // recursive call
            in
                table2
    in MergedTable,

    IssuesTable = GetChunk(null)
    
in
    IssuesTable

It uses as parameters JiraRootUrl, JiraFilter, maxRows (set to 0 to get all) and maxResults (can be set to 1000 / or the max limit configured on your server - except you have memory issues getting the max number of issues in a call, then you can try reducing it.)

It is implemented using a recursive approach (see the @ in the code).

Packaging

You can package your PowerBI file as a template and also define the variables are real parameters.

When you open the template you will then be prompted to fill in the parameters.

Alternative Solution

Alternatively, you could also export a Jira filter directly to a csv using the Export Csv feature.

Advantage: you immediately get the data as you see it in Jira (no need to rename fields)

You still need to download by bunches of 1000 so you need some kind of advanced scripting to merge the bunches but it hasn't to be in PowerBI - can be for example in VBS.

The data is then imported in PowerBI via an intermediary file (Csv or Excel)

This approach is explained in this post of 2020-10-14.

Thierry Dalon's Blog

October 6, 2020