cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shirleympearls Regular Visitor
Regular Visitor

JIRA & PowerBI

Hey everyone,

 

I badly need to connect JIRA with Power BI. I did go through a document that has been shared all over the internet but it didn't help me much. For starters, this is the URL that I use, when PBI asks me to enter the URL for JIRA. 

 

https://jira.rd.abcpharma.net/secure/RapidBoard.jspa?rapidView=5048&projectKey=SSCL

 

This is the link to the Kanban board that is in JIRA, and I'd like to bring it to Power BI. Any help would be much appreciated! Smiley Happy

 

TIA Smiley Happy

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator Eric_Zhang
Moderator

Re: JIRA & PowerBI


@shirleympearls wrote:

Hey everyone,

 

I badly need to connect JIRA with Power BI. I did go through a document that has been shared all over the internet but it didn't help me much. For starters, this is the URL that I use, when PBI asks me to enter the URL for JIRA. 

 

https://jira.rd.abcpharma.net/secure/RapidBoard.jspa?rapidView=5048&projectKey=SSCL

 

This is the link to the Kanban board that is in JIRA, and I'd like to bring it to Power BI. Any help would be much appreciated! Smiley Happy

 

TIA Smiley Happy


@shirleympearls

When connecting to JIRA, the required URL is like "https://xxxxxx.atlassian.net", while clicking next, it acutally calls the API "https://xxxxxx.atlassian.net/rest/api/2/search".

 

Capture.PNG

 

Capture2233.PNG

 

So simplely speaking, at this moment, Power BI now can only get the data that returnbed by API "https://xxxxxx.atlassian.net/rest/api/2/search", As to your link to other JIRA REST APIs, you can do it by calling REST API in Power BI desktop, this would require REST API and programing skill. Check how to call such similar APIs in a demo Get Data from Twitter API with Power Query.

 

You can also submit your idea at Power BI Ideas and vote it up.

 

 

11 REPLIES 11
Moderator Eric_Zhang
Moderator

Re: JIRA & PowerBI


@shirleympearls wrote:

Hey everyone,

 

I badly need to connect JIRA with Power BI. I did go through a document that has been shared all over the internet but it didn't help me much. For starters, this is the URL that I use, when PBI asks me to enter the URL for JIRA. 

 

https://jira.rd.abcpharma.net/secure/RapidBoard.jspa?rapidView=5048&projectKey=SSCL

 

This is the link to the Kanban board that is in JIRA, and I'd like to bring it to Power BI. Any help would be much appreciated! Smiley Happy

 

TIA Smiley Happy


@shirleympearls

When connecting to JIRA, the required URL is like "https://xxxxxx.atlassian.net", while clicking next, it acutally calls the API "https://xxxxxx.atlassian.net/rest/api/2/search".

 

Capture.PNG

 

Capture2233.PNG

 

So simplely speaking, at this moment, Power BI now can only get the data that returnbed by API "https://xxxxxx.atlassian.net/rest/api/2/search", As to your link to other JIRA REST APIs, you can do it by calling REST API in Power BI desktop, this would require REST API and programing skill. Check how to call such similar APIs in a demo Get Data from Twitter API with Power Query.

 

You can also submit your idea at Power BI Ideas and vote it up.

 

 

Highlighted
TiagoMachado Frequent Visitor
Frequent Visitor

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

AlphaBeta Frequent Visitor
Frequent Visitor

Re: JIRA & PowerBI

Can someone help me understand how I would call all users rather than just my own assigned tickets?

savethepennies Frequent Visitor
Frequent Visitor

Re: JIRA & PowerBI

Change the criteria in the jql section. Whatever change you make should be made to both the Source line and the #"Added Custom" line. If you want all tickets then you can remove the jql section but be careful. You could be waiting awhile depending on your issue count.

savethepennies Frequent Visitor
Frequent Visitor

Re: JIRA & PowerBI

Thanks for the great explanation btw. This was very helpful in getting going. I'm trying to work out how to expand down to the issue fields so we'll see how that goes.

rsmccall33 Regular Visitor
Regular Visitor

Re: JIRA & PowerBI

My apologies for reviving an old thread, but I have implemented the code that@TiagoMacado has provided and it has allowed me to bring in 1000 rows instead of just 50.  However, the expected results is 3839 rows.  Is it possible to get all 3839 rows?  I have another table that has 40k+ rows and 1000 rows doesn't paint an accurate picture.

 

Edit:  to clarify, the line below returns a total of 3839.  So at this point, this step shows 3839 rows, but when I create a report that shows the total number of records, it = 1000

 #"total" = #"Removed Other Columns"{0}[total],

 

axy5093 Regular Visitor
Regular Visitor

Re: JIRA & PowerBI

Hi @TiagoMachado, Firstly thanks for your query i was able to get all the issues for a specific project with just a little tweaking! But now i realized that the hidden custom fields are not retrieved using REST API, i need to be able to get values for them. Do you know of a workaround to get this data ?

 

Thanks in Advance,

Aish 

TiagoMachado Frequent Visitor
Frequent Visitor

Re: JIRA & PowerBI

Hi @axy5093

 

I answered this question on this thread as well:

 

In order to get the custom_field "human" names, you need to perform another REST call at:

https://xxxxxx.atlassian.net/rest/api/2/issue/createmeta?expand=projects.issuetypes.fields

(where xxxx is your cloud domain)

And then rename the columns on your Power BI.

 

If there aren't many customfields, it is faster to hardcode the values (the above REST call takes some time to return data - specially when the number of projects you have on your cloud instance increases)

 

Some helpful references:

https://community.atlassian.com/t5/Answers-Developer-Questions/Custom-Field-Name-in-Jira-JSON/qaq-p/...

https://developer.atlassian.com/cloud/jira/platform/rest/?_ga=2.106182916.649572337.1531849237-10605...

 

 

Please let me know if it was helpful

 

Regards

axy5093 Regular Visitor
Regular Visitor

Re: JIRA & PowerBI

Hi @TiagoA

 

Thanks thats helpful, my problem was that i wasn't getting all the fields. The issue was that with JIRA REST API search function the default behaviour is to get only navigable fields and i needed all of them, Anyways tweaking the url like below worked for me.

Search documentation

https://xxxxxx.atlassian.net/rest/api/2/search?jql=project=XYZ&fields=*all

 

 

Aishwarya 

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 121 members 1,678 guests