JIRA & PowerBI

Hi,

Eric_Zhangis correct - in order to get specific data from JIRA, we must use the JIRA REST API.

The idea of this reply is to give a more step-by-step solution for those not so familiar with M, JIRA REST API or both.

Here I'm going to detail how I was able to retrieve data from JIRA - this was my first contact with PowerBI and worked, but I'm not happy with the performance - if anyone has any suggestion on improvements, just let me know !! (Thanks in advance)

Enough introduction done, let's get our hands on data !! So we must start clicking on "Edit Queries" in order to be able to edit the query internals. Later on it will make more sense why we are doing so (instead of clicking on Get Data -> Web)

Now we must:

1. Create a new blank query (New Source -> Blank Query)
2. Open the Advanced Editor (Right click on your new query -> Advanced Editor)

Now for those unfamiliar with M, we are going to just make a web query and parsing the results as a JSON format :

let
    Source = Json.Document(Web.Contents("https://xxxxxx.atlassian.net/rest/api/2/search?jql=assignee=currentUser()")),
in
   Source

This query will return all the items assigned to the current user. Please remember to replace "xxxxxx.atlassian.net" with your specific domain.

An improvement I made here is to add a parameter named "Query" in order to make it easier to customize the data I want from JIRA.

And that's it. Power BI will require your JIRA login and password, query your JIRA and present your data.

No.... Unfortunately, it is not that easy...

It is true that some data will be presented, but not necessarily all data.

Let me explain - for those unfamiliar with JIRA REST - the JSON result will have 3 important parameters:

• total - The total number of records of your query
• maxResults - The max number of records of this page
• startAt - The first item showed on this page

So, if total is less or equal than maxResults, all your data is being presented. But, if your query contains more records than maxResults, we need to query JIRA a couple more times to get everything.

Remark: maxResults cannot be larger than 100 at this moment

So, we are going to prepare all the URL's, query them in JIRA and return a single table with all the data. In order to do it:

let

    // The same query as before
    Source = Json.Document(Web.Contents("https://xxxxxx.atlassian.net/rest/api/2/search?jql=assignee=currentUser())),

    // Converting data from List to Table, so
    #"Converted to Table" = Record.ToTable(Source),

    // we will be able to transpose it
    #"Transposed Table" = Table.Transpose(#"Converted to Table"),

    // and make the field names the column headers
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),

    // Now we are going to assign the correct data types for each column
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"expand", type text}, {"startAt", Int64.Type}, {"maxResults", Int64.Type}, {"total", Int64.Type}, {"issues", type any}}),

    // And keep only the total column
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"total"}),

    // Now we are going to get the first (and only) record and assign it to a variable named #"total"
    #"total" = #"Removed Other Columns"{0}[total],

    // Now it is time to build a list of startAt values, starting on 0, incrementing 100 per item
    #"startAt List" = List.Generate(()=>0, each _ < #"total", each _ +100),

    // Converting the startAt list into Table - not sure if this is really necessary
    #"Converted to Table1" = Table.FromList(#"startAt List", Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    // and renaming its single column
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table1",{{"Column1", "startAt"}}),

    // Now let's create a new column URL with the URL we are interested at. Note the maxResults and startAt parameters this time
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "URL", each "https://xxxxxx.atlassian.net/rest/api/2/search?maxResults=100&jql=assignee=currentUser()&startAt=" & Text.From([startAt])),

    // And finally retrieve them all
    data = List.Transform(#"Added Custom"[URL], each Json.Document(Web.Contents(_)))
in
    data

Now, no matter how many results you have on your query, all the data will be retrieved. Later you will need to expand the issues field in order to work with your data, but this will have to be done accordingly to your needs/query/JIRA instance.

As I said earlier, this solution retrieves all JIRA data, but I'm not happy with the performance of it. The few improvements I made were on the first query - where we are going to get the total value, where I filter the fields (fields=id) as I'm not interested on the issue data itself at the moment - and getting only one record (maxResults=1).

I hope this helps anyone who is starting on PowerBI and/or JIRA...

If you liked this answer or if you have any performance improvement suggestion, please, leave a reply.

Regards,

Tiago.